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.

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

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 (?)

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.

Below is the work around for our problem:

Change the query to look something like

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.

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”

Shraddha Tambe | Helical IT Solutions

You may also like to read –

Introduction to BIRT (Business Intelligence and Reporting Tools)

Creating a Simple BIRT Report

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download