Customer Demo: How to create a Multi Select Filter in a Report

Create a Blank Report in Jaspersoft Studio and populate with the following SQL:

SELECT store.store_country,
       store.store_state,
       sales_fact_1998.store_cost,
       sales_fact_1998.store_sales,
       sales_fact_1998.unit_sales
  FROM store
 INNER JOIN sales_fact_1998 ON sales_fact_1998.store_id = store.store_id
 WHERE $X{IN, store.store_country, COUNTRY_P}

Tidy up the report by deleting bands and get the report looking like this:

Publish the report to the Server, as most of the magic is done there ...

Edit the Published Report :

Select the "Controls & Resources" and change the Display Mode to "In Page" and select the "COUNTRY_P" Input Control

Go through the Configure Wizard and Select the following options:

Save the Input Parameter

Submit the Input Parameter

Then select the MultiSelectReport  and you will a Multi Select Query which works as expected:

SELECT store.store_country,
       store.store_state,
       sales_fact_1998.store_cost,
       sales_fact_1998.store_sales,
       sales_fact_1998.unit_sales
  FROM store
 INNER JOIN sales_fact_1998 ON sales_fact_1998.store_id = store.store_id
 WHERE $X{IN, store.store_country, COUNTRY_P}
   AND $X{IN, store.store_state,   STATE_P  }

Note that I have extended the where clause to add a second condition which will form the second part of the filter.

Then publish to server as above and add a 2nd Input Control called STATE_P in the same way as above replacing COUNTRY with STATE until you get to the query string. At this point use this query string 

SELECT store.store_state
  FROM store 
 WHERE $X{IN, store.store_country, COUNTRY_P} 
 ORDER BY store.store_country,store.store_state

The Value and Visible columns should be both 'store_state'.

This should up a simple cascading filter.

multiselectreport.jrxml

cascadingparametersreport.jrxml