Dynamic query via Parameter / Input Control NOT Working in JasperServer 5.1 CE

[bogartlisa]

I have a report where I am attempting to have the where clause dynamically generated based on a parameter.  Basically, the user can enter a value for one of the parameters and, if so, the data returned would be limited to data that matches that criteria.  If the enter no data for that parameter, then all the data is returned.  So, I am dynamically building a where clause parameter.  The first parameter, processingStatus, is a promptable String parameter.  The second, processingStatusSql, is an non-prompting String parameter. 

the Default Value Expression for processingStatus is not set.

the Default Value Expression for processingStatusSQL is:

($P{processingStatus}!=null ?
    ($P{processingStatus}.length() == 0 ?
    " Where app.ProcessingStatus in ('C1', 'E1') " :
        " Where app.ProcessingStatus='" + $P{processingStatus}.toString() + "'")
    : " Where app.ProcessingStatus in ('D1', 'E1') ")

The sql for the report is as follows:

Select App.AccountNumber, App.LastName, App.FirstName, App.ProcessingStatus from napa.dbo.application App $P!{processingStatusSql}
order by App.ProcessingStatus, App.ID desc

This works beautifully in iReport.  It does NOT work once the report is deployed in JasperServer however. 

I have defined the input parameters in JasperServer as follows:

processingStatus - Single Value, Visible, Text Data Type

processingStatusSql - Single Value, Read-Only, Text Data Type.

 

If I supply a value for processingStatus, the value for is calculated as "Where app.ProcessingStatus in ('D1', 'E1') " which is NOT what I need.  I need "Where app.ProcessingStatus='D1'".

If I supply NO valye for the processingStatus, I get "Where app.ProcessingStatus in ('D1', 'E1') " as the processingStatusSQL which is correct.

 

 

[lucianc]

Is there any reason to have an input control for processingStatusSQL?  It shouldn't be required, try getting rid of the processingStatusSQL input control, it might help.

Regards,

Lucian

[manojshishodia]

I am calling jasper server reports through IFrame from my webapp.I am using custom bean data source for filling reports.I have to send from date and to date parameter entered by user to jasperserver. how can i retrieve those in jasperserver. i want to use them as filtering purpose. Also these paramters are not present in JRXML file. How ca i send and retrive those in my custom bean code which i have written.

 

Regards

Manoj