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


Follow

Get every new post delivered to your Inbox.

Join 294 other followers