【Question】

I’m new to BIRT, but I need to use it for one of my report project. The report is complicated as it involves SQL statement select * from XX where id in(?). The computing goal is to get information under each region through a parameter. Parameter format is like [1,2,3,4] of different lengths. I tried a number of methods but none of them works and no valid information has been found on the web. So, any useful idea will be greatly appreciated!

【Answer】

Since BIRT doesn’t offer a direct support of array type parameter in a query, that is, an array can’t be passed directly to a DataSet to perform a SQL query with IN condition, you are impossible to write a SQL query directly under DataSet.

BIRT, however, takes a workaround. First it defines a SQL statement (select * from XX) in DataSet to retrieve all data; then define a parameter, say arg1, whose display type is List Box and that can have multiple values; and at last, select beforeOpen event (which enables execution of a pieced-together before the query over the data set) on Script tab under DataSet and write the following JS script to piece together a SQL query:

param = reportContext.getParameterValue("arg1");

tmp = "";

for(var i=0;i<param.length;i++){

tmp += param\[i\] + ",";

}

tmp = tmp.substring(0,tmp.length-1);

this.queryText += "where id in ("\+ tmp +")";

This way we have a DataSet with an IN condition. It’s roundabout and the assumption is that the value of IN condition is a number. If the value is a string or a date/time/datetime, we need to add the quotation marks. That will be even more complex.

If we can pass an array parameter to the query, the whole thing will be simple. esProc can do that. With esProc we define an array parameter, passes it to the SQL query to execute and return the result set to BIRT’s DataSet to present.

We can write the query with esProc as follows:

esProc can integrate seamlessly with BIRT that accesses the script in the same way it accesses a database result set. Details are explained in How to Call an SPL Script in BIRT.