Creating Dynamic Sizing Charts with Pentaho Report Designer.

Posted under Open Source, Pentaho, Report Designer, Tutorials, Visualisation

Pentaho Report Designer’s (PRD) charting capability is great – it has numerous charts to choose from and the formatting is extremely flexible. However I did have a problem with charts represent large data sets. For example I had a list of countries and a bar chart showing the population for each country, users were able to view one or more countries at a time using a multi value list parameter. The problem occurred when there were too many countries, the chart’s labels became unreadable. One method was to create a chart which had a dynamic width depending on the amount countries a user selected.

Scenario.

I wanted to create a bar chart which has a dynamic width (expand or contract) depending on the amount of categories a user selected from a multi value list parameter. This would ensure that the chart didn’t become squashed and unreadable. in this tutorial I will be using a data set which represents population by countries.

Data Set.

Below is an example of the data set I will be using for this tutorial, as you can see each country has a population (you can download a spreadsheet version of the sample data at the end of this tutorial).

ID COUNTRY POPULATION*
1 China 1337490000

* Population is calculated in the millions

Step 1 – Data Source.

Start PRD and create a new report. Right click on the Data Sets icon and select the JDBC option from the menu.

Data Source, JDBC option

After selecting the JDBC option a JDBC Data Source window will appear. I have already created a connection to my database which includes the data that I will be working on, in this example it is called Geography.

Geography Data Source

Once you have a working connection to your database you are now ready to create your queries.

Step 2 – Queries.

This report contains two separate queries:

  1. qCountries : Retrieves a distinct list of countries, this will be used for the countries parameter
  2. qDataset : Retrieves the population for the countries selected by a user

pCountries.

The qCountries query needs to return a distinct list of countries, the SQL query looks like this:

SELECT   DISTINCT COUNTRY
FROM     dataset

A screen shot of the JDBC Data Source window of the qCountries query looks like this:

qCountries

qDataset.

The qDataset query needs to return the population for countries which the user has selected, the SQL query looks like this (this query will need to use a parameter which I will add in later):

-- Use a sub query to calculate the total amount of countries selected
SELECT    t1.COUNTRY AS COUNTRY,
               SUM(t1.POPULATION) AS POPULATION,
               SUM(t2.COUNTRY_COUNT) AS COUNTRY_COUNT
FROM      dataset t1,
          (
              SELECT COUNT(*) AS COUNTRY_COUNT
              FROM dataset
              GROUP BY COUNTRY
          ) t2
GROUP BY COUNTRY

A screen shot of the JDBC Data Source window with the qDataset query looks like this:

qDataset

Step 3 – Parameter.

There is only one (multi value list) parameter in this report which will display a distinct list of countries and be populated by the qCountries query. To create a new parameter right click on the Parameters option under the Data tab and click the Add Parameter… option:

Add Parameter...

This will bring up the Add Parameter window. After filling out all the options the Add Parameter window now looks like this:

sCountries

Where the options and values are:

  • Name : This is the name of the parameter, this is used within report queries i.e. the WHERE clause of qDataset
  • Label : The text which sits next to the parameter on the report i.e. Select one or more countries
  • Value Type : The data type of the sCountries value i.e. COUNTRY is a String
  • Mandatory : For the report to run a parameter value must be selected i.e. Checked (true)
  • Display Type : The parameter type i.e. Multi Value List
  • Value : The parameters value which will used within queries etc. i.e. the field COUNTRY
  • Display Name : What the user will see the parameter value as i.e. the field COUNTRY
  • Visible items : The amount of items which are shown to the user in the multi value list parameter i.e. 3

Click OK to save the parameter, it should now be visible under the Parameters item under the Data tab.

sCountries Parameter

After adding the sCountries parameter the next step is to use the parameter as part of the qDataset WHERE clause. Right click on the qDataset item under the Data tab and click the Edit Query option:

Edit Query

Modify the qDataset query so that it now includes the sCountries parameter:

-- Use a sub query to calculate the total amount of countries selected
SELECT    t1.COUNTRY AS COUNTRY,
               SUM(t1.POPULATION) AS POPULATION,
               SUM(t2.COUNTRY_COUNT) AS COUNTRY_COUNT
FROM      dataset t1,
          (
              SELECT COUNT(*) AS COUNTRY_COUNT
              FROM dataset
              WHERE COUNTRY IN (${sCountries})
              GROUP BY COUNTRY
          ) t2
WHERE COUNTRY IN (${sCountries})
GROUP BY COUNTRY

qDataset

Step 4 – Bar Chart.




The next step is to create a bar chart whose width is dynamic based on the amount of countries a user selects. To get started drag and drop the chart object onto the report header.

Bar Chart on Report Header




As you can see the width is fixed at the moment but before I start to make any changes to the chart’s width I will need to configure my chart to display our data and see how it becomes unreadable when we increase the amount of categories on the x axis. Double click on the chart and to bring up the Edit Chart window.

Edit Chart

By default the chart is set to a bar chart and without going through every available option I made the following changes to represent my data set:

  • chart-title : Chart title i.e. Population of Countries (optional)
  • gridlines : Are the vertical and horizontal grid markers i.e. Set to False (optional)
  • x-axis-title : Label of the x-axis i.e Countries
  • x-axis-label-rotation : Rotation of the x-axis label i.e. 90
  • y-axis-title : Label of the y-axis i.e. Population (millions)
  • category-column : Field which will be on the x-axis i.e. COUNTRY
  • value-columns : Field which populates the y-axis i.e. POPULATION
  • series-by-field : Field which defines the series i.e. COUNTRY

After previewing the chart with all the countries selected you can see that the output is squashed and unreadable.

Preview Non Dynamic Chart

One way around this is to make the width (or height) of your chart dynamic by the amount of countries the user selects from the multi value list parameter. Select the chart and on the right hand side under the Style tab locate the size & position option and click the Add a new query icon icon under the Formula column. This will display an Expression window, click on the icon which will then open the Formula Editor window.

Formula Editor

The formula below retrieves the COUNTRY_COUNT field and multiplies it by the width of the chart which suits one category (country) on the x axis, which in this example is 150.

# If there is only one country selected then use default width otherwise calculate width
=IF([COUNTRY_COUNT]=1;150;[COUNTRY_COUNT]*150)

Width Formula

Save the formula. Now you are ready to preview the report.

Step 5 – Preview.

Click on the preview icon Preview icon and select one country from the multi value list parameter, as you can see the width is set to our default width, 150.

1 Parameter

Selecting two countries from the multi value list parameter will expand the width to 300.

2 Parameters

Selecting four countries from the multi list parameter will expand the width to 600.

4 Parameters

So that is how you create a chart which can expand or contract depending on the amount of categories displayed on the x axis.

Downloads.

Filename Type Notes
DynamicChartsData ODS Released 17/05/2010
DynamicCharts PRPT Released 17/05/2010

Final Notes.

A couple of things to note about the method I used:

  • The query to generate the amount of selected countries uses a sub query and could cause performance issues with large data sets etc.
  • This method can be used for both the width and height and on any other object within PRD i.e. shapes, labels, pictures etc.

Hopefully this comes in handy when you start handling large data sets and charts!

Enjoy!

Posted by Prashant Raju on May 17, 2010 — 13 CommentsShare

13 Comments for “Creating Dynamic Sizing Charts with Pentaho Report Designer.”

  1. Posted by Farouk TANGAO on May 18, 2010

    That is great, once again good job !

  2. Posted by Michael Tarallo on May 22, 2010

    Prashant, such Sweetness!

    http://michaeltarallo.blogspot.com/
    Pentaho Pre-Sales Director

  3. Posted by Prashant Raju on May 22, 2010

    Thanks Michael!

    More to come :D

  4. Posted by Carsten on Nov 25, 2010

    Thanks for this tutorial. I Also tried to hide the chart if only one or no items were transmitted. This is maybe also a usefull feature.
    Here is the formula vor the property visible (size&position): =if([COUNTRY_COUNT] < 2; FALSE(); TRUE())

  5. Posted by Paul on Jun 8, 2011

    Thanks for the tutorial…. one question: how do you make the parameter optional? I see the “mandatory” checkbox on the GUI but how do you go about NOT including it in the SQL where clause (should nothing be selected, for example). Thanks, Paul

  6. Posted by Kishore on Jul 13, 2011

    Hi Prashant,

    This post is not related with this thread.

    We have our own application with login and password for authentication. We want to incorporate Pentaho with our application for reports. To login into pentaho I want to use my application tables i.e User table and Roles table.
    Could you please help me in detail step how can i use my database for pentaho login security.
    Thank you.

  7. Posted by Rajalakshmi on Jul 13, 2011

    Is it possible to change the alignment of the chart while there is some subsequent change in chart size.

  8. Posted by Rutvij on Sep 7, 2011

    I wanted to create a report which has two value columns with one category field.

    1. Count Ranges 100-10000
    2. Revenue Ranges $5,000,000-$50,000,000

    Is there any way we put both the value columns in a single bar chart with associated category.

    Please reply me ASAP since this is of the highest priority requirement.

    Thank in advance,
    Rutvij

  9. Posted by Vishwesh on Sep 13, 2011

    Excellent Post.. :)

  10. Posted by Atul on Oct 5, 2011

    Hi,

    Is there any tutorial for Linking with two Pentaho-Reports, or a subreport stuff.

    Regards,
    Atul Darne

  11. Posted by Silambarasan on Dec 9, 2011

    Hi i Create report designer using Bar Chart But try to show Spring MVC UI Bar chart not showing

  12. Posted by kuldeep on Nov 21, 2012

    plz explain this for PRD 3.9.0 GA.
    And if possible plz attach the sample report.

    thanks in advance .

  13. Posted by Erik on Feb 23, 2013

    Very clever and really well documented. Thanks!


Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>