Complete Pentaho Installation on Ubuntu, Part 10

Posted on by

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

  1. Download a stable version from the Pentaho project in sourceforge.
    Or you can download the 3.8 release candidate [67MB].
  2. Extract its content in the /Pentaho folder so you end up with a:
    /Pentaho/report-designer/
  3. Make shure the *.sh files are excecutable.
  4. 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.properties
    to use the JNDI connection options.
  5. 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

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.

  1. Click the New Button.
  2. In right panel, select the Data tab
  3. Right click on Data Set
    choose one of the options: JDBC (/JNDI), metadata, ETL (PDI), OLAP, XML or Table.

    Report DataSource

  4. If you choose Metadata:
    1. Browse to:
      Pentaho/biserver-ce/pentaho-solutions/steel-wheels/metadata.xmi
    2. In the dialog, type:
      SteelWheels for Domain
      Click on the Query plus sign and set a name for the query name
    3. And build a query by clicking on the pencil button.
      Like this:

      Report-Designer Medatdata

      Report-Designer Medatdata

    4. If you click ok, you’ll see all your queries and parameter datasources. Use preview to check your connection and data.

      report-designer Query

      report-designer Query

  5. If you choose JDBC:
    1. Select SampleData (our MySql datasource)
    2. Click on the plus button and define a query by selecting tables and fields, the joining fields are asumed when name matches.
    3. The designer is easy to learn or you can build your query on the MySql Browser and type it in the query field.
  6. After you close the datasource dialog you can drag columns to your report area and change the display properties on the Structure Tab.

Now

  1. Check the samples.
  2. Check this great post that uses sparklines: Report Parameter & Sparkline.
    and while you’re at it, read about Edward Tufte’s data visualization  history and articles.

Good luck!

[Edit] Resources

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.wiki
    Defines each type, shows the difference between ‘date’ and ‘date (sql)’.
    An llustration of each type and how appears in the PUC at prashantaju.com
  • Showing multivalue list and SQL query on prashantaju.com
  • How to ask for a SQL query parameter ‘* = all’ option: diethardsteiner.com
  • Calculate previous date with functions: bizcubed.com
  • Using ‘date picker’ and format date bizcubed.com
  • Cascade parameters (results of one parameter depends on the previus one): prashantraju.com
  • Overview of PRD 3.6 on five interesting videos.
  • Wiki aricles.
    Including this introductory guide.
  • PRD documentation.
  • Subreports:
    passing parameters and working with subreports on pentaho.wiki.
    Subreports, multiple reports, passing parameters on bizcubed.com.
  • Limit output type (PDF, HTML) parameters on sherito.org
    Set the default with attribute on setting options. Check the tip on the next section.
  • Tutorial and tips on PRD classic (that’s 3.5 and lower) at bizcubed.com, useful for maintenance. The tutorial for current version is also on bizcubed.com
  • Explanation on parameters and GWT fundamentals at sherito.org
  • Article on building interactive reports.
  • Note on How to call stored procedure/kettle files in Pentaho designer.
  • Pentaho Reporting 3.5 for Java Developers, Book Sample: booksonline.
  • Change row background color at guru4us.
  • Control page break in style sub section also at guru4us.
  • Using chart objet to display a value aginst results on prashantahu.
  • Link a report in html. In the example a top ten report lets you click on a single item.
  • Crosstabs.
  • An article about BIRT vs Jasper vs PRD.

Eight Tips

  1. 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.
  2. 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
    • Add another parameter that will be the one in our query:
      – name: DATESTR
      – type: String
      – formula: =MESSAGE(“{0,date,yyyyMMdd}”;[DATEGIVEN])
      – Hidden
      – display type : None

      Parameter Date

      Parameter Date

    • You can use ${DATESTR} in a query or
      DATESTR in PDI ‘getsystem info’ step as argument #.
  3. 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.
  4. 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.
  5. 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.
  6. 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

    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:

    <id>RUN</id>
<command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;locale={locale}</command>
</operation>

    Add “&amp;autoSubmit=false” like this:

    <id>RUN</id>
<command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;locale={locale}&amp;autoSubmit=false&amp;layout=flow</command>
</operation>

    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.

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

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

Leave a comment