A very common goal in dynamic reporting is to give the user the ability to specify some particular criteria to filter a report on, before the report data is queried for. These criteria are called parameters, and the Secure Filter Component in the Pentaho platform allows solution developers to achieve this goal. Taking the feature one step further, users many times would like to filter or choose a second set of criteria, based on the first parameter they chose. For instance, Jody manages the Western division of her company. The Western division has different products than the Eastern division. So the first parameter Jody would like to choose is the Western division. Based on that selection, Jody would like to see only Western division products. This is often called a master\slave relationship, where one set of slave data is dependent on a master set of related data.
This article explains a sample solution (I'll call it the dep-param solution) that Mike D'Amour created for providing the master\slave feature while still utilizing all of the power of the platform's Secure Filter Component. Mike's solution is very clean, using AJAX to dynamically populate a combobox with the slave filter selections on the parameter page, once the master selection is chosen. I'll get into more details in the article as we go about what this sample can do out of the box, and where it can be extended.
Resources Before You Get Started
The following software is what I used to build out this article. I highly recommend you set up these bits in your environment before you get started. While it may seem like a bit of work, each project has a very simple install and setup.
- Pentaho Design Studio (PDS) version 1.2 or later.
- Pentaho Pre-configured Installation (PCI) running locally, and the hypersonic database running locally with the SampleData database, version 1.2 or later.
- The dep-param Solution Files, courtesy of Mike D'Amour. (Pentaho team developer)
Sample Use Case
The Pentaho sample data is quite simplistic. The table that we will use in this exercise is QUADRANT_ACTUALS. The QUADRANT_ACTUALS table contains Region, Department, PositionTitle (job title), Actual, Budget and Variance data. Each department has a unique list of position titles.
We want to be able to create a report that will show actual and budget data for a selected position title within a department. We will set up the solution so that the user must first select a department, which will then filter the list of position titles that they choose from.
For the sake of keeping this solution simple and focusing on what needs to be done to enable the master\slave parameter feature, the output for the data will result in a plain table of data in the browser, rather than a new report. Some of the input data is also hard coded, although we easily could define additional SQL Lookup Rules for database retrieval instead. Also, the dep-param solution we are demonstrating here only accounts for one master\slave set of dependent fields, and the control used on the page must be a combo box. Once we have explained how this specific problem is solved, we will discuss how you can extend this solution to handle multiple dependencies (for example, select a region, that then filters departments, that then in turn filters position title). We will also talk briefly about how to tweak the solution to accommodate other control types, such as radio buttons, check boxes, etc.
Let's get started!
As I mentioned in the "Resources.." section, it's important at this point that you have the PCI and the Pentaho Design Studio installed and ready to run somewhere on your computer. If you have any questions on how to set these things up, they all have installation and user guides located at http://www.pentaho.org/download/latest.php. From here forward I will assume you have the necessary tools for the exercise.
Install the dep-param Solution
First thing you will want to do is install the dep-param solution for this article in the solutions folder that came with the PCI demo. Download the zipped file from this page, and extract the content to the following directory: <HOME>/pentaho-demo/pentaho-solutions/samples/reporting
with <HOME> being the top level drive and/or directory that you installed the PCI to. The structure should look similar to the following path once the files are extracted: <HOME>\pentaho-demo\pentaho-solutions\samples\reporting\dep-param\<all the sample solution files here>
Once you have installed the solution files installed, start your Pentaho server if you haven't already. If your server was running when you installed the dep-param solution, just Publish To Repository (from the demo home page, choose Admin | Repository), and you will be all set.
Navigate to the dep-param solution, so we can test that the solution indeed works:
- Using your favorite browser, navigate to the PCI demo homepage. If you installed the PCI locally and chose the default set up, that URL would be http://localhost:8080. If you changed any of the defaults, or installed to a different domain or port that URL will be slightly different. If you have any trouble finding the PCI home page, consult the Getting Started with the BI Platform documentation.
- From the PCI home page upper right corner, select Go | Solutions | Reporting Samples | Dependent Parameter Examples .
- From this page, select Dynamic Dependent Parameter Page. You should see a prompt for parameters, similar to the following screenshot: