Report with many complex optional parameters

[jbreault]

I am trying to create a report with Jaspersoft Studio with 15 optional parameters. Each parameter would only effect the WHERE filter clauses in the SQL. The user should be able to choose which of the 15 optional parameters they want to include by the Paramter Name, select the value of each parameter from a dropdown menu of possible options, select the operator for numerical values (=, <, >, ...) (This part is not essential, the majority of the time I would just want to use =), and lastly the user should be able to choose if this filter will be prefaced by the word "AND" or "OR" in the SQL. I have been reading up on conditional queries using parameters and cascading parameters, but this is much more complex than the examples I have seen and I am a little confused. Any help you can offer is appreciated.

I should also mention that this report will be published to JasperReport Server once completed. I am not sure if all this needs to be done instead on JasperReport Sever via input controles.

[darth_fader]

Start small and build up your query one parameter at a time. Parameter expressions can be conditionals; so use one parameter for the value the input control provides, another to create the query string based on that parameter value. Multi select inputs can be handles via the $X! and $P! references. For example, you may have an imput control to fulter null values, FilterNullValues, defined as a boolean data type input control, then a second parameter that evaluates that parameter and builds the sql accordinly. Finally, you can reference the FilterNullPopUpsSql by simply dropping $P!{FilterNullPopUpsSql} parameter in your query string.  When linking multiple parameters in your query string, just be careful where you place your "ands" and "nots" that link them together.

 

<parameter name="FilterNullValues" class="java.lang.Boolean" isForPrompting="true">

<defaultValueExpression><![CDATA[TRUE( )]]></defaultValueExpression>

</parameter>

<parameter name="FilterNullPopUpsSql" class="java.lang.String" isForPrompting="false">

<defaultValueExpression><![CDATA[$P{FilterNullPopups} ? 

"AND (NOT (resp.Response_Type = 'Show message' AND (resp.Response_Value = ' ' OR resp.Response_Value = NULL)))" : 

""]]></defaultValueExpression>

</parameter>

 

 

[mlopez_1]

For me is much more easier using:

$X{IN, <column_name>, <parameter_name>}

$X{NOTIN, <column_name>, <parameter_name>}

$X{BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>}

..

..

If parameter values are null, translate it to always true value and doesn't affect to query.

Mariano