Dynamic query in jasper reports

Question

We are using jaspersoft studio to create a report and i am stuck at query part. Logically the kind of query i want is :

select * from mytable where
                    IF (condition1)
                              raw_sql_part_1
                    ELSE
                              raw_sql_part_2

So now to achive this in "Query editor dialog", i wrote:

select * from mytable where $P!{param1}

and added "param1" default value in Expression editor as:

"$P{param2}.equals("A") ?  "1 <> 1" :"1=1" , is 'For prompting' as false

Also added "param2" in parameter list with 'is for prompting' as true

I expect this: When i hit preview it will prompt me for param2 value and then based on param2 resolve the condition in param1 and then finally substitute that in the actual query. The default value of the param2 is empty string.

What actually happens: when i hit preview it ask me for the value of param2 which is expected,but value of param2 is not used to resolve condition defined in param1 as param1 condition always resolved to else part i.e "1==1" and the main query also became

select * from mytable where 1==1

Q1: Am i expecting wrong ?

Q2: Why is param2 is not used to resolve condition defined in param1 ?

We are using Jaspersoft studio version 6.1.1

Answer 1

Actually I did not think you could set defaultValueExpression on a parameter based on the value of another parameter, but it seems to work if the order of the parameters are correct in the jrxml.

Example

<parameter name="param1" class="java.lang.String">
    <defaultValueExpression><![CDATA["A"]]></defaultValueExpression>
</parameter>
<parameter name="param2" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[$P{param1}.equals("A") ?  "1 <> 1" :"1=1"]]></defaultValueExpression>
</parameter>

Works, so my guess is that you have not defined the parameters in correct order.

Note:

select * from mytable where $P{param1}, will try to use prepared statement

Queries in jasper report can be executed using parameters with prepared statement or with simple string substitution.

• $P{param} -- > prepared statement

• $P!{param} -- > String substitution

From your example it seems like String substition the query should be

select * from mytable where $P!{param1}

See this for more information: JasperReports: Passing parameters to query