convert parameter to SQL constant to use in query

[leniviy]

People often advise to use 'calculated parameters' where their default value expression refers to other parameters. But in jasperserver when I open a report and change the value of initial parameter in  "report options" , the calculated param isn't changed.

For example:

I have report parameter 'city' of type String and corresponding input control. 'city' can be null or any string value.

I created another parameter 'city_sql' , default value now is just $P{city} . I planned an expression to convert string to SQL constant and then use it as filter in my query:

where ( $P!{city_sql} is null or $P!{city_sql} = table1.city  )

But when query runs, city_sql is alvays null.

I wanted to use variables instead of parameters, but discovered that '$V{}' isn't expanded inside queries.

How to workaround this?

 

[baggypants]

I've done this by creating two parameters The first one $P{COMAPANY} takes the input from the user (if any) The second one $P{COMPANY_SCRIPT} manipulates the first one  e.g. Default Value Expression is

 ($P{COMPANY}.equals( null ) ? "" : "AND COMPANIES.CODE =" + $P{COMPANY} )

Then I pass that parameter back to the Query

 WHERE TRANS_CARDS."CASH_LOAD" IS NOT Null$P{COMPANY_SCRIPT}

Post Edited by baggypants at 07/14/2010 10:33

[bbartholow]

Anyone know how to do this using domain as a source and Domain Query

[bbartholow]

Anyone know how to do this using domain as a source and Domain Query?