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…

OpenText profile picture

OpenText

March 21, 20133 minutes read

Descriptive text explaining the contents of the image.

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.

Attached Image 

Attached Image 

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.

Attached Image 

You can download this example here

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

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

January 31, 2024 4 minutes read
Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

January 18, 2024 4 minutes read
SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

November 21, 2023 3 minutes read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.