I am developing a report using iReport where I want to add a condition for null values. It's working with single select option as:
(city=$P{p_city} or $P{p_city} is null)
It means here if we do not pass any value for the city parameter, the report will work, but this is not working in case of a multiselect option. We have to choose a "Collection" value expression in iReport when we create a parameter:
($X{IN, country,p_country} or $P{p_country} is null)
I have created two input controls in JasperReports Server to run this report. One is a single select, "Select City" and the other is a multi select, "Select country", and the report is running without passing a value for the "Select city" parameter, but for the "select country" parameter, we must pass the value, because "Select country" is multi select and a Collection type parameter and you can not add null condition in a query as for the city parameter.
How we can modify the JRXML query?
The $X feature handles this automatically. In your case you have this in your query:
$X{IN, country, p_country}
That gets converted to 1 = 1
in the case where your Collection is null. So there is no reason to add $P{p_country} is null
into the query.
When the value of Collection parameter is NULL then to hide all the data output is done by bellow
print when expression on table component.
P{Collection_Parameter_Name}.size()>0
notes:
- Collection parameter takes the syntax
$X{IN,filed_Name,param_Name}
in SQL query but here we have to take it
as $P{Collection_parameter_Name}
- If you print parameter on the report you will get different sizes of the collection parameter (Number of selected values in collection drop down)
You could put your condition like this
city IN ($P!{p_city})
Then you could call by the url with:
http://ip:8080/jasperserver/flow.html?_flowId=viewReportFlow&standAlone=true&_flowId=viewReportFlow&ParentFolderUri=/reports/reports&reportUnit=/reports/reports/rptReporte&j_username=jasperreportadmin&j_password=jasperreportadmin&p_city='city1','city2'