How to give condition for null values of multi sel

Question

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?

Answer 1

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'

Answer 2

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.

Answer 3

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:

1. 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}
2. If you print parameter on the report you will get different sizes of the collection parameter (Number of selected values in collection drop down)