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.

Mondrian and Pentaho Analytics – A book is in the works

One of the biggest challenges many new users to Pentaho have is finding good documentation.  A variety of documentation exists, but it’s scattered among various sources, such as the Pentaho Knowledge Base, the Mondrian site, the Pentaho wiki, and a variety of blogs.  While much of this content is good, wouldn’t it be nice to have one place to go to for all things Mondrian?  I’m happy to say that a book is on the way.

It’s still very early in the process, but Julian Hyde, and Nick Goodman, and myself are collaborating to put together a book that covers the nuts and bolts of Mondrian including:

  • Setting up and running Mondrian
  • Creating schemas
  • Scaling Mondrian
  • Security
  • Integrating Mondrian with applications and enterprise tools

The as yet to be named book will be available from Manning and we hope to have some early access versions available this summer.  Early feedback is always welcome so we can make this the best book possible.

If there are specific topics you’d like to see in the book, please mention them in the comments.


Get every new post delivered to your Inbox.

Join 294 other followers