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.

About these ads

14 Comments on “Adding Parameters to the Pentaho MongoDB Report”

  1. Bin Lin says:

    Bill,
    Can groovy scripting data source access the session variables stored in pentaho session?

  2. Bin Lin says:

    I created a parameter named “from”, which corresponds to a field “from” in Mongo collection. Below is the script I wrote:

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

    def mongo = new Mongo(“mymongo.usa.net”,27017)
    def db = mongo.getDB(“gws2″)
    def auth = db.authenticate(“myuser”,”mypassword”.toCharArray())
    def log = db.getCollection(“log”)

    def columnNames = ["_id", "phase", "subj", "tenant", "from", "src_ip", "action"] as String[]
    def columnTypes = [String.class, String.class, String.class, String.class, String.class, String.class, String.class] as Class[]

    TypedTableModel model = new TypedTableModel(columnNames, columnTypes)

    //use parameter to search data
    def docs = log.find(new BasicDBObject(“from”, dataRow.get(“from”)))

    while (docs.hasNext()) {
    def doc = docs.next()

    model.addRow([ doc.get("id"), doc.get("phase"), doc.get("subj"), doc.get("tenant"), doc.get("from"), doc.get("src_ip"),doc.get("action") ] as Object[])
    }
    }
    docs.close()

    Any way
    model
    However when I ran report, it always throw exception:
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Evaluation error
    at org.pentaho.reporting.engine.classic.extensions.datasources.scriptable.ScriptableDataFactory.queryData(ScriptableDataFactory.java:219)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:135)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:85)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:434)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:326)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getValues(DefaultListParameter.java:119)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getDefaultValue(DefaultListParameter.java:172)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validateSingleParameter(DefaultReportParameterValidator.java:157)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validate(DefaultReportParameterValidator.java:140)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.validateParameter(ParameterReportControllerPane.java:683)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.reinit(ParameterReportControllerPane.java:506)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.setReport(ParameterReportControllerPane.java:404)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportController$ReportUpdateHandler.propertyChange(ParameterReportController.java:92)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:339)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:347)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:276)
    at java.awt.Component.firePropertyChange(Component.java:8248)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane.setReportJob(PreviewPane.java:1120)
    at org.pentaho.reporting.designer.core.editor.preview.ReportPreviewComponent.updatePreview(ReportPreviewComponent.java:232)
    at org.pentaho.reporting.designer.core.editor.ReportRendererComponent.showPreview(ReportRendererComponent.java:466)
    at org.pentaho.reporting.designer.core.ReportDesignerFrame$FrameViewController.setPreviewVisible(ReportDesignerFrame.java:822)
    at org.pentaho.reporting.designer.core.actions.global.ShowPreviewPaneAction.actionPerformed(ShowPreviewPaneAction.java:76)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2028)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2351)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.JToggleButton$ToggleButtonModel.setPressed(JToggleButton.java:291)
    at javax.swing.AbstractButton.doClick(AbstractButton.java:389)
    at com.apple.laf.ScreenMenuItemCheckbox.itemStateChanged(ScreenMenuItemCheckbox.java:178)
    at java.awt.CheckboxMenuItem.processItemEvent(CheckboxMenuItem.java:364)
    at java.awt.CheckboxMenuItem.processEvent(CheckboxMenuItem.java:332)
    at java.awt.MenuComponent.dispatchEventImpl(MenuComponent.java:337)
    at java.awt.MenuComponent.dispatchEvent(MenuComponent.java:325)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:687)
    at java.awt.EventQueue.access$000(EventQueue.java:85)
    at java.awt.EventQueue$1.run(EventQueue.java:643)
    at java.awt.EventQueue$1.run(EventQueue.java:641)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    at java.awt.EventQueue$2.run(EventQueue.java:657)
    at java.awt.EventQueue$2.run(EventQueue.java:655)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:654)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:296)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:196)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:188)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
    ParentException:
    org.apache.bsf.BSFException: exception from Groovy: groovy.lang.MissingPropertyException: No such property: “from” for class: expression
    at org.codehaus.groovy.bsf.GroovyEngine.eval(GroovyEngine.java:97)
    at org.apache.bsf.BSFManager$5.run(BSFManager.java:445)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.apache.bsf.BSFManager.eval(BSFManager.java:442)
    at org.pentaho.reporting.engine.classic.extensions.datasources.scriptable.ScriptableDataFactory.queryData(ScriptableDataFactory.java:205)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:135)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:85)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:434)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:326)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getValues(DefaultListParameter.java:119)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getDefaultValue(DefaultListParameter.java:172)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validateSingleParameter(DefaultReportParameterValidator.java:157)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validate(DefaultReportParameterValidator.java:140)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.validateParameter(ParameterReportControllerPane.java:683)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.reinit(ParameterReportControllerPane.java:506)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.setReport(ParameterReportControllerPane.java:404)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportController$ReportUpdateHandler.propertyChange(ParameterReportController.java:92)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:339)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:347)
    at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:276)
    at java.awt.Component.firePropertyChange(Component.java:8248)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane.setReportJob(PreviewPane.java:1120)
    at org.pentaho.reporting.designer.core.editor.preview.ReportPreviewComponent.updatePreview(ReportPreviewComponent.java:232)
    at org.pentaho.reporting.designer.core.editor.ReportRendererComponent.showPreview(ReportRendererComponent.java:466)
    at org.pentaho.reporting.designer.core.ReportDesignerFrame$FrameViewController.setPreviewVisible(ReportDesignerFrame.java:822)
    at org.pentaho.reporting.designer.core.actions.global.ShowPreviewPaneAction.actionPerformed(ShowPreviewPaneAction.java:76)
    at javax.swing.AbstractButton.fireAction

    • billdback says:

      Is this code you typed into Report Designer? The quotes in your comment aren’t true ASCII quotes until you get down to the addRow line in your script. It could be a cut/paste error into the comments here, but check to make sure they show up as strings in the editor (should turn pink). If the color of different strings is different in the editor, then you know there is a problem. If this isn’t the problem, please follow-up in the support system.

  3. Michael says:

    Hi,

    I was able to create the example and have it working on the BI server reading from mongodb. I’m using this as a work around due to No capability to add mongodb datasource in PAC BI server.

    The question I have is how do I add multiple parameters to the report (syntax) using groovy. I have 1 parameter and want to add time window with one more parameter. Any help would be appreciated.

    Mike

  4. umamaheshwar says:

    please tell me how can run this with java

    • billdback says:

      I don’t really understand the question. You can’t use Java as a scripting language in PRD.

      • Michael Martinez says:

        You select the Scripting option for datasource and choose groovy.

        Ok I’m stuck on this issue. I have date-range in mongo and I have created a date filter report which I thought was working but I see that when I change the start date it returns all the data for my chart. I’ve been killing myself trying to figure out why when I change the start date it isn’t recognized as a date parameter or else it retains it’s original values and doesn’t refresh. Is there a setting I’m missing for this regarding parameters?

        I posted to community forums and to jira as a bug and they say it isn’t a bug. I feel it is since you can’t drill into a particular date window using groovy params with dbbasicobject. If you do you will return all rows wich isn’t good.

        reference:
        http://forums.pentaho.com/showthread.php?145839-Bug-in-date-picker-using-MongoDB and
        http://jira.pentaho.com/browse/PRD-4611

        That’s one issue. The other issue I have is that when using groovy inside PRD the groovy script doesn’t close the cursor connection with the .close command at the end. Killing myself on how to correctly kill this connectiion in mongo when using the scripting solution via groovy. . When using Kettle and I see the connections just like PRD but after completion the connection is killed Not in PRD. It stays open. To me this is a bug in scripting data connection in PRD. HAs anyone seen this when using MONGO with PRD. 4.8 bi server with 3.9.1 PRD.

      • billdback says:

        First off, this isn’t a help forum, so I’m only going to address this post. I believe my example is missing a db.close(), but haven’t had a chance to try it yet. I’m hoping to try it soon and update the examples. If you are using the basic script I show, that is most likely your problem.

  5. Michael Martinez says:

    Hi,

    I realize that this isn’t a help forum. Sorry for the ramble. I’ve tried to put the db.close() in after the doc.close() and receive compilation errors, also tried to input after model.

    Your solution was a big win for us to start retrieving data from mongodb. Once mongodb datasource can be added to the Admin console it should be better in 5.0.

    Michael

    • Michael A Martinez says:

      Well never heard back from Bill. Knocked my head a little, tested and found the solution. Not tricky just pretty much a close command and the connection is cleaned up.

      Now how to drill into a time window is the next challenge. Date picker not working with java driver when trying to drill into a time window that is in the middle of a data window.

      Reference.

      http://jira.pentaho.com/browse/PRD-4611

      • billdback says:

        Sorry, but as I mentioned before this is not a help forum and I’m trying to get a book out the door. Glad you got your problem solved.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 295 other followers