Birt allows for dynamic creation of queries in the Propery Binding tab of the Data Set Editor, as in
select columnA, columnB from tableA where columnC = ? and columnD in (" + params["VALUES"] + ")
<br />
to allow for passing a variable number of "in (list)" query parameters. However, since the list (comma-separated list of values) is then embedded in the query as literal values and not passed as bound variables, this doesn't allow for efficient caching of the prepared statement by the database. I've been searching for a way to do this, and after reading Scott Rosenbaum's article (
http://birtworld.blogspot.com/2009/04/multi-select-parameters-part-2.html), I was able to borrow from Scott to create this javascript-only technique. Insert this code into the report's "initialize" method and it will dynamically update any queries which have parameters which are linked to report parameters which are lists, and re-bind the parameter values for the query.<br />
<br />
NOTE:
Before using this technique, be sure to change any default report parameter values which are lists, to be single values. The initialize method may be run while you are in the Data Set Editor. If this happens when the default value of a report parameter contains a list, it will then re-write your query and change the query parameters...probably not what you want to have happen. Only specify a list for a parameter at preview or run time.<br />
<br />
To use this, change your query to "select columnA, columnB from tableA where columnC = ? and columnD in (?)" (one ? where the list used to be) and do not use Property Binding to build your query. In the Data Set editor, link the columnD parameter to the appropriate report parameter. If this parameter contains 3 values at run time, the query will be changed to "select columnA, columnB from tableA where columnC = ? and columnD in (?,?,?)" and the parameters re-bound to the individual list items as necessary.<br />
<br />
NOTE: report parameters specified as a list have to be of type "string" and query parameters linked to that report parameter need to be specified the same, even if the database column being bound to is of a different type.<br />
<br />
This code was written specifically for an environment where all list parameters are given for database columns of type integer - but the code should be easily adapted for strings or other types.
