Tip : Displaying work around "All" on report when passing nothing from multi select non mandatory parameter in Jasper Reports 6.x

Hi Folks,
Here is a latest work around for displaying "All" when passing nothing from "Multi select parameter" in Jasper reports.

Concept :
Display the selected values of multi select parameter on the report.
Lets say if you select "USA","Mexico" from the multi select drop down, your report should display USA,Mexico
and if you select nothing in the drop down it should display All on the report instead of all the countries.


Solution :
Find the empty collection length, if the lenght is <=2 display "All" and if the lengh is > 2 display the selected values.
•    Create a Parameter as collection (coming from clarity)
            param_country (java.util.Collection)
•    Create another parameter which will calculate the length of multi select param
            param_country_length (java.lang.String)
        * Under default value expression, write below expression
                     $P{param_country}.toString().length()
•    Your Query for report should be as follows.

SELECT
        nvl(to_char(WM_CONCAT(replace(replace(Country_Field, '['), ']'))), 'All') cf
FROM customers
WHERE
    (
            ($P{param_country_length}) > 2 and $X{IN,lookup_code,param_inv_type}
            OR
            ($P{param_country_length}) < = 2
    )
  
    
* NOTE :
# "All" should not be a value from the dropdown.
# All should be handled from SQL code.
# Query used is Oracle SQL 11g (+) specific. Functions used in SQL code may not work in lesser versions.
# Tested the report using Jasper Design Studio 6.x Professional.
# On report to display the countries use filed instead of parameter name.
   i.e., $F{Country_Field} instead of $P{param_country}
# Do all this in a sub report and call it in main report because you can not handle it in main report.
# This may not work when you select "All" option available in the multi select drop down. ( Not tested).
# This is one of the ways, we have implemented in a report. I believe there could be other ways to solve the same.

Thanks,
Sadakar Pochampalli
Open Source BI specialist.