Easy way to pass down all parameters to Pentaho subreports

Reblog from http://diethardsteiner.blogspot.co.uk/2014/01/easy-way-to-pass-down-all-parameters-to.html

Easy way to pass down all parameters to Pentaho subreports
At yesterday’s Pentaho London User Group (PLUG) meetup I discussed with Thomas Morgner again the topic sub-reports. For a long time I’ve tried to explain that sub-reports are a concept that shouldn’t be exposed to the actual report designer. We should all just start with a blank canvas (with no bands) and add objects (tables, crosstabs, charts, etc) and then link these objects to the data sources (frankly quite similar to how it is done in some other report designers or CDE). And parameters should just be available everywhere without having to map them to the specific objects.
Anyways, during this discussion Thomas mentioned that there is actually no need to specify all the parameters in the parameter mapping dialog in the subreport. You can just add one mapping with stars (*) in it and all parameters will be passed down automatically to the subreport. Frankly, I was puzzled and astound about this … I remembered the days when I was working on monster reports with 30 or 40 subreports and all the time had to define 17 or so parameters for each of them. Why this was not documented anywhere, I was quite wondering. Needless to say that this is a real time safer!
My next question was then, why this was not the default behaviour? If I don’t specify any parameter mappings, PRD should just pass down all parameters by default. So, I just created this JIRA case therefore and we all hope that this will be implemented as soon as possible. Please vote for it!

So excited as I was about these news, this morning I had to quickly test this approach. Here the mapping in the subreport:

1Then I just output the values in the details band of the sub-report:

2And here is how the preview looks:

3Now that was easy! Thanks Thomas!


Book Review: Pentaho 5.0 Reporting by Example: Beginner’s Guide

Hello friends today I am going to review Pentaho 5.0 Reporting by Example: Beginner’s Guide  book:

Pentaho5_Libro cover

Below you can check the link to purchase it:


Book review by: David Fombella Pombal (twitter: @pentaho_fan)

Book Title: Pentaho 5.0 Reporting by Example: Beginner’s Guide

Authors: Mariano García Mattío, Dario R. Bernabeu

Paperback: 329 pages

I like this book because if you are a noob in Pentaho Reporting you will gain a lot of knowledge of this excellent tool, besides if you are advanced with PRD you can use it as reference book.

The best of this book is that uses Pentaho 5 recent released version and shows how to publish and interacting with reports created with Report Designer 5 into the new Business Analytics server.

You should read this book because you will learn all details of this awesome tool. Maybe you have created reports using PRD but after reading this book you will improve your reporting skills.

Rating: 8 out of 10

Pentaho 5

Chapter List

Chapter 1 – What is Pentaho Report Designer?
In this chapter it is explained what is Pentaho Report Designer and some examples of typical reports are listed: Transactional Reporting, Tactical Reporting, Strategic Reporting, and Helper Reporting. Besides it is interesting to learn about the different versions of PRD along history.

Chapter 2 – Installation and Configuration
This chapters shows us how to download, install, and configure Pentaho Reporting Designer new version 5.0. Maybe it is not funny but this chapter is useful for beginners.

Chapter 3 – Start PRD and the User Interface (UI) Layout
In this chapter you will learn how to start PRD in different operating systems. Different areas of its UI are displayed too.


Chapter 4 – Instant Gratification – Creating Your First Report with PRD
Explains how to create an initial report, how to define its data sets and how to configure the report’s sections, how to add and set insertable objects and functions, and how to preview and export your report in all available formats suchs as CSV, PDF, HTML, Excel/Excel 2007. This topic is excellent to introduce non technical users to all visualization capabilities that Pentaho Report Designer includes.

Chapter 5 – Adding a Relational Data Source
Here we will create a copy of the report created in the previous chapter and modify it so that it takes its data from a relational source. We will explain what JDBC is, what a JDBC driver is, and how it is used. Using a JDBC driver you can connect to all available database systems: MySQL, Postgres, Oracle, Infobright, Vertica, Monet, Hadoop, SQL Server, PALO, Hypersonic, CouchDB…

Datasources Pentaho5

Chapter 6 – Adding Groups
In this chapter, we will use the report we created in the previous chapter and configure it to work with groups. The information included in this chapter allows us creating reports with partial group calculations and subtotals.

Chapter 7 – Adding Parameters
This chapter will be dedicated to parameters. Through the use of parameters, the final user will be able to interact with the report. A parameter is usually represented through a user interface component, which allows the selection or input of values. Pentaho Report Designer eases the task of parameter creation by providing us a simple and intuitive interface such as : Dropdowns, MultiValue Lists, Date Pickers, Text search components…

Parameters pentaho5

Chapter 8 – Using Formulas in Our Reports
In this chapter, we will talk about formulas (PRD uses OpenFormula ). We will explain how to create them and use them. As in previous chapters, we will use a practical example to guide us as we explain how to work with formulas, creating general-use formulas that we can use as an object and specific-use formulas that we can apply to our objects’ styles and attributes. By the end of the practical example, we will see how the combination of formulas and parameters opens up new horizons for the creation and personalization of reports and allows us great flexibility in design.

Chapter 9 – Adding Charts
This part of the book is dedicated to charts. We will talk about chart functions, good and bad practices of using charts, and how to create and configure our own chartsamong other topics. Report designer uses JFreeChart to create charts. By explaining each type of chart (Bar chart, Line chart, Pie chart, Area chart, Barline chart, Ring chart, Bubble chart, Scatter-plot chart, XY chart, XY line chart, XY area chart, Waterfall chart, Radar chart, XY area line chart) , we will be able to understand which chart to use for a particular need, and how to configure the chart so that its look and feel is what we want. We will also see how charts allow us to show data from different perspectives and add value to our reports.

Radar chart

Chapter 10 – Adding Subreports
This chapter is dedicated to Subreports. We will see what Subreports are and how they work and learn about their specific features. We will also talk about the relation that exists between the main Report and its Subreports. We will be able to add information to our Reports in the form of tables, charts, and so on, whether this information is connected to our data set or not. This gives us an idea of the potential that Subreports provide.

Chapter 11 – Publishing and Running Reports in Pentaho BA Server
In this chapter we talk about Pentaho, the fastest growing, most popular, and most heavily invested in Open Source Business Intelligence (OSBI) suite in recent years. We analyze its principal characteristics and the principal projects included in the suite. With Pentaho Business Analytics Business Analytics Server, we have full BI analytic power at our fingertips through a GNU General Public License (GPL). We will see how Pentaho Report Designer (PRD) and Pentaho BA Server interact, and how we can use them to run our reports from Pentaho User Console (PUC). At the end of this chapter, we will have Pentaho BA Server running and will be able to access our reports from a web browser. Pentaho can run on any compatible application servers with JEE architectures such as JBOSS, Tomcat, WebSphere, WebLogic, Oracle AS…

Chapter 12 – Making a Difference – Reports with Hyperlinks and Sparklines
In this chapter we will see how to create and configure Hyperlinks. Hyperlinks give us great flexibility in designing our reports, as they let us drill down. Using Hyperlinks, we can create a network of reports and also re-use reports. Here we will also see a very interesting object that we can add to our reports, which will present specific information graphically: the sparkline.


Chapter 13 –  Environment Variables, Stylesheets, and Crosstabs
This chapter is dedicated to environment variables, stylesheets, and crosstabs. We will treat each of them in detail, defining what they are about and how to implement them in Pentaho Report Designer (PRD). Using Environment Variables allows us to interact with the BA Server that is executing our reports. That is, we can obtain the username of the user who is currently logged in and his or her roles, among other information. With Style Sheets, we can configure our report so that its look and feel varies according to certain CSS Rules. These CSS Rules are easy to configure, save, and import. Finally, we will take a look at one of PRD’s new jewels, Crosstabs. Using Crosstabs, we can show OLAP-style analytic information in our reports.

Chapter 14 – PRD Reports Embedded in Web Applications
This chapter attempts at taking not only one step forward with PRD, but many. Once this chapter is finished and the concepts presented here are understood, we will be able to create a web portal that allows users to access PRD reports, send parameters to them, and select the final presentation format. We will also be able to perform every configuration needed to have the portal working as well as have it packaged so that we can transport it and deploy it in a different environment with little modification.

What’s your favorite chart? (New features in Pentaho)

0-Title12DaysVizChoose your favourite Chart/Diagram from new Pentaho features and comment me a background where this chart unveils valuable information.  Web: http://wiki.pentaho.com/display/COM/Holiday+Visualizations


The sunburst chart enables you to visualize hierarchical data by displaying each level in the hierarchy as a series of rings.

1-SunBurst1-SunBurst2ZOOM CHART

The zoom chart allows you to view trends across a large data set where you can select the key areas that you want to “zoom into”

2-Zoom Chart2-Zoom Chart2TRELLIS CHART

Discover structure and patterns in complex data.

3-Trellis Chart3-Trellis Chart2TREEMAP

Visual representation of a data tree, with data organized in hierarchical clustered rectangles, the size of each representing the quantity

4-Treemap4-Treemap2CHORD DIAGRAM

Useful for exploring relationships between attributes.5-Chord Diagram5-Chord Diagram2PARALLEL COORDINATES

A popular method of visualizing high-dimensional data, this visualization provides the ability to analyze multiple attributes across many measures, then interactively filter data across all measures simultaneously.

6-Parallel Coordinates6-Parallel Coordinates2INDEX CHART

Visual representation of text data with the importance of each tag signified by font size or color to quickly show relative prominence.

7-Index Chart7-Index Chart28-Calendar Chart8-Calendar Chart2TAG CLOUD

Visual representation of text data with the importance of each tag signified by font size or color to quickly show relative prominence.

9-Tag Cloud9-Tag Cloud2FUNNEL CHART

Funnel charts are a type of chart, often used to represent stages in a sales process and show the amount of potential revenue for each stage. This type of chart can also be useful in identifying potential problem areas in an organization’s sales processes

10-Funnel Chart

10-Funnel Chart2PACKED CIRCLE

Packed Circle chart, uses circular containment to represent hierarchy.

11-Packed Circle11-Packed Circle2CROSS FILTER

Select areas of interest on the chart to slice and dice many attributes simultaneously; useful for large, multivariate datasets

12-Cross Filter12-Cross Filter2

Have you enjoyed? Please share your comments and try the new charts.


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.




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.

via Getting session variables when using the PRD scriptable data source.

Executing JNDI based Pentaho reports using the Java API

Executing JNDI based Pentaho reports using the Java API.


Working on the Alfresco Business Reporting project. In there, the tool can execute PentahoJaspersoft reports (against Alfresco business objects, using the Pentaho/Jasersoft Java API) on a scheduled basis. I ran into the issue of portability of the (Pentaho) reports. The database, url, credentials were not identical on my Development, Test and Production environments… JNDI is the answer.

In JNDI ( (Java Naming and Directory Interface) world, a datasource is identified by a name, and each naming provider can carry different credentials/url’s for the same name in different environments. Datasources can be LDAP systems, JDBC connections etc. Just what I needed.
I ran into 3 issues, initially entangled;
  1. How does the Pentaho reporting API deal with JNDI
  2. Running a report using my custom Java logic from a command-line
  3. Running a report from within Alfresco – How to manage the duplicate credentials. The reporting database connection properties were already defined in alfresco-global.properties, and I prefer to re-use these in the named JNDI connection.
 Although I like journeys, I do prefer reaching destination too, especially in this particular case. There were not many useful guides along the route, so having it finally figured out, also resulting in this blog.
How does the Pentaho reporting API deal with JNDI

The Pentaho report definition (.prpt files) are zip files containing mostly XML files. One of the files contains all database related info. This means the JNDI name and the JDBC userame/password/driver/url information is stored inside the report. It appears hard to override when executing the report using the Java API.
The Pentaho API has a class MasterReport, that has the methods setDataFactory and setDataSource. The DataFactory is typically used for JDBC like connections (user, pass, url, driver).  I have had no success in overriding these values when executing a report in a different environment.
The JNDI name is stored in the report too, but since it is resolved against the context at time of execution, it is perfectly fine to use the name as stored. Exactly how JNDI was meant. Find here how to configure the Pentaho Report Designer for JNDI.
Running a report using my custom Java logic from a command-line
I created a command-line version of the logic to execute reports. Building stuff into Alfresco, restarting and the full procedure takes too much time. But how to use JNDI like this?
Most guides expect some kind of JNDI provider (usually application servers like JBoss, Tomcat), and tell you how to get definitions from there. I do not want to depend om yet another definition of my reporting database details, it is in Alfresco’s global properties already. I got stuck in Simple-JNDI and related tools, that apparently get datasources and details from, yet again, an existing JNDI provider. The Report executer does not accept anything that Simple-JNDI provides (DataSource or Connection). (Wonder how Pentaho Report Designer does the trick itself… I have had my share of reverse engineering for now…) The Pentaho report just requests the JNDI name, and gets provided by the environment. How to get such a responsive environment?
I finally ran into this guide from Oracle. It inspired me to finalize their idea (stopRegistry was missing):
01 private static Registry startRegistry() throws RemoteException {
02     System. out .println("enter startRegistry" );
03     Registry registry = null ;
04     try {
05         registry = LocateRegistry.createRegistry(Registry. REGISTRY_PORT );
06         System. out .println("exit startRegistry: RMI registry created.");
07     } catch (ExportException e){
08         System. out .println("exit startRegistry: RMI registry already existed.");
09     }
10     return registry;
11 }
13 private static boolean stopRegistry(Registry registry) {
14     System. out .println("enter stopRegistry" );
15     boolean result = false;
16     try {
17         result = UnicastRemoteObject.unexportObject(registry, true );
18     } catch (NoSuchObjectException e) {
19         System. out .println("stopRegistry: RMI registry already stopped.");
20     }
21     System. out .println("exit stopRegistry: RMI registry stopped: " + result);
22     return result;
23 }
25 private static InitialContext createContext() throws NamingException {
26     Properties env = new Properties();
27     env.put(Context. INITIAL_CONTEXT_FACTORY ,"com.sun.jndi.rmi.registry.RegistryContextFactory" );
28     env.put(Context. PROVIDER_URL , "rmi://localhost:" +Registry. REGISTRY_PORT);
29     InitialContext context = new InitialContext(env);
30     return context;
31 }
33 private static void setJndiDataSource(String source) throws NamingException, RemoteException{
34     System. out .println("enter setJndiDataSource source="+source);
36     ConnectionPoolDataSource dataSource = newMysqlConnectionPoolDataSource();
37     ((MysqlDataSource) dataSource).setUser( "username" );
38     ((MysqlDataSource) dataSource).setPassword("password" );
39     ((MysqlDataSource) dataSource).setServerName("localhost" );
40     ((MysqlDataSource) dataSource).setPort(3306);
41     ((MysqlDataSource) dataSource).setDatabaseName("alfrescoreporting" );
42     DataSource ds = (javax.sql.DataSource)dataSource;
43     InitialContext context = createContext();
44     context.rebind( "java:/comp/env/jdbc/" +source, ds);
45     System. out .println("exit setJndiDataSource" );
46 }
Remind that the unexportObject() method only works with the exact object as returned by createRegistry() call … I invoked startRegistry() and setJndiDataSource() before and stopRegistry() after Pentaho’s  reportProcessor.processReport() and got exactly what I needed!  (I suggest you call the stopRegistry() in a finally part of the try/catch block around the processReport()).
Now I was able to build my own JNDI provider that actually allowed me to execute a Pentaho JNDI-based report from the command-line (e.g. without any application server).
Running a report from within Alfresco
It would be too easy if my command-line code would work inside an application server too. Th application Server (Tomcat in my case) owns the Registry. You can access the registry read-only, but not rebind and therefore ‘dynamically’ adding the appropriate details for any given JNDI name. This actually makes sense of course.
Basically, define a JNDI source within Tomcat (or application server of choice). I followed Tomcat’s example, and added
1 <Resource defaultTransactionIsolation="-1" defaultAutoCommit="false"
2     maxActive="15" maxIdle="3" initialSize="1"
3     username="username" password="password"
5     driverClassName="org.gjt.mm.mysql.Driver"
6     type="javax.sql.DataSource" auth="Container"
7     name="jdbc/alfrescoReporting"/>
to my META-INF/context.xml. My web.xml was enahnced with:
1 <resource-ref>
2     <description>Alfresco Reporting JNDI definitition</description>
3     <res-ref-name>jdbc/alfrescoReporting</res-ref-name>
4     <res-type>javax.sql.DataSource</res-type>
5     <res-auth>Container</res-auth>
6 </resource-ref>
You also need to make sure your database driver (mysql-connector-java-5.x.yy.jar, or any Postgres, Oracle, MSSql equivalent) is located in tomcat/lib. (Usually it is already there.)
According the guys from Tomcat, you’re done. However, it doesn’t work. Apparently you also need to tweak <applicationname>.xml (in my case: alfresco.xml) in tomcat/conf/Catalina/localhost. You need to insert exactly the same line(s) as added to context.xml above.
And now we’re done. Pentaho reports are executed using the Java API in a web application like Alfresco using JNDI!

How to use a Textbox Parameter for Multi-Value Selections in Pentaho Report Designer

Pentaho Report Designer (a desktop application that provides a visual design environment to create report definitions) 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.

Publishing Pentaho Reporting Templates Tips!

Yesterday I have issues updating Repository cache after uploading new .PRPT ‘s to my solution repository on a BI Server version 3.6 .

After a while I noticed that reports which failed had éá and latin characters so I turned it into ASCII ea and all worked correctly.

Second problem was to open on my Pentaho User Console, reports with + char on its title. The solution was to avoid the using of this type of strange characters.

Hope you help