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.

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.TypedTableModeldef 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