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?

Antrum

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.

Astrophysics

Many thanks for this solution! The only mark: I failed to make this work when the fnMultiValParamSql function is defined for datasource: I moved it to report design (root) level for the "initialize" event - and this worked like charm :) Also I modified the funcion a bit: added this code: if (strSelectedValues == "") { strSelectedValues = "null"; } after this I can pass nullable values and work with them like this: and (userCreator_id in (0 /*userList*/) or (0 /*userList*/) is null ) – Caddish

Answer 2

FYI: the BIRT World article should work (I wrote it) but that was an earlier solution to the problem.

We have created an open source plugin that you can add to BIRT that has a much cleaner solution to this problem. The Bind Parameters function in the birt-functions-lib provides a simple way to do multi-selects from multi-value parameters.

If you are still interested have a look at the birt-functions-lib project on Eclipse Labs.

Ophiolatry

Answer 3

Here's another one. Based on some hints I found elsewhere and extended to preserve the number of parameters in your data set SQL. This solution works with a JavaScript function that you call at OnBeforeOpen of the data set:

prepare(this);

function prepare(dataSet) {
    while (dataSet.queryText.indexOf("@IN?")>=0) {
        dataSet.queryText = dataSet.queryText.replace(
            "@XYZ?", 
            "('"+params["products"].value.join("','")+"') or ?=0"
        );
    }
}

In your query, replace occurrences of (?) with @XYZ?. The method above makes sure that the query has the actual values and still a parameter (so that the dataset editor and preview doesn't complain).

Note: Beware of SQL injection, e.g. by not allowing string values

Ciceronian

Nice answer, but it would be better if you provide an example dataset query where you could apply this query on. I'm following it and struggling a little bit with how to use the function. – Penney

Answer 4

I created a more general solution, which handles optional/required parameters behaviour too. When parameter is not required and user doesn't select any value, the IN-clause gets disabled. It also allows the user to select both real values and null value.

In report initialize script I add this code:

/** Fullfill IN-clause in a data set query,
 *  using a List box report parameter.
 *  Placeholder must be the parentheses after IN keyword with wathever you want inside.
 *  If required is false then the whole IN-clause in the query 
 *  must be surrounded by parentheses.
 *  dataType and required refers to the parameter, they must be passed, 
 *  but should be better to find a way to retrieve them inside this function
 *  (given parameter name).
 */
function fulfillInClause(dataSet, placeholder, param, dataType, required) {

    if (dataSet.queryText.indexOf(placeholder)>=0) {

        var paramValue = params[param].value;
        var emptyParam = (paramValue==null || paramValue.length<=0);

        //build the list of possible values
        //  paramValue==null check in ternary operators 
        //  will prevent exceptions when user doesn't select any value
        //  (it will not affect the query if param is optional, 
        //  while we will never arrive here if it is required)
        var replacement = " (";
        if (dataType == "string")
            replacement += (emptyParam ? "''" : createList(paramValue, ",", "'", "varchar(10)") );
        else if (dataType == "integer")
            replacement += (emptyParam ? "0"  : createList(paramValue, ",", "" , "int"        ) );
        else
            //TODO implement more cases
            return;
        replacement += ") ";

        //if param is not required and user doesn't select any value for it
        //then nullify the IN clause with an always-true clause
        if (!required && emptyParam)
            replacement += " or 0=0 ";

        //put replacement in the query
        dataSet.queryText = dataSet.queryText.replace( placeholder, replacement );
        //DEBUG
        params["debug" + dataSet.name + "Query"]=dataSet.queryText;        
    }
}

/** Create a string list of array values,
 *  separated by separator and each of them surrounded by a pair surrounders
 */
function createList(array, separator, surrounder, sqlDataType){
    var result = "";

    for(var i=0; i<array.length; i++) {

        if(result.length>0)
            result += separator;

        if(array[i]!=null)
            result += surrounder + array[i] + surrounder;
        else
            result += "cast(null as " + sqlDataType + ")";
    }
    return result;
}

Usage example

In dataset query put your special IN-clause:

select F1, F2
from T1 
where F3='Bubi'
  and ( F4 in (''/*?customers*/) )

In beforeOpen script of the dataset with the IN-clause write:

fulfillInClause(this, "(''/*?customers*/)", "customers", "string", false);

Note that I used a placeholder which allows the query to run also before the replacement (eg. it has quotes as F4 is a varchar). You can build a placeholder that fits your case.

Cardialgia

Beware of sql injection, single quotes (') inside parameter values must be replaces by two single quotes (''). Besides that, a useful idea. – Crucify