Key words: Array type parameter  IN clause  A parameter of comma-separated strings

BIRT doesn’t support passing an array-type parameter to the query. That is to say, we can’t pass an array to the DataSet to execute a SQL query that contains an IN clause. So it’s hard to compose such a SQL query in Data Set directly.

There is a workaround:

1. Define a SQL query (select * from XX) in DataSet to select all data, and a parameter (such as arg1 whose display type is List Box) that can have multiple values;

2. Select beforeOpen event (A script that executes before BIRT opens the data set) on DataSet’s Script tab and write the following JSscript to compose a SQL query:

Though we can achieve a conditional query with IN clause this way, it’s not straightforward. If members of the array are strings or dates, times or datetimes, we need to quote them or perform a type conversion. Too many uncertainties need to be taken into account and the code is inflexible.

The query would become rather simple if we could directly pass an array value to the parameter. Below shows the configuration an array-type parameter and the query result:

Here I recommend esProc, which can receive an array-type parameter to pass it to a SQL query to execute and return result to BIRT’s DataSet for reporting. The esProc code is simple:

With the help of esProc SPL, BIRT can effortlessly achieve dynamic computing goals. For more examples, read Some Examples of Achieving Dynamic Data Sources in BIRT.

esProc provides JDBC driver to conveniently integrate with BIRT and other reporting tools. Read How to Call an SPL Script in BIRT to find how to use and deploy esProc JDBC.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.