Using a Textbox Parameter for Multi-Value Selections in Pentaho Report Designer

Pentaho Report Designer allows you to create parameters that users can use to filter the data.  Usually this is done by providing buttons or lists or other standard UI types to select the values.  However, there are times when the list of possible values is quite long, making a selection list to long to be feasible.  In these cases it would be ideal to provide a simple text box and then enter the list of matching values to use.  Unfortunately a text box is associated with a single value and not a list.  But not to fear, there is a straightforward solution using a hidden parameter and a post-processing formula.

Lets say you want to look at information from an HR database that contains 1000 employees with dozens of records each an you only want the information for a few employees.  So you create a report with the query:

 
select * from employees where employee_id in (${empid});

You then create a parameter called empid with a text box and type of string and run the report.  Typing in 1002, 1005, 1007 you only get data back on the employee with number 1002.

To fix this problem, create a second parameter that we’ll call empid_array.  This parameter has a type of Object and a Post-Processing Formula of =CSVARRAY([empid];0;”,”;).  Check the box that makes the parameter hidden so that it won’t show up on forms.

This formula says to convert the parameter named empid to an array using a comma as a separator and don’t quote.

Now modify your query to use the empid_array parameter:

 
select * from employees where employee_id in (${empid_array});

Now run your report and enter 1002, 1005, 1007 and you should see all records for all of the employees.

---