iReport statement as parameter

[Mark Crichton]

Hi. Please can someone help with following:

I would like to pass the complete "WHERE statement" as a parameter to iReport 3.7 rather than each value for the statement parts.
The select statement is stored in the iReport itself.

The where statement options the user can choose are many, it's easier to build the where statement programatically and pass it to the report as a single parameter, so that I can ignore those not required rather than having to put default values that will include all values.

ie: rather than WHERE
fixproduct.prdid = fixlines.linprdid
and fixlines.linhedid = fixheader.hedid
and fixproduct.prdcode between $P{stkStart} and $P{stkStop}

would prefer
varWhereStatement = " fixproduct.prdcode between 'Item 1' and 'Item 6' "

WHERE
fixproduct.prdid = fixlines.linprdid
and fixlines.linhedid = fixheader.hedid
and $p{varWhereStatement}

[Mark Crichton]

Hi ALL... found the answer on the web...

To dynamically modify portions of the SQL query or to pass the entire SQL query as a parameter the syntax differs a little. BE CAREFUL. The ! character is needed. See the following example
SELECT * FROM $P!{MyTable} ORDER BY $P!{OrderByClause}
The parameters here are used to pass the table name in the FROM clause and the column names in the ORDER BY clause.
Note that you cannot use the normal parameters (i.e. $P{}) to dynamically modify your query.
Please note that the reporting engine first deals with the $P!{} parameter references by using their values to obtain the final form of the SQL query, and only after that it transforms the rest of the $P{} parameters.
Reference: The Definitive Guide to JasperReports by Teodor Danciu and Lucian Chirita