Use Case : Handling multi select parameter at report level in TIBCO Jaspersoft BI (OR) "Filter Expression" example to filter(multi select) the data at report level in TIBCO Jaspersoft BI

Hi Folks,

Here is my new workout on filtering data at report level instead of query level in TIBCO Jaspersoft Reports. I hope you will find this useful.

When we can go with this approach?
1) Situations when you can't send  multi select parameter to Stored Procedures from Jasper. (I have tested with Oracle SP & hoping that the same would work in other RDBMS stored procedure methodologies ). 
2) Situations when you write complex sub queries to get the the multiple values WHERE IN clause of main query.

This example, I have explored while converting Business Objects crystal reports into TIBCO Jaspersoft reports.

Software used for the use case : 
1) OS : Windows 10
2) TIBCO Jasper Design Studio 6.1 CE
3) TIBCO Jaspersoft server 6.1 CE
4) Mozilla firefox browser
5) Database : PostgreSQL and foodmart database

NOTE:
# Assuming that the reader is fa-miler in basics of report design and  I will be covering the core part of the use case in this post
# Click the images to get the best view of the content.

USE CASE Description : 
Select few fields from customer table to have a tabular report output.
Filter the data at query level on gender field and filter the data at report level using country field.

i.e., parameter associated with gender filed is single select and parameter associated with country field is multi select which would applied on report but not the query.

1) Create new report and create data source connections.
2) Create 2 parameters. One will be used at query(param_Gender) and another will be used at "Filter Expression" of the report.

##) param_Country (java.util.List is the type) - It will be used in "Filter Expression"

 ##) param_Gender(java.lang.String is the type) - It will be used in Query

3) Write below query to the report.
SELECT * FROM customer WHERE (gender= $P{param_Gender} or $P{param_Gender} IS NULL)

4)Scroll down "Data set and Query Dialog" editor and click on "Filter Expression" tab from the bottom Menu.

5) Write below expression in the "Filter Expression" area (Open the expression editor)
(
        $P{param_Country}.isEmpty()
    )
    ?
    (
        $P{param_Country} != null
    )
    :
    (
        ((ArrayList) $P{REPORT_PARAMETERS_MAP}.get( "param_Country" )).contains($F{country}.toString())
    )

6) Save the report and Preview.
Output test 1 : 
 ( select no elements for param_Country parameter and "F" for param_Gender).
You will get all the elements of gender F type. )
 i.e., When you don't pass anything in param_Country get all the result set of F type.

Output test 2 : 
Pass two values (Mexico and Canada) for param_Country and M type gender for param_Gender
You will get the output of M type gender who are from Mexico and Canada country.

Server side output:
1) Publish the report to the server.
2) Create these param_Country and param_Gender parameters in repository and link them to the report from server UI.
NOTE : param_Country is a multi select Query and param_Gender is single select query inputs.
Queries are :
#) param_Country takes SELECT DISTINCT country FROM customer
#) value and visible columns will be country filed.

#) param_Gender takes SELECT DISTINCT gender FROM customer 
#) value and visible columns will be gender field.

That's all. you are done writing parameters at query level as well report level.

Do you want to test the same example in your environment ?

Click this link to download the JRXML file & Project Export zip file.

# You can test it using stand alone JRXML in studio or by Importing the zip file into your server.
# Make sure you are running postgresql foodmart data base to get the output for this example.


Best Practices : 
1) Analyze the code in "Filter Expression". There could be other ways to achieve this.
2) Note that java.util.List type parameter take multiple values from parameters prompt.
3) There is no concept explained related to java.lang.Collection and $X{IN, fieldName, paramName} for this example. i.e., Report developer can assume that there would be only $X{} syntax way to pass multiple values which is not correct in few situvations.

I hope this will be helpful. If you find it useful please do share by clicking g+ on the page so that developers like us get benefit if they have the similar kind of requirement.
Also, suggestions & comments or alternative solutions are always welcome in comment box.


References :  

http://stackoverflow.com/questions/11512034/does-java-util-list-isempty-check-if-the-list-itself-is-null

 http://community.jaspersoft.com/questions/542895/dataset-expression-filter-filtering-using-contents-report-parameter-type

Thank you for finding this page & some time on this.

Cheers..!!!
Sadakar Pochampalli