Parameterized BIRT Reports – Multi-Select Parameter

Parameterized BIRT Reports – Multi-Select Parameter

This blog will help you add a multi-select parameter in BIRT report and filter the data. In the last blog, we parameterized a “sales report” by adding a single select parameter “store city” to it. (You may refer it here). We will now convert this same parameter to a multi select, allowing you to select more than one cities, instead of just one.

Below images, show the single select parameter and the report.

Parameter_Prompt

Report_After

To reiterate our steps for adding parameter,

1. We first created a dataset for the parameter to get its values from.

2. Then we defined a Report Parameter.

3. We updated the report Query to use this parameter. We added the clause “where s.store_city = ?” in the query and then created a query parameter , linked to the report parameter.

HOW TO make the parameter a Multi-select ?

There are just 2 things we need to change.

1. Configure Report Parameter as Multi-select:

This is easy. All we need to do is to edit the parameter and check the “Allow Multiple Values” option in its properties. You can see that the default value option also now allows setting multiple values

Multiselect parameter - Edit

 

2. Updating the query to use a multi-select parameter:
This is not so straight forward.
We change the query to use the “in” clause – s.store_city in (?)

 

Query_Multiselect-Issue

 

But if you go to the query parameter definition, it does not work with the multi-select parameter, you see this message. By the default BIRT behavior, the query parameter only uses the first value among the select values. This will not give us the results we expect.
 
query-parameter_Multiselect-Issue
 

Below is the work around for our problem:

Change the query to look something like
 
Query_Multiselect
 

Now, we are going to write a small script to replace the string ‘999’ in the query string with a list of values selected from the multiselect report parameter.

Click on the Report dataset and Go to the “Script” tab on the main window. Select “beforeOpen” in the dropdown. This is because; we got to do the substitution before the dataset is opened.
 
DataSet_BeforeOpen
 
It will basically take the array of parameter values for “store_city”, say [Beverly Hills, Los Angeles, Merida] and give you – Beverly Hills’,’Los Angeles’,’Merida

This string will replace the string ‘999’ and you get this clause in your query –

s.store_city in (‘Beverly Hills’,’Los Angeles’,’Merida’)

Save the report now and we are done!!

Run the report in web viewer and we get a parameters window with the multi-select input for “Store City”

 
multiselect parameter

 
Report_WithMultiselect
 

Shraddha Tambe | Helical IT Solutions

You may also like to read –

Introduction to BIRT (Business Intelligence and Reporting Tools)

Creating a Simple BIRT Report

How to restrict Multiselect input control values in jaspersoft :

How to restrict Multiselect input control values in Jaspersoft 

This blog will teach the reader , how to restrict multiselect input control values in jasper without hampering the performance .

DB : Postgres (foodmart)

Table : customer

Reporting tool : iReport

Server : Jasperserver 5.6

I/P Parameter : country

Requirement : Get All the informations from customer table where country name is either USA or Mexico or Canada

Condition :

  1. you can not select more than 2 value in “country” input parameter
  2. if more than 2 values are selected in “country” input parameter then the main report query should also not get executed. This will unnecessarily hamper the performance.

Solution :

  1. create multiselect input control named as “country”

select distinct country from customer

  1. create a sub report which will display all the informations from customer table on selection of “country” parameter

and give its print when expression as :

$P{country}.size() <= 2

  1. create a text box in the header , write a message init as :

“You can not select more than 2 values in country parameter”

And give its print when expression as :

$P{country}.size() > 2

  1. in the sub report , query will be :

select * from customer

where $X{IN,country,country}

limit 20

  1. publish the report on jasper server.

O/P

# 1 : when 2 values selected in country parameter

lenlt2#2 : when more than 2 values selected in country parameter

lengt3

Thanks,

Rupam Bhardwaj