How to Pass a Multi-Select Parameter to Subreport

This week I was asked a question regarding multiple value list box parameters. Additionally they wanted to know how to pass these values from a master report to a sub-report via drill-through links. I had used these parameters in past reports but I hadn’t tried to pass all the selected values to a sub-report until now.

When you create a list box parameter that allows multiple values the result is a comma delimited list as shown below.

The problem starts when this is passed to the sub-report. Only the fist value of the list shown above is passed along.

The solution to this is simple. From the initialize() of the master report we create a String of these values that is separated by some character not included in the data set. For my example I used the tilde.

importPackage(Packages.java.io);
importPackage(Packages.java.util);
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.engine.api);
importPackage(Packages.org.eclipse.birt.report.engine.api.script.element);

myParam       ="NewParameter";//report parameter
paramLen      = reportContext.getParameterValue(myParam).length;var thisParam =newString();for(i =0; i < paramLen; i++){if(i >0){
    thisParam = thisParam +"~"+ reportContext.getParameterValue (myParam)[i];}else{
    thisParam = reportContext.getParameterValue (myParam)[i];}}

reportContext.setPersistentGlobalVariable("globalParameterVariable", thisParam);

The code above will take the parameter we are trying to parse and treat it like an array. This way we can get how many values it holds. Then in the for loop we can add each value adding it to the variable holding the tilde separated list. Finally we set the newly created list as a persistent global variable.

Now I can create my drill-through hyperlink. In the expression builder for the parameter we are passing we can recall the PGV and pass it to the sub-report.

var values = reportContext.getPersistentGlobalVariable("globalParameterVariable");
values;

Now the data is being passed as a string we need to make sense of it in the beforeOpen() of the sub-report data set. I’ve chose to split the string into an array rather than parse the String manually so it will be easier to work with.

if(BirtStr.charLength(params["NewParameter"].value)>0){var values =newString(params["NewParameter"].value);var temp =newArray();
  temp = values.split("~");var length = temp.length;this.queryText ="Select * from customers "this.queryText +=" where  customername IN ("for(i=0; i<length; i++){var thisCustomer ="'"+ temp[i]+"'";this.queryText += thisCustomer;if(i != length-1){this.queryText +=","}}this.queryText +=" ) "}

The first thing we do with the code above is make sure that we have some value in the parameter by checking the value. If we do then I continue on to split the string into an array based on the tilde as my separator. Then I grab the length of the array for the loop later on.

After all of our data is in the array I’m going to modify the SQL query. The first part of the query is static. It tells SQL what rows we are selecting from which table. Then I loop through through the array adding the values to the query.

If I had selected the same four customers from the first screen shot then this code will produce the following query.

select*from customers
where customername IN ('Atelier graphique','Australian Collectiors, Co.','Baane Mini Imports','Havel & Zbyszek Co')

Which gives us the following results in our sub-report.

You can download this example here

If you have any questions or comments please leave them below or ask on the forums.