Post navigation

Complete Pentaho Installation on Ubuntu, Part 10

Install Pentaho Report Designer (PRD)

If you have installed de BI Server, PDI (ETL), PDS (sequence of actions), modeling (Query metadata and OLAP workbench), you have all you need to provide access to your data and automation of extractions, even mail distribution, using tables or excel formated files.

Now with the report application you can create specially formated output presentations with areas, colors, tables, graphs. Using subreports you can mash them up even from different datasources: jdbc, metadata, ETL.

I prefer to publish the reports to the BI server but you can use the PRD (Pentaho Report Designer) as a stand-alone application, just like the PDI.

Installation

Download a stable version from the Pentaho project in sourceforge.
Or you can download the 3.8 release candidate [67MB].

Extract its content in the /Pentaho folder so you end up with a:
/Pentaho/report-designer/

Make shure the *.sh files are excecutable.

Edit the
Pentaho/report-designer/configuration-template/simple-jndi/default.properties
change the database strings to MySql like in
Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.propertiesto use the JNDI connection options.

Start the app with:
$ ./report-designer.sh

In the startup dialog you can check the samples, the wizard, or start from zero. Now you have a standard reporting tool, where you define a datasource and then drag and drop fields into header, grouping or detail bands, then publish your reports.

Report-Designer Sample

Tutorial

Michael Tarallo from Pentaho has made an excelent tutorial about using the Pentaho Report Designer in a series of short videos. I think this is the place to start.

And a step by step guide in the Pentaho Wiki for creating your first report.

Example from the Beginning

Before I found the tutorial, these were my guidance notes.

Click the New Button.

In right panel, select the Data tab

Right click on Data Set
choose one of the options: JDBC (/JNDI), metadata, ETL (PDI), OLAP, XML or Table.

The PRD can substitute the .xaction files for initial parameter information. Then it can get data from OLAP, databases or ETL jobs and mash them into a page or file, which make it a very interesting tool, but each function has its details, so here are some of the articles about them:

Creating Parameters with Pentaho Report Designer: prashantaju.com
Shows how to build a query, ask for a parameter, then modify the query to use it.

Several parameter tips on diethardsteiner.com: using it with queries, metadata, olap, single and multiple values, formulas and more.

PRD parameter type definition in pentaho.wikiDefines each type, shows the difference between ‘date’ and ‘date (sql)’.
An llustration of each type and how appears in the PUC at prashantaju.com

Remember to use unique names for your parameters.
If you use the same name as one of your data columns even on subreports, it will confuse the PRD and it will be sustituted by null.

To ask for a date:

Add a parameter:
Right click on right panel, option data, at the bottom, like
– name: DATEGIVEN
– type: Date (SQL)
– format: MM/dd/yyyy
– timezone: use server timezone
this is important or it will add or substract additional hours
– Some default on date like 31 of january: 01/31/2011
– Mandatory
– Display type: Date Picker

You can use ${DATESTR} in a query or
DATESTR in PDI ‘getsystem info’ step as argument #.

To change the order in which the parameters appers in the bottom-right panel, right click on them and select bring them forward=up or back=down on the menu.

To add a subreport you use the icon in the left toolbar at the bottom.
Drag the icon from the left toolbar and you will be prompted to use it as an area or as a band (this one uses the horizontal area). Each one can use its own datasource, graphs, etc.

If you use subreports, you need to define first the datasources on the master reports.
When you open the subreport you need to define the parameters in the data panel in a confusing importing-exporting two list dialog. Use te same names and order than in the first list.

Using the attributes panel, you can remove the autos-submit button parameter, or set the default output type, as seen on the picture.

PRD Attributes

To remove the auto-submit button permanently for all reports:
Check the section “How to turn off auto-submit button in Pentaho?” in this wiki that says you need to edit the file:
\Pentaho\biserver-ce\pentaho-solutions\system\reporting\plugin.xml
And change:

You can also add “&amp;layout=flow” to make all parameters appears ‘inline’, not each one in its own row. Or change the default option in the output type, but you need to restart the server to see the changes.

You can use the same report and give different levels of summarization. You only need a parameter that gives the options and define in the report the hide functions in the detail or header/footer bands. [Bizcubed original article]

Define parameter SHOW-TOTALS.
String value on a drop down table with Yes and no options.

In the report structure, in Details level (under Details Body), find in the top Format menu ‘conditional Hide’ and type:
=IF([SHOW-TOTALS]=”NO”;”True”;”False”)

If there are more levels than detail and summary, you’ll need to use a more complex OR function:
=IF(OR([SHOW-TOTALS]=”T”;[SHOW-TOTALS]=”D”);”False”;”True”)

You can check the style tab on the bottom right panel, in the ‘size & position group’, you’ll see the function in the visible attribute, thirdh column=function

Another function created by the Format Menu us the row-banding or alternate color banding. Where you can choose from some colors in a dialog.
But as is explained on this forum thread, after using the menu you can go to the data tab on the top right panel and search for the function ‘row banding’, click on it and in the bottom panel choose the color you really want.