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

i trying create dataset in birt report contains select statement "in" clause , pass comma separate value in place of "?" using birt parameter accepts multiple values.

for eg : select * table id in ( ? )

i tried adding in dataset "select * table id in ( params["paramer_name"].value)" not working.

i not want use built in filter of birt dataset because using "in" clause in query reduces cost of query lot of extent in database server.

is there simple way same without adding long java scripts ???

fyi : list of parameter user selects comes dataset, , want use selected value input dataset.

thanks lot help...

we cannot regular sql parameter '?'.

a workaround replace '?' default value in query, , dynamically inject appropriate comma-separated list of values in "beforeopen" script of dataset:

default query

assuming datatype of id integer, set query (of course use here valid id able preview data):

select * table id in ( 1000 ) 

"beforeopen" script of dataset:

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

this way, if "parameter_name" returns 3 values 1100,1200,1300 query sent database be:

select * table id in ( 1100,1200,1300) 

it similar if datatype of id string, have play little bit quotes. string type kind of handling makes sql injection attacks possible, should firstly check if parameter values expect.