Bubble Report: BI Server + SQL Query + Report Designer

To Do

The Pentaho BI demo has an example of what will be built. In the ‘Browse’ panel click on bi-developer and in ‘Files’ panel  open the Bubble chart report.

Bubble Report

Bubble Report in the BI demo

Report Specification

[If you’re just interested in learning to build the report, you can skip this section]

The report is defined by an .xaction (you can right-click on the file and check properties):

[Pentaho/biserver-ce/pentaho-solutions/]
bi-developer/reporting/report-charts/bubble-charting.xaction

To find out our data source, data, graph size, etc. we’ll browse the .xaction file. Then we’ll use the Pentaho Report Designer (PRD) to build a more interactive version that will prompt for parameters. And is easier to build.

We  have at the top, the documentation ‘section’:

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <name>bubble-charting.xaction</name>
  <title>%title</title>
  <version>1</version>
  <documentation>
    <author/>  
    <description>%description</description>  
    <icon>PentahoReporting.png</icon>  
    <help>This action-sequence was generated by the Report Design Wizard. To edit this action-sequence use the Pentaho Design Studio.</help>  
    <result-type>report</result-type>
  </documentation>

Define an input string variable output-type with value ‘html’ and output report of type content

  <inputs>
    <output-type type="string">
      <default-value>html</default-value>  
      <sources>
        <request>type</request>
      </sources>
    </output-type>
  </inputs>

  <outputs>
    <report type="content">
      <destinations>
        <response>content</response>
      </destinations>
    </report>
  </outputs>

In the resources section there is a reference to a file of type ‘solution’:  bubble-charting.xml that will define the chart.

  <resources>
    <!-- use this section to identify any files that the component needs to execute the report -->  
    <report-definition>
      <solution-file>
        <location>bubble-charting.xml</location>  
        <mime-type>text/xml</mime-type>
      </solution-file>
    </report-definition>
  </resources>

Then a sequence of actions: the SQL-lookup gets the data from a SQL query using a jndi connection to SampleData that puts the result in the object result-set with four columns: department, actual, budget and variance (its really just the difference of actual-budget), all are sums of columns from the table cuadrant-actuals that do not have departments that  start with ‘Prof%’.

  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>rule</action-type>
      <action-inputs/>
      <action-outputs>
        <rule-result type="result-set"/>
      </action-outputs>
      <component-definition>
        <jndi>SampleData</jndi>  
        <query><![CDATA[select DEPARTMENT, sum(actual) as ACTUAL, sum(budget) as BUDGET, sum(variance) as VARIANCE from QUADRANT_ACTUALS WHERE NOT department like 'Prof%'  GROUP BY department]]></query>
      </component-definition>
    </action-definition>

Then the output is used in the chart component. The output-type was defined as ‘html’  and the data is in the object ‘result-set’. The chart  was also specified in the .xml file.

    <action-definition>
      <component-name>JFreeReportComponent</component-name>
      <action-type>report</action-type>
      <action-inputs>
        <output-type type="string"/>  
        <data type="result-set" mapping="rule-result"/>
      </action-inputs>
      <action-resources>
        <report-definition type="resource"/>
      </action-resources>
      <action-outputs>
        <report type="content"/>
      </action-outputs>
      <component-definition/>
    </action-definition>

  </actions>
</action-sequence>

If you read the .xml graph file you’ll see specification for several elements: headers, footers, functions, series definitions, fonts, axes ranges, groupings, chart sizes, colors and data grid. If you want to use the fancy graphic capabilities of the library JFreeChart library check: Chart usage and  Bubble chart specs.

Using the Pentaho Report Designer

Start the PRD and a new report:

  • Open a terminal or command window.
    In Ubuntu click start on the left bar, type TER. Click on the icon ‘Terminal’
  • Change to PRD folder.
    cd /Pentaho/report-designer
  • Start the aplication
    ./report-designer.sh
  • Click on File -> New

Lets take an incremental way to build the report that let us test and see how the report grows and how it becomes beautiful.

Report

Just an SQL is needed for the report so well select a JDBC connection but you here can choose from MDX o PDI (ETL transformatoion) or several other sources.

  1. In the upper right, select the data tab.
  2. Right-click on Data Sets and click on JDBC
    Data Tab

We’ll just need the recods for our report but you can define also parameter queries and subreport sources.

  1. Choose the SampeData connection on the left panel (see the image below)
  2. Click on the plus sign on the upper right of ‘Available queries’
  3. On Query Name type someting like ‘sqlBudgets’ (use prefixes and mnemonics)
  4. Copy and paste the original query:
    select DEPARTMENT, sum(actual) as ACTUAL, sum(budget) as BUDGET, sum(variance) as VARIANCE
     from QUADRANT_ACTUALS
     WHERE NOT department like 'Prof%'
     GROUP BY department
  5. Click on the Preview button. You should see a 6×4 grid with the data.
    SQL Data
  6. When you close the dialogs (Close and OK) the columns will appear in the right panel.
  7. Drag and Drop the DEPARTMENT, ACTUAL, BUDGET VARIANCE columns into the Details Band in the middle of the page. Don’t Worry about Aligment now.
    Drag Columns

It’s a good time to save the report, it will let you go back to a known point in case you don’t like a change or save progress. The PRD is stable but you can never be too cautious.

  1. In the top menu: File -> Report Properties.
  2. In the dialog click on Description.
  3. Fill the data required: (Author, description)
    The Title is important as is the string that will be used in the BI server.
  4. Now save it into the bi-server/Pentaho Solutions/[Your folder].
    In the top menu File->Save As.

Lets see the data in the actual output

  1. Click the Run button (green arrow) in the toolbar: Run Button
    Select output: html
  2. After checking the data, close the browser window and return to the PRD.

Set Style for the output columns

  1. Select all columns: click on each one and press at the same time the shift key.
  2. Check that the Structure tab is selected in the upper panel.
  3. In the bottom panel in the Style tab, scroll down to size & position section, set:
    y=0,  Height=12, font-size=8, v-align=MIDDLE, width=25% (you can use pixels or percentages)
    Style

Set Format for the currency columns

  1. Deselect all items (click on other area) so you can click again only on ACTUAL, BUDGET VARIANCE.
  2. In the bottom panel click on Attributes tab.
  3. In the common section make the format equal to $#,###.00.
  4. Now align the columns so there is no overlapping selecting.
    Click on each one and move them with the arrow keys on your keyboard.

Add text labels for titles, column headers, etc.

  1. Use the left toolbar icon for labels Text Area
    Drag it into the top left area in the Header.
  2. In the Attributes tab (or in the same text box) in value (as it appears on the original report), type:
    Variance By Department
  3. Set: font-size=10, x=0, y=0, height=12.
  4. Put another label to the right with this text:
    $(report.date, date, MMMM dd, yyyy @ hh:mm )
  5. Set: font-size=8, x=50%, y=0, width=50%, height=12, align=RIGHT
  6. Add 4 more labels with text in the report header: DEPARTMENT, ACTUAL, BUDGET VARIANCE.
  7. Set font-size=10, width=25%, height=18, bg-color=#9eaa36, text-color=white, v-align=MIDDLE. Then align=LEFT for Department, align=RIGHT for the others.
  8. Alignment:
    Tip 1: To align object vertically, select them, right click on one and choose Alignment->top. Then use the arrows to put them when you want them.
    Tip 2: To align them horizontally, select a column on the detail then shift-click on the corresponding label on the header. Click on x and press enter, then on width, the values will be the same.
  9. Drag a horizontal line from the left toolbar into the header.
    Set text-color=#9eaa36, height=3, width=100%, x=0, y=24
  10. For the grid effect, select the four data columns (Detail band) and set their top, bottom, right and left borders to size=0.5, style=solid, color=#bbb

Preview the report

  1. Use the Preview button:
    (click on the pencil or edit button in the same poition to return to this view)
    Data Report

Bands

Now that you are familiar with objects and its attributes. An additional concept you need to know is the band-structure of the report.

Select the Structure tab in the upper panel.

Structure

Example 1: The ‘No Data’ Band.

  1. In the tree structure click on No Data.
  2. Click Attributes Tab in the bottom right panel
  3. Set hide-on-canvas=false.
    Visible Band
    now you can set message or elements on the report band in case there is no data.

Example 2: Alternating colors on Details.

  1. Click on the Details Band in the tree structure.
  2. Click in the top menu: Data -> Row Banding
  3. Select ‘White’ for visible color
  4. Any other for invisible.
  5. Click OK to close the dialog.

Lets check the function that was added and change color

  • In the upper panel click on Data tab.
  • Select Row Banding on Functions.
  • In the bottom panel setinactive color=#e7e7e7

Preview your report and save.

The Chart

The JFreeChart library is used in the PRD, but we can use the properties to set options, thats is settings. We just need to add the chart object.

  1. Drag from the left toolbar the Chart Icon Chart Icon into the Header band.
  2. Give it the size you like
  3. Move the text with the arrow keys after selecting them on the structure right panel and clicking on one of them (That is more easy to me).
  4. Right click on the chart, select Chart.
    The following dialog will appear.
    Chart Definition
  5. In the top toolbar select bubble chart.
  6. In the left panel you can set fonts, lablels, backgrounds (bg-color=#9eaa36), series colors, and legend options.
    Set: max-bubble-size=125, plot-fg-alpha=0.5 (transparency),
    legend-font=SansSerif-PLAIN-6
  7. On the right panel you set the datasource.
    First click on x-value-columns, then on the three dots. In the drop down select click on the data column ACTUAL.
    Data
    Now set y=BUDGET, z=VARIANCE, series-bby-field=DEPARTMENT.
  8. Save it an click preview or run.
    Notice how the axes are dynamic as you didn’t set a maximum value.

You have compleated your report. If you reload the solutions on the BI server you will be able to run it from the menu.

Add a Parameter

To add a parameter for the user to filter data we need do three things:

1. Add a query that list the available departments

  • In the right-top panel, check that Data tab is selected.
    Double click on JDBC: SampleData to open de Data Source Dialog with the ‘SampleData’ selected.
  • Click on the plus sign in the Available Queries panel and name it sqlDept.
    Copy and paste this SQL:

    SELECT DISTINCT DEPARTMENT
    FROM QUADRANT_ACTUALS
    ORDER BY DEPARTMENT;
  • Close the dialog.

2. Add the parameter for the user selection

  • In the right-top panel, check that Data tab is selected.
    Right-click on Parameters, select Add Parameters
  • Set: name=pDept, label=Department to exclude:, type=string, Mandatory.
  • For using the query: Display=Drop Down, Query=sqlDept
    Parameter
  • Close the dialog.

3. Modify the query to use the parameter

  • Double click on ‘JDBC: SampleData’ in sqlData.
  • Change: WHERE NOT department like ‘Prof%’
    to: WHERE NOT department like ${pDept}
  • Close the dialog

Finishing Touches

Lets add the department in the report so you can see what was the query if printed. Just drag de pDept parameter to the Report Header. Add a Label ‘Parameter Excluded:’

If you test the report in the BI server you’ll see that a parameter for output was added as well as an auto-submit checkbox. Lets asume we just want one html page:

  1. Click in the upper panel: Structure Tab.
    Click on Master Report.
    Click on Attributes tab in the bottom panel.
  2. To avoid the option select drop down:
    Scroll to common section and set:
    output-format=table/html;page-mode=stream
    lock-output-format=true
  3. To avoid the auto-submit:
    Scroll to Parameter and set:
    auto-submit=false
    autosubmit-default=true
    parameter-layout=flow

This is how it looks:


Bubble Chart Report in the BI Server

Bubble Chart Report in the BI Server

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

  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.