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