5

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

Alex K
  • 22,315
  • 19
  • 108
  • 236
user29910142
  • 53
  • 1
  • 4

1 Answers1

4

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

Community
  • 1
  • 1
Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
  • ,my order of defining the parameter is correct ie param2 declared first followed by param1(param1 evaluates the condition)... and sorry we actually tried the query with `$P!{param1}`.. fixed typo in question.... When you say it works on your side does it eval condition to both side of the condition based on param2?...In our case no matter what value we give for param2 our condition always eval based the defaultValue of param2 – user29910142 Jan 06 '16 at 05:26
  • ok it works!! the mistake i made was, I have been clicking on the reset button before running the report in jasper studio preview window, that very much looks like run button, but its actually clearing out the value.Thanks for helping out anyway. – user29910142 Jan 06 '16 at 09:12
  • Thanks for the accept, I say seems to work because using parameters like this seems a bit strange..... why not pass directly the correct value to param2.... note the final client should not be using iReport (normally)... – Petter Friberg Jan 06 '16 at 12:30