How do I set a parameter to a list of values in a BIRT report?

Question

I have a DataSet with a query like this:

select s.name, w.week_ending, w.sales 
from store s, weekly_sales_summary w 
where s.id=w.store_id and s.id = ?

I would like to modify the query to allow me to specify a list of store IDs, like:

select s.name, w.week_ending, w.sales 
from store s, weekly_sales_summary w 
where s.id=w.store_id and s.id IN (?)

How do I accomplish this in BIRT? What kind of parameter do I need to specify?

Answer 1

The easy part is the report parameter: set the display type to be List Box, then check the Allow Multiple Values option.

Now the hard part: unfortunately, you can't bind a multi-value report parameter to a dataset parameter (at least, not in version 3.2, which is what I'm using). There's a posting on the BIRT World blog here: http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html that describes how to use a code plug-in to bind multi-select report parameters to a report dataset.

Unfortunately, when I tried it, it didn't work. If you can get it to work, that's the method I would recommend; if you can't, then the alternative would be to modify the dataset's queryText, to insert all the values from the report parameter into the query at the appropriate point. Assuming s.id is numeric, here's a function that can be pasted into the beforeOpen event script for the datasource:

function fnMultiValParamSql ( pmParameterName, pmSubstituteString, pmQueryText )
{
strParamValsSelected=reportContext.getParameterValue(pmParameterName);
strSelectedValues="";
for (var varCounter=0;varCounter<strParamValsSelected.length;varCounter++)
{
    strSelectedValues += strParamValsSelected[varCounter].toString()+",";
}
strSelectedValues = strSelectedValues.substring(0,strSelectedValues.length-1);
return pmQueryText.replace(pmSubstituteString,strSelectedValues);
}

which can then be called from the beforeOpen event script for the dataset, like this:

this.queryText = fnMultiValParamSql ( "rpID", "0 /*rpID*/", this.queryText );

assuming that your report parameter is called rpID. You will need to amend your query to look like this:

select s.name, w.week_ending, w.sales 
from store s, weekly_sales_summary w 
where s.id=w.store_id and s.id IN (0 /*rpID*/)

The 0 is included in the script so that the query script is valid at design time, and the dataset values will bind correctly to the report; at runtime, this hard-coded 0 will be removed.

However, this approach is potentially very dangerous, as it could make you vulnerable to SQL Injection attacks: http://en.wikipedia.org/wiki/SQL_injection , as demonstrated here: http://xkcd.com/327/ .

In the case of purely numeric values selected from a predefined picklist, a SQL injection attack should not be possible; however, the same approach is vulnerable where freeform entry strings for the parameter are allowed.