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

Creating a simple BIRT report

Creating a simple BIRT report

This is a small tutorial to help you get started with BIRT report development. We will setup the BIRT report designer and create a simple tabular BIRT report accessing data from a MySQL database.

Below are the steps –

Getting the BIRT report designer
You can download the BIRT report designer at http://download.eclipse.org/birt/downloads/ .

It provides various download options. I went with the “All-in-One” download package which included eclipse with all the necessary BIRT plugins preinstalled. You just need to unzip the archive file and then start the eclipse application.

Adding required drivers for DB connection
If you want to add any drivers for your database connections to be used in the designer, copy the driver jars to the below folder in your eclipse installation before you start eclipse –

<eclipse home>\plugins\org.eclipse.birt.report.data.oda.jdbc_X.X.X.vxxxxxxxxxxxx\drivers

In this case, I added the mysql driver jar file.

Creating a project
Once you have the report designer all set up, create a new project in eclipse with the “new project wizard”. You can see a new Projects Category appearing in the wizard selection list – “Business Intelligence and reporting tools”, select “Report Project” option under it.

Fig 1 new  report project

 
Adding BIRT report to the project
Now add a new report file (rptdesign) to the project. Right click on the project in the navigator window and click new à report, give an appropriate name to the file which will contain the report definition of the BIRT report. It’s a XML based report design – a single “.rptdesign” file which contains everything that is required for the report to execute.

Fig 2 New report file

Designing the report
When the “rptdesign” file is open, in the main window, it shows 4 tabs. It shows a canvas in the first tab, the visual layout view of the report – where you can drag and drop various items from the palette to design the report. The other tabs are the master page, scripts tab for you to add any custom scripts, the XML source tab to view the source of the design file as it gets generated.

In the “Outline” window, you can see the entire list of things that will define your report. We will now use this window to start adding components to our report.

1. Creating a data source

Right click on the “Data Sources” in the Outline window and select “new Data Source” to open the wizard. It lists all the different types of data sources that can be created. For this tutorial, we select JDBC Data source and then fill in the required connection details to create the data source –
Fig 3 Data source proerties

2. Creating a Data Set

A dataset is basically the query that would fetch the data to be displayed in your report. You can associate a dataset with your tables, charts and any other report elements.

Right Click on the “Data Sets” in the outline, to define your dataset. The wizard allows you to first select one of the datasets and then select the “Data Set Type” which in our case would be a SQL query. Write your SQL query text, which in our case is a simple select statement –

Fig 4 Query for Dataset

Once finished, all the fields start showing up under the dataset in the outline

3. Adding a table to the report

Now drag a “table” component from the palette and drop it on the canvas. When you do, it asks for a dataset to associate with the table. Select the one we created in the step above. It will display all the fields from the dataset from which you select the ones to be displayed in the table. When you click finish, you see a table on the canvas, with 3 sections – Header, Detail and Footer.

The detail section columns will be automatically bound with the various fields we selected and header labels will also be set.

Fig 7 Table

You may use the “properties window” to format the table look and feel. I have added some borders and changed the font etc.

Fig 6 Properties Window

We are more or less done with a basic report. I also have changed the page heading for the report, by navigating to the “Master Page” tab and changing the same there.

Fig 8 Master Page

Previewing the report

There are various types of preview available –

Fig 9 Birt Report Previews

When you preview in web viewer, the BIRT report viewer is used to render the report.

Fig 10

With this, our simple BIRT report is done!! More posts on BIRT reporting to follow, stay tuned !

Shraddha Tambe | Helical IT Solutions

You may also like to read –
Introduction to BIRT (Business Intelligence and Reporting Tools)

Creating a project in SpagoBI Studio

Creating a project in SpagoBI Studio

SpagoBI Studio is an eclipse based BIRT reporting software and creating a project in SpagoBI studio is very much easy and recommended too as all our reports / documents will be organized in better way

Let’s start creating a project in SpagoBI Studio:

  1. Start SpagoBI studio
  2. Click File > New and select Project.
  3. Search for Report Project under (Business Intelligence and Reporting Tools).
  4. Assign Spago Tutorial
  5. Now, you should be able to see SpagoBI Tutorial under Project Explorer.
  6. Click Yes, to open Report Design Perspective.

Project Creation

Thanks & Regards
Venkatanaveen Dasari™

Creating a folder under Functionalities Tree in SpagoBI Server

Creating a folder under Functionalities Tree in SpagoBI Server

In general Spago BI uses its own functionalities tree which allows to better organize documents / reports by grouping them in folders. Although in built Folders are provided to organize (Analytical documents, Custom Documents and Audit & monitoring under Functional Tree) but most of the cases we like to maintain our reports to be placed under different folder.

Based on Role-Based permissions these folders are visible to user. This structure can be created or modified only by the administrator.

Let’s start creating a folder under Functionalities Tree:

  1. Login as biadmin.
  2. From left menu, Select Functionalities Management under Profile tab.
  3. By clicking on a node of the functionalities Tree a set of possible actions is shown:
  4. While clicking on Functionalities insert option fill in Label, Name & Description (option) on Functionalities Detail page.
    • Insert
    • Detail
    • Erase
    • Move up/down
  5. Assign roles as preferred and Save the page

Work Flow

Thanks & Regards
Venkatanaveen Dasari™