Getting session variables when using the PRD scriptable data source

In a previous post I was asked in the comments how to use a session variable as a parameter when using the scriptable data source in Pentaho Report Designer.  I finally figured it out and thought I’d share with everyone. The solution is relevant for scriptable data sources, not just those that use MongoDB, so I’ll leave that complexity out of this discussion.

Pre-conditions

Before this will work you need to have some way you are getting a session variable set that you want to use.  You can create an action sequence that runs when a user logs in or, if you are using single sign-on, you can set it during the log in process that way.  These are describe other places, so I won’t go into how to set the session variable.  For the sake of this example, lets assume you have somehow set a session variable for a user called “Region” that has the region that applies to the user, North, South, East, or West.

Create a Report Parameter

The first thing to do is to create a report parameter that will get the session value.  Then set the Name and Value Type as appropriate.  The key step is to set the Default Value Formula to =ENV(“session:Region”). The ENV function will get the session value for the attribute with the name “Region”.  You should also set the parameter to be Hidden by checking the box, although while testing it can be handy to have it unchecked.  Note that if you preview in report designer this will have no value (there are ways to set it), so a default value can be handy.  I don’t recommend deploying to production with a valid default, though.

The following figure shows getting the Region value from the session.

Image

Using the Parameter

Using the parameter from your script is simple.  The scriptable interface provides a dataRow object with a .get(String name) command to get the value.  So, to get the value of Region at run time use the following line (in Groovy):

def region = dataRow.get(“Region”)

Then just use the value in the script.


Adding Parameters to the Pentaho MongoDB Report

In the previous post, I showed how to create a Pentaho report with MongoDB as a data source using Groovy Scripting.  That’s pretty handy, but a lot of users want to be able to have parameters they can use to filter the date.  Adding parameters is pretty easy, but not well documented, so here’s how it’s done.  These instructions assume you are fairly familiar with Pentaho Report Designer.  If you need the data file and instruction on setting up the data, see the previous post.

To make it easy for the user, let’s create a query to use for the parameter values.  You could do another MongoDB query, but I’ll just use a table to keep things simple.  Under the data source tab add a new data source of type Table, create a query, and then add values for each of the regions: North, South, East West.

Regions for the parameter values.

Next, create a new parameter named Region that the user will select the Region to filter on.  The setting should look like the following:

Region parameter settings

Now you’re ready to use the parameters in your query.  Since the query is a Groovy script you have to use a special call rather than the traditional ${parameter} approach that you might be used to in SQL queries.  Specifically you will use the rowData.get("parameter-name"). The following is the new version of the query:

import com.mongodb.*
import org.pentaho.reporting.engine.classic.core.util.TypedTableModel

def mongo = new Mongo()
def db = mongo.getDB(“pentaho”)
def sales = db.getCollection(“sales”)

def columnNames = ["Region", "Year", "Q1", "Q2", "Q3", "Q4"] as String[]
def columnTypes = [String.class, Integer.class, Integer.class, Integer.class, Integer.class, Integer.class] as Class[]

TypedTableModel model = new TypedTableModel(columnNames, columnTypes)

def docs = sales.find(new BasicDBObject(“region”, dataRow.get(“Region”)))
while (docs.hasNext()) {
def doc = docs.next()
model.addRow([ doc.get("region"), doc.get("year"), doc.get("q1"), doc.get("q2"), doc.get("q3"), doc.get("q4") ] as Object[])
}
docs.close()

model

Download the report description to see all the details. When run it looks like the following:

Example report with queries.


Creating Pentaho Reports from MongoDB


So you’ve made the move and started using MongoDB to store unstructured data.  Now your users want to create reports on the MongoDB databases and collections.  One approach is to use a Kettle transformation that retrieves data from MongoDB for reports.  This approach is documented on the Pentaho Wiki.  However, I want to use the MongoDB database directly without dealing with Spoon and Kettle transformations.  Fortunately Pentaho Reporting also supports scripting with Groovy built in.  This tutorial will show you how to create a report against MongoDB data using the Java drivers and Groovy scripting.

Pre-Conditions
You should already have mongodb installed and accessible.  I’m running on the same machine with the default settings, so vary the code as needed for your configuration.
You also need to put the mongo-java-driver-2.7.2.jar file in the libraries for Report Designer and the BA Server
$PENTAHO_HOME/design-tools/Pentaho Report Designer.app/lib/
$PENTAHO_HOME/server/biserver-ee/tomcat/webapps/pentaho/WEB-INF/lib
Restart the app and BA Server if they are running to pick up the new .jar files.
Setting Up
The first thing you need is some data.  I’ve created an input file of sales by region and year to use as an example.  Download and import the data using the mongoimport command:
> mongoimport -d pentaho -c sales data.json
Verify that the data has been successfully imported by opening the mongo shell and using the following commands:
> use pentaho
> db.sales.find();
You should see a list of documents that were added.
Creating the Report
  1. Using Pentaho Report Designer, create a new report.
  2. Add a data source and choose Advanced -> Scriptable
  3. Select groovy as the language and click the (+) for a new query
  4. Enter the following code as the script

import com.mongodb.*
import org.pentaho.reporting.engine.classic.core.util.TypedTableModel
def mongo = new Mongo()
def db = mongo.getDB(“pentaho”)
def sales = db.getCollection(“sales”)

def columnNames = ["Region", "Year", "Q1", "Q2", "Q3", "Q4"] as String[]
def columnTypes = [String.class, Integer.class, Integer.class, Integer.class, Integer.class, Integer.class] as Class[]

TypedTableModel model = new TypedTableModel(columnNames, columnTypes)

def docs = sales.find()
while (docs.hasNext()) {
def doc = docs.next()
model.addRow([ doc.get("region"), doc.get("year"), doc.get("q1"), doc.get("q2"), doc.get("q3"), doc.get("q4") ] as Object[])
}
docs.close()

model

This will read the data from MongoDB and return the table model needed by the reporting engine.
From here it’s just standard report generation and publishing, which is described in the Pentaho documentation.
Go ahead and download the report definition.  You’ll need to have mongod running and on the default (or change your script).  The results look something like the following:

Sales by Region from MongoDB


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.

Follow

Get every new post delivered to your Inbox.

Join 295 other followers