Passing the Select Multiple Option Listbox Parameters to a Dataset Query in Birt

on

This is another brief guide on how to use the multiple select option on the List parameter type and passing the values to datasets in Birt.
Using the multiple select is not as simple as click and use , as the values are in the form of an array and any attempt to use this parameter value directly in the dataset query will result in errors.

So how do we do this ?

The trick ( and I mean trick because this method is not how the Birt documentation tells us to utilize parameters !! ) is to process the array into a comma separated string before the dataset is created and insert it directly into the Query Text.
I will use the CLASSICMODELS sample Dataset from the Birt docmentation for my example, and have included a working example file below.

Steps

  1. Create a List type parameter.
  2. Check the Multiple Select Option
  3. In the BeforeOpen script for your DataSet add the below code: 
     
// Get the Parameter
var parameters = params["vehicle_types"].value;

// Join together as a comma seperated string
var text_to_replace = "'" + parameters.join("','") + "'";

/* Search and replace the Sql Query text for the replace token we have used "$$vehicle_types$$" 
which will insert our comma seperated set of selected values. 
*/
this.queryText = this.queryText.replace("$$vehicle_types$$",text_to_replace);
  4. Add the token to be replaced into your Dataset Query - I will add $$vehicle_types$$ .
  5. Save & Preview
  6. Select multiple options by hold down the CTRL key and clicking multiple options.
  7. The results set should return only rows filtered using the inserted options.
You can download the report file I created here

Comments

Post a Comment