reporting services - How to create a BIRT dataset that accepts multiple (CSV) values that it can be used inside "IN" clause in select statement

Question

I am trying to create a dataset in BIRT report that contains a select statement with "IN" clause and pass a comma separate value in place of "?" using a BIRT parameter that accepts multiple values.

for eg : select * from table where ID in ( ? )

I tried adding this in my dataset "select * from table where ID in ( params["paramer_name"].value)" but it is not working.

I do not want to use the built in Filter of BIRT dataset because using the "IN" clause in query reduces the cost of query to lot of extent in my database server.

Is there a simple way to do the same without adding long java scripts ???

FYI : The list of parameter that user selects comes from another dataset, and I want to use the selected value as an input to another dataset.

Thanks a lot for your help...

Answer 1

We cannot do this with a regular SQL parameter '?'.

A workaround is to replace this '?' by a default value in the query, and dynamically inject an appropriate comma-separated list of values in the "beforeOpen" script of the dataset:

Default query

Assuming the datatype of ID is an integer, set up the query like this (of course use here a valid ID to be able to preview data):

select * from table where ID in ( 1000 )

"beforeOpen" script of the dataset:

   this.queryText=this.queryText.replaceAll('1000',params["parameter_name"].value.join(","));

This way, if "parameter_name" returns 3 values 1100,1200,1300 the query sent to the database will be:

select * from table where ID in ( 1100,1200,1300)

It is similar if the datatype of ID is a String, we just have to play a little bit with quotes. However with a String type this kind of handling makes SQL Injection attacks possible, we should firstly check if parameter values look like what we expect.