Multiple, Multi-Select Parameters

One limitation in Birt is the lack of a simple way to create multiple, multi-select parameters. What I mean by that is when you want to have parameters where the user can select more than one value for a parameter. This can be overcome by writing scripts that modify the query at runtime. There are two ways to do this, but one of them is much more powerful than the other, especially when you want to have multiple parameters.

Here's the first way and I think it is is an inferior solution compared to the second one described below. It's utility seems to be limited to reports with a single parameter. In this method, you write a query with no where clause. Create a report parameter. Make sure to click the Allow Multiple Values checkbox. In the screen shot below, I built the parameter off a data set from the database as opposed to hard-coding in the values.

Next, go to the dataset and click on the scripts tab. Set the drop-down box to 'Before Open'. Enter the following script with the appropriate modifications to match what you've named your parameter.

var endOfQuery = " WHERE workorder.proposal IN ('" + params["proposal"].replace(/,/g,"','") + "')";
this.queryText += endOfQuery;

Another way to view this would be:

var endOfQuery = " WHERE tablename.columnname IN ('" + params["parametername"].replace(/,/g,"','") + "')";this.queryText += endOfQuery;

This will append the where clause onto the end of the query. It may be possible to modify this script to accomodate additional where clauses. It's been a while since I used this method and I may have tried and failed or maybe I never tried it.

My current preferred method is to write the query, but to include the where clause. A script is still required, but write string variables in the where clause. The script replaces these strings with selected parameter values.

The where clause may be something like this:

where tablename1.columname1 in ('xxx') and tablename2.columname2 in ('yyy')

Here's the script.

this.queryText = this.queryText.replace("xxx",params["parameter1"].value.join("','" ))
this.queryText = this.queryText.replace("yyy",params["parameter2"].value.join("','" ))

where parameter1 refers to tablename1.columnname1 and parameter2 refers to tablename2.columname2

I like this for its simplicity and there doesn't seem to be a limitation on the number of parameters that you can have.

A note: no dataset parameter is required when this is used.