January 23, 2007 |
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.
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.
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.
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:
We are going to examine the solution files that Mike used to demonstrate the master\slave parameters. The first thing we need to do is set up the Design Studio so that we can work and debug directly from the sample solutions that come with the PCI. (If you want to use your own solution directories, that's fine too, just modify the following steps to accommodate your structure).
Before we delve into the technical detail of the action sequences, let me first explain the purpose of each solution file, to help give you a better perspective of how this solution works as a whole.
There are several files in our dep-param directory, but the only ones that contribute to the workings of this feature (and that we will concern ourselves with here) are DependentParameterExample.xaction, PositionTitlesForDept.xaction, and DependentParameterTemplate.html. The rest of the files in the directory (if you aren't already familiar with Pentaho solutions) support externalizing your solution's strings for localization, and support the appearance of the solution within the PCI demo. To learn more about these support files in detail, you can browse some of our early tech tips, as well as the Internationalization documentation.
The DependentParameterExample.xaction is the main action sequence. In this action sequence, we set up the Secure Filter Component that will request a region and department from the user. Once the user has chosen the department, we will submit a request to the server to get the list of position titles for that department. Retrieving the department specific position titles happens via the PositionTitlesForDept.xaction. I will get in to details on how the second action sequence gets executed in a minute, but for right now its useful to understand that there is no reloading of the page or the Secure Filter Component when we retrieve the position titles,as that function is an AJAX call.
The way we enable AJAX and the dynamic loading of the dependent parameter, position title, is by using a custom DHTML file as the template for the page that the Secure Filter Component (in our first action sequence) generates. That file is DependentParameterTemplate.html.
That's alot to digest in a very compressed fashion, so let's walk through the action sequences and the DHTML template in the Design Studio. Step by step, we will put the bits together and help you understand how Mike came up with this solution.
The DependentParameterExample action sequence controls our solution. Let's dig in and see what's in there.
So the action sequence is really not that special, intentionally so for demonstration purposes. The real key to this solution is in the template that we assigned to the Secure Filter Component for generating the parameter page. Let's take a look at DependentParameterTemplate.html next.
The DependentParameterTemplate.html file is a custom template file from which the parameter page is built. Some of what we will see in the template file is boilerplate, while much of the Javascript and the form control definitions are specific to this solution. I will try to distinguish between the two as we examine the code.
<form name="parameter-form" id="parameter-form" method="POST"> <table width="100%" style="padding:5px;"> <tr> <td class="portlet-font">The department selections filter the positions.</td> </tr> <tr> <td class="portlet-font">Region: {REGION}</td> </tr> <tr> <td class="portlet-font">Department: {DEPARTMENT}</td> </tr> <tr> <td class="portlet-font">Position: {POSITIONTITLE}</td> </tr> <tr> <td><br/><input type="button" name="go" class="portlet-form-button" value="Run..." onClick="doForm()"/></td> </tr> </table> {solution} {action} {path} </form> |
<script> <![CDATA[ var deptControl = returnObjById("DEPARTMENT"); deptControl.onchange=departmentChangeFunction; function departmentChangeFunction() { paramService(new Array( "dept", deptControl.value, "solution", "samples", "path", "reporting/dep-param", "action", "PositionTitlesForDept.xaction" ), "parseSOAPResultSet(\"POSITIONTITLE\", ", ""); } ]]> </script> |
<script> <![CDATA[ var url=unescape('ViewAction?'); var target=unescape(''); function doForm() { var submitUrl = url; var form = document.forms['parameter-form']; var elements = form.elements; var i; for( i=0; i<elements.length; i++ ) { if( elements[i].type == 'select-one' || elements[i].type == 'text' || elements[i].type == 'hidden') { submitUrl += '&' + elements[ i ].name + '=' + escape( elements[ i ].value ); } else if( elements[i].type == 'radio' ) { if( elements[i].checked ) { submitUrl += '&' + elements[ i ].name + '=' + escape( elements[ i ].value ); } } else if( elements[i].type == 'checkbox' ) { if( elements[i].checked ) { submitUrl += '&' + elements[i].name + "=" + escape( elements[i].value ); } } else if( elements[i].type == 'select-multiple' ) { var options = elements[i].options; var j; for( j=0; j!=options.length; j++ ) { if( options[j].selected ) { submitUrl += '&' + elements[i].name + '=' + escape( options[ j ].value ); } } } } if( target == '' ) { document.location.href=submitUrl; } else { window.open( submitUrl, target ); } return false; } |
function returnObjById( id ) { if (document.getElementById) var returnVar = document.getElementById(id); else if (document.all) var returnVar = document.all[id]; else if (document.layers) var returnVar = document.layers[id]; return returnVar; } |
function sendRequest( url, query, func, dependentFunction ) { http_request = false; var returnType = "text/xml"; if (window.XMLHttpRequest) { // Mozilla, Safari,... http_request = new XMLHttpRequest(); if (http_request.overrideMimeType) { http_request.overrideMimeType(returnType); } } else if (window.ActiveXObject) { // IE try { http_request = new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { try { http_request = new ActiveXObject("Microsoft.XMLHTTP"); } catch (e) {} } } if (!http_request) { alert('Cannot create XMLHTTP instance'); return false; } http_request.onreadystatechange = function() { pentahoResponse(http_request, func, dependentFunction); }; http_request.open('POST', url, true); http_request.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); http_request.setRequestHeader("Content-length", query.length); http_request.setRequestHeader("Connection", "close"); http_request.send(query); } |
function pentahoResponse(http_request, func, dependentFunction) { if (http_request.readyState == 4) { if (http_request.status == 200) { var content = http_request.responseText; eval( func+'content )' ); eval( dependentFunction ); } else { eval( func+'( "There was a problem with the request." )' ); } } } |
function paramService( params, func, dependentFunction ) { var url = "/pentaho/ServiceAction"; var query = ""; if( params ) { query += "?"; for( idx=0; idx<params.length; idx+=2 ) { query += "&" +escape( params[idx] ) + "=" + escape( params[idx+1] ); } } return sendRequest( url, query, func, dependentFunction ); } |
function parseSOAPResultSet(dependentControlName, content) { CDATA_START_TAG = "\<DATA-ITEM\>\<!\[CDATA\["; CDATA_END_TAG = "\]\]\>\</DATA-ITEM\>"; dependentControl = returnObjById(dependentControlName); dependentControl.options.length = 0; index = 0; while (content.indexOf(CDATA_START_TAG) >= 0) { cdataBeginIdx = content.indexOf(CDATA_START_TAG); cdataEndIdx = content.indexOf(CDATA_END_TAG); text = content.substring(cdataBeginIdx+CDATA_START_TAG.length, cdataEndIdx); content = content.substring(cdataEndIdx+CDATA_END_TAG.length); dependentControl.options[index] = new Option(text, text); index++; } } ]]> </script> |
This implementation will support more than one master\slave relationship. For instance, what if we wanted the list of Position Titles to be driven by the Department that gets chosen, and the list of Departments to be filtered by the Region that was chosen? This is an example where the chained relationship allows a cascading effect to happen to drive multiple field selections on the form. This brings us back to the dependentFunction parameter that we pass down ultimately to the pentahoResponse function. This parameter should be the name of the function that the slave's onchange event points to, and will be executed as soon as the SOAP response is parsed (See the pentahoResponse() function). We need to pass and execute this function in this way, because the onchange event for the controls only fires when the USER changes a value in the control, NOT when the value changes as a result of script execution.
The following code demonstrates this concept, using the example of Region driving Department selections, and Department driving Position Title selections:
<script> <![CDATA[ var regionControl = returnObjById("REGION"); regionControl.onchange=regionChangeFunction; var deptControl = returnObjById("DEPARTMENT"); deptControl.onchange=departmentChangeFunction; function departmentChangeFunction() { paramService(new Array( "dept", deptControl.value, "solution", "samples", "path", "reporting/dep-param", "action", "PositionTitlesForDept.xaction" ), "parseSOAPResultSet(\"POSITIONTITLE\", ", ""); } function regionChangeFunction() { paramService(new Array( "region", regionControl.value, "solution", "samples", "path", "reporting/dep-param", "action", "DepartmentsForRegion.xaction" ), "parseSOAPResultSet(\"DEPARTMENT\", ", "departmentChangeFunction()"); } ]]> </script> |
Note that you now need another action sequence, to return to you the list of departments that you are requesting.
Once the user chooses a department, we issue a call tot he ServiceAction, executing an action sequence that issues a query for the list of appropriate position titles. There is nothing fancy about this last action sequence, but I wanted to include a look at it here, so there is nothing left unclear.
If I haven't mentioned it yet, this code is written specifically geared toward combo boxes as the controls that we manipulate. You would have to tweak a bit of the code, notably the parseSOAPResultSet() function to get this to work with radio buttons, check boxes, etc.