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:
- Dynamic SQL generation
- Iterating over a result-set in a javascript rule to get the column title based on user input
- Defining a message-field for the column name in a report
- 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:
- Gather user input.
- Based on the users' input, run a query that either returns information on departments, or information on position titles.
- 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:
select department as column1, sum(actual) from quadrant_actuals where region = '
' group by department order by actual desc
If the user selects the position report, the query will look like this:
select positiontitle as column1, sum(actual) from quadrant_actuals where region = '
' group by positiontitle order by actual desc
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.)
IMPORTANT NOTE: In Step 5, "Describing Column1", you'll note that the display name was set to $(columnTitle). In Step 5, "Describing Actual", you'll see the display name is set to Actual. This is the only change we will make to this report definition. Otherwise, the report defaults were used to generate this simple report.
STEP 1: Report Title and Description