Dynamically Generating SQL... And More

Quick Background

Sometimes, it isn't possible to be able to pre-define your query as a resource or an input to report. Sometimes, it becomes necessary to generate your query (whether it's SQL, MDX, or whatever) using a programming language (such as javascript). Examples of this requirement may involve:

• Based on user inputs, a query may require a join to one or more tables
• If there are several optional inputs that effect the way the query is constructed.

What This Tech Tip Demonstrates

This tech tip goes beyond simply demonstrating building a dynamic SQL query in a javascript rule. Here are the highlights:

1. Dynamic SQL generation
2. Iterating over a result-set in a javascript rule to get the column title based on user input
3. Defining a message-field for the column name in a report
4. Defining a message-field for the selected region in a report.

Sample Use Case

Instead of just coding up a sample, I wanted to make sure I had a reasonable use case for the technical tip. The use case was:

1. Gather user input.
2. Based on the users' input, run a query that either returns information on departments, or information on position titles.
3. Do this using only one report specification and one action sequence.

Setup and Requirements

Before beginning this tech tip, please make sure you have downloaded and installed the following:

• Report Design Wizard (RDW) version 1.1.6 or later.
• Pentaho Design Studio (PDS) version 1.1.6 or later.
• Pentaho Pre-configured Installation (PCI) running locally, and the hypersonic database running locally with the SampleData database, version 1.1.6 or later.

Query Analysis

This action sequence will compose the following queries:

If the user selects the department report, the query will look like this:

If the user selects the position report, the query will look like this:

Note that in both cases, the first column is selected and renamed as column1. This is an important thing to note because when the report is built using the Report Design Wizard, column1 will be the column that's used for getting at the data.

Building the Report Using the Report Design Wizard

For the purposes of laying out a quick report, it doesn't matter which query we choose - the result will be the same. The following screenshots walk you through the steps and options to choose in the Report Design Wizard.
Run the Report Design Wizard ( execute the reportwizard.bat|.sh file). (If you are not familiar with the Report Design Wizard, review the Report Design Wizard User's Guide before going forward.)

Otherwise, the report defaults were used to generate this simple report.
STEP 1: Report Title and Description

STEP 2: Select a Datasource and Query

STEP 4: Report Layout

STEP 5: Describing Actual

STEP 5: Describing Column1

STEP 6: Page Setup

STEP 7: Advanced Options

Modifying the report XML

To modify the report to be able to handle the incoming region (for the title) and the substitution of the incoming column title, you could use the Report Designer (not the Report Design Wizard, but our new offering), or you can simply directly modify the report XML document. I'm choosing the latter because the changes are small for this example.

This AraxisMerge image shows the before (on the left) and the after (on the right). The overview of the changes are as follows:

1. Add a report header that displays the region;
2. Change the field type for the column header from label to message-field;
3. And create property references for the incoming parameters.

Adding the Report Header

To add the report header, either type, or paste in the following XML fragment over the existing <reportheader/> line in the XML document:

Changing the Field Type

Why would we want to change the field type? Well, in JFreeReport, a label is simple text. A message-field will do evaluation on the parameters. For this example, we want to evaluate the value of the column header parameter, and display that value. The new field looks like this:

Adding the Property References

Before JFreeReport can use the incoming parameters, they must have a property-ref entry in the <functions> section.

Publish the Report to the Pentaho Pre-configured Installation

That's it. Now, we want to publish our report to the local Pre-configured Installation, with a starter action sequence.

1. From the File menu, select Publish | To Location....
2. You will be prompted with a file system navigation dialog. Navigate to the <Pre-configured Installation root>/pentaho-solutions/samples/reporting directory.
3. Select OK, and the report definition along with a starter action sequence file will be generated in that directory.

Finishing the Action Sequence Using the Pentaho Design Studio

The action sequence that is generated from the Report Design Wizard only sets up the step to run the report we just created. We need to add a few steps to the action sequence to achieve our goal:

1. We need to add a SecureFilterParameter component, which provides the ability to prompt the user for the dynamic values in our report (which region, which column we would like in the query (department or position), and report output format (pdf, xls, or html)).
2. We need to add a Javascript rule that will accept the query column parameter and the region parameter from the SecureFilterParameter component, and builld them into our report query.
3. We need to add a Javascript rule that will use the query resultset to find the title to use on the dynamic column in our report, Position Title or Department.
4. Finally, we need to stitch up the report component to use our newly defined parameters and dynamic query.

We'll use the Pentaho Design Studio to fill out our action sequence. If you are not familiar with the Pentaho Design Studio or action sequences, take time now to review the Pentaho Design Studio User's Guide and Creating Pentaho Solutions Guide. Both can be found on our downloads site.

1. Start the Pentaho Design Studio.
2. The easiest way to work on your new action sequence is to have the pentaho-solutions directory (under the Pre-configured Installation root) set up as a Simple Eclipse Project. From the File menu, choose New... | Project.
3. In the dialog, choose the Simple option in the list, then Project. Click the Next button.
4. Name your project TechTipSolution, uncheck the "use Default" option, and Browse to the <Pre-configured Installation>/pentaho-solutions directory on your local machine. Click the Next button.
5. Click Finish. You should have a new Eclipse project named TechTipSolution in your Navigator view.
6. Navigate to <Pre-configured Installation root>/pentaho-solutions/samples/reporting directory, and open the .xaction file with the name of your report prefixed. In our example, the file name is DynamicColumnSample.xaction, since DynamicColumnSample is what we named our report in the Report Design Wizard.

Now, we can get to work finishing our action sequence.

General Action Sequence Information

1. The first screen displayed is the General information about your action sequence. The fields are pretty self explanatory, fill in this information for your action sequence.

Define Inputs

1. For each parameter, we need an input to hold the list of possible values as well as an input to hold the selected value. Define the following inputs for your action sequence: