Parameterized BIRT Reports

Parameterized BIRT Reports – Simple Parameter

In this tutorial, we will parameterize a simple BIRT report by adding a single select dynamic parameter to filter the report. (To learn how to create a simple BIRT report without parameter you may refer this blog)

We will take this simple sales report based on the FoodMart database. The report looks like –

Say we want to filter the above set of records by City, i.e. Store City. We will now see how we can add a simple single select parameter “Store City” to this report with a dynamic list of cities.

Create a dataset for the parameter

First Step is to create a dataset with the required query to fetch the list of Cities to display as a drop down list to the user for selection. This is required for a dynamic list input parameter, if you plan to give a static list of values, you do not need to define this dataset.

Create a Report Parameter

Now we define a report parameter to accept user input for the city. You can create one by right clicking on the “Report Parameters” in the “Report Outline” window.

• We will create a list box with the name “store_city”, data type will be string as this is a city name.
• To make the parameter mandatory, check the “Is Required” check box.
• Select “dynamic” in the ‘Selection List Values’ options. In the Dataset, select the dataset we created in the above step and give the value column and display column. We have only one column for both i.e. “store_city”.
• We are currently creating a single-select parameter, so we will leave the “Allow Multiple Values” unchecked.
• You may assign a default value, we are setting it to “Los Angeles”.

Updating the report Query to use parameter

Next step is to update the report query to use the parameter value. For that we will edit the report dataset. Firstly, we will add a where clause in the query for the column store_city. See the below image.

We added the clause “where s.store_city = ?” in the query Secondly, we define a query parameter whose value will be substituted in place of the question mark. See image below:

We defined a query parameter “ds_store_city” and linked it to the report parameter that we created earlier. This means that whatever value we select for the report parameter will get assigned to this query parameter and then it will be substituted in place of the “?” in the query. If you have multiple parameters in the query, there will be multiple “?” place holders in the query and the values will be assigned sequentially from the query parameters that are defined.

Save and Execute

Save the report now and we are done!! Run the report in web viewer and we get a parameters window to select the parameter input for “Store City”

And the report result is filtered accordingly.

In the upcoming blogs we will know more on the parameters – multiselect parameters, cascading parameters and so on. So, stay tuned!!

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