【Question】

I’m new to BIRT. I need to build reports involving complicated computations. One of them requires the statement “select * from XX where id in(?)”. The specific query target is like this: According to a parameter whose format is 1,2,3,4 and that passes sequences of indefinite lengths,  I need to perform aggregation over data in a number of regions. I tried a lot of methods but all failed.

【Answer】

It’s complicated to handle a query having IN operator in BIRT. BIRT doesn’t support defining an array type parameter in a query. It can’t pass an array directly to a DataSet to perform a SQL query having in operator. So there’s no way of write the SQL query within a data set.

There’s a workaround. You write complicated JS script to combine a SQL query and, in the meanwhile, need to assume that the IN statement returns a numeric data. For strings or datetimes, they should be quoted. That will make things even more complicated. To make the situation simple, you can handle it in SPL (Structured Process Language). SPL supports passing an array type parameter to the SQL query and return the result to BIRT’s DataSet for presentation. Below is the SPL script:

A1: Pass an array type parameter to find the desired data.

A2: Return the query result to BIRT.

You can connect to esProc via JDBC in BIRT and call the SPL script in the same way as calling a stored procedure. See How to Call an SPL Script in BIRT for details.