(OS)BI

Pentaho Kettle - BIRT Output Step

2011-08-25T07:07:00.000-07:00

Matt announced the Pentaho Reporting output step for Kettle almost a year ago. This step allows Kettle to be used to generate and burst reports, which makes it much more than a pure ETL and data integration tool.
With the possibility to run Eclipse BIRT reports from within the Pentaho User Console, I thought it would be a shame not the have the possibility to generate and burst BIRT reports, and developed a similar output step for BIRT reports. Just download, unzip into your Kettle installation folder and (re-)start Spoon.

This step contains the 2.6.2 runtime, but that should be relatively easy to change. A version that supports BIRT 3.7 should arrive in the more or less near future.
The code for this step will be released in the next couple of days. In the meantime, do not hesitate to give me your feedback.

UPDATE (06-Sep-2011): there was a small bug in the parameter handling, should be ok now (thanks Sylvain @osbi_fr for noticing and testing).

Running Fully Functional Eclipse BIRT Reports in Pentaho - UPDATE

2011-08-22T07:40:00.000-07:00

When I wrote my post about integrating the Eclipse BIRT report viewer in Pentaho almost a year ago, Pentaho was at release 3.6. Since then, 3.7 and 3.8 have been released, and 3.9/4.0 is about to go GA. The biserver's plugin architecture has seen some changes over these releases, causing the BIRT plugin to break.
A new plugin is now available, compatible with Pentaho >= 3.6 and BIRT releases 2.6.x and 3.7. The installation instructions remain unchanged, check my original post for reference.
BIRT switched to a jar-based runtime with version 3.7. The default runtime jar, however, does not include the OpenDocument emitters that were also introduced in 3.7. If you want to offer your users the possiblity to export reports to ODF-formats, you’ll need to add some jars to the runtime webapp. All this takes is to copy the necessary jars from the full Eclipse BIRT (designer) download (<ECLIPSE>/plugins/org.eclipse.birt.report.engine.emitter.od*.jar and org.eclipse.birt.report.engine.odf_3.7.0.v20110609.jar, timestamps in file names may vary slightly) to <PENTAHO>/biserver-ce/tomcat/webapps/WebViewerExample/WEB-INF/lib/. Recent BIRT versions also need a commons-logging jar in the classpath. You can copy this file from the <PENTAHO>/tomcat/webapps/pentaho/WEB-INF/lib to <PENTAHO>/tomcat/lib or to <PENTAHO>/tomcat/webapps/WebViewerExample/WEB-INF/lib.

In addition to the original post: you can apply security to your BIRT reports by adding the BIRT report file extension (.rptdesign) to the ‘acl-files’ element in /pentaho-solutions/system/pentaho.xml, so it looks something like the element below:

xaction,url,prpt,xdash,xcdf,rptdesign

This plugin is very simple. All it does is tell the biserver to recognice the .rptdesign file extension, and point it to the BIRT report viewer for execution. As simple as it is, I will release the code for this plugin in a couple of days.

M2P - Move To Production for Kettle

2011-07-29T13:20:00.000-07:00

Introduction

Just like with any other software projects, serious ETL projects work in at least two, preferably three stages: development, acceptance and production. There may even be a fourth, testing environment between development and acceptance. You don’t want to introduce human errors in moving your content from one environment to the other, so it is important to add as much automation to this process as possible.

M2P

Move To Production (M2P) is a framework (to be released shortly) that aims to make the process of moving your Kettle jobs and transformations as automated and robust as possible.
Kettle comes with a flexible repository export job entry that allows to export your repository to a .xml file. With version 4.2-RC1, a command line utility -import.sh- allows you to import that .xml file into another repository. The combination of these two tools allow you to move your ETL content between environments, but it still takes some effort to perform this automatically. This is where this framework comes in.
The framework consists of a job, a couple of transformations and a Linux shell script. Adding support for Windows and/or Mac OS wouldn’t be hard (Mac OS may work right out of the box, but this is untested), but all of the projects I work on are on Linux, so there has not been any need to add that.

The first transformation ‘Parse Parameters’ quite surprisingly parses the parameters:

COMMENT: The comment that will be set for the new revisions of the transformations and jobs caused by the import. OPTIONAL
DIRECTION: ACC2PRD The direction of the migration. Possible values are DEV2ACC and ACC2PRD. REQUIRED
RULES_FILE: The path to the rules file that you want to use during the import. Defaults to an empty rules file /rules.xml. OPTIONAL
SRCFOLDER: / The folder that needs to be exported from the source repository. Defaults to /. OPTIONAL
TMP_FOLDER: /tmp/m2p A folder where the export will be stored intermediately. If not used, a temporary folder will be created in the executing user’s home. OPTIONAL
TRGFOLDER: / The folder in the target repository that the export needs to be imported in. Defaults to /. OPTIONAL

The second transformation ‘Parse Repositories’ reads a simple m2p.xml file in the M2P home folder. This xml file specifies the development, acceptance and production folders. The repositories in this file use repositories.xml in your /.kettle/repositories.xml file to connect to and export/import from, and set variables to decide the source and target repositories.

This is all the information we need to export from the source repository:

Once the repository export has been done, we are ready to pass the necessary parameters to the shell script to import into the desired folder in the target repository.

As can be seen from the shell script job entry, this shell script takes seven parameters:

the PDI working dir. We need to move to this directory to execute kitchen.sh.
the name of the target repository
the user name to connect to the target repository
the password to connect to the target repository
the folder to import into the target repository
the folder to read the exported repository file from
a comment to add to the newly created job and transformation versions for Enterprise Repositories.
the rules file you want to use for the import.

As you are moving between environments, it is assumed you have good reasons to replace existing jobs and transformations, either to add functionality or to fix bugs, therefore, ‘replace=true’ is hard coded in the script.

Moving content between environments

With all this information in place, the main job can be executed either through Spoon or -ideally- through Kitchen. After execution of the job, your new set of jobs and transformations will be in place in the target repository. Use the repository explorer to check, you will find a new version and comment if you are using an Enterprise Repository.

Summary

This framework aims to help you in the process of moving your jobs between environments. The software that allows you to do this is in it's RC stage, so there’s still a lot to add to this framework. More options will be added as soon as possible, but this has already proven to be of great help to avoid human errors in the move to production process.
This framework was developed because it was needed in projects I am working on, but will be released as a an open source project shortly. Feedback and tips to improve it or to add new functionality are -as always- very welcome.

Update: M2P is now available on github.

Pentaho Reporting Custom Functions

2011-07-27T14:43:00.000-07:00

Pentaho’s reporting engine offers an extensive library of functions for use in your reports. Sometimes however, the functions in this library just don’t cut it. This may be because you use some formula all over the place in your reports, because a function you really need isn’t there, or for whatever reason.
At a project I am currently working on, people are in the process of moving their current BI platform to Pentaho, but some of the functions they use extensively in their current reports do not exist in the Pentaho report engine.
This is where LibFormula comes in. The Pentaho reporting engine maintains LibFormula as a side project for these functions. LibFormula was written from the ground up to be extensible, so if you have basic Java knowledge, LibFormula makes it very easy for you to develop your own custom functions.

This is exactly what was needed for this project, and we started porting those functions of the current platform to LibFormula.

Many more will follow, we have added these functions so far:

QUARTER(“Date”): returns the quarter for a given date.
ADD_MONTHS(“Date”; “Number”): returns a new date, created from the first parameter, augmented with the number of months given in the second parameter. If this second parameter is negative, a number of months is subtracted from the first parameter.

All the technical details about developing custom functions are in the Wiki, but the whole process is quite straightforward and proves once more the power and flexibility of open source software over its traditional, closed counterparts.

To try these functions for yourself, just download the jar, drop it into <PRD>/lib, restart PRD and have a look at the functions list. Let me know if you run into any issues, or if there is a function your really want to see in this library. This download is binary only for now, but the code will be released in a couple of days.

PME Publish: Metadata File Name and Publish Location

2011-07-14T13:21:00.000-07:00

The Pentaho Metadata Editor (PME) is theoretically a very powerful piece of software, but it hasn’t been getting the love it deserves lately. In fact, the only thing that changed since 2009 is the version number (yeah, I know there’s ‘IN’-parameter support in 4.0-RC1).
One of the things that enerves me most is the publish dialog.

metadata file name: there is no option to specify a name for your metadata file on the server. This leads people to believe there can only be one file per folder, which is not correct. There can be multiple metadata files per folder, but as PME has ‘metadata.xmi’ hard coded as a file name, every new publish overwrites the previous file. To work with multiple metadata files in one folder, you will need to export your model to a .xmi file and manually move it to your solution folder on the server. This process needs to be repeated for every single change you make to a file.
hard coded default solution: the ‘steel-wheels’ solution is hard coded in PME. You will need to change this to the solution folder on your server every single time you publish a model.

Instead of waiting for Pentaho to implement PMD-455, I downloaded the code and made some quick modifications to this dialog: the Metadata File Name and Publish Location fields now store previous versions in a drop down box and default to the last value used, just like the publish url.

For me, this makes getting my metadata models on the server a lot easier. Feel free to download your copy of this modified PME (based on trunk code, the modified PublishDialog.java is included) and give it a try.

Running fully functional Eclipse BIRT reports in Pentaho

2010-09-13T13:01:00.000-07:00

While exploring Pentaho's echo plugin a few days ago, I was wondering what it would take to write a plugin that would allow Eclipse BIRT to be used in the Pentaho User Console.

While the Pentaho report engine has made some huge leaps in the last couple of years, there are still areas where it is outclassed by BIRT. Apart from pure functionality, Eclipse BIRT is a very (arguably the most) popular open source BI project.

Pentaho has supported BIRT since the earliest days of the platform. However, support has never been splendid: functionality is missing or incomplete, the BIRT engine in Pentaho is still at 2.2.1 (which was released almost four years ago), so people had problems running their reports created with newer version of the BIRT report designer. Last but not least, every single report had to be called in it's own action sequence. This action sequence needed to re-specify the parameters that were already implemented in the report. One had to really want to use BIRT reports in Pentaho quite desperately to go through this mess.

The BIRT report viewer, the application behind the 'View Report in Web Viewer' option in BIRT's report designer, is also available as a web application in the BIRT runtime download. Instead of writing a new plugin from scratch, which would undoubtedly result in the same mess as with the old plugin, I wanted to drag this report viewer into the PUC.

I was quite surprised to see how easily this can be done. Extract the WebViewerExample from the runtime archive to your Pentaho server's webapps folder (/biserver-ce/tomcat/webapps/). Next, we need to tell BIRT where to look for reports. Open /biserver-ce/tomcat/webapps/WebViewerExample/WEB-INF/web.xml and add your pentaho solutions folder to the BIRT_VIEWER_WORKING_FOLDER parameter.

<context-param>
<param-name>BIRT_VIEWER_WORKING_FOLDER</param-name>
<param-value>/opt/pentaho/biserver/ce/3.6.0-BIRT/biserver-ce/pentaho-solutions</param-value>

</context-param>

You can test if this application was installed and configured correctly by firing up your Pentaho server and going to http://localhost:8080/WebViewerExample/. Click 'View Example' in the lower left corner to run a sample report.

Get the plugin and unzip it: the 'birt-plugin' folder should go to your pentaho-solutions/system folder, the sample report SalesInvoice.rptdesign should go to pentaho-solutions/steel-wheels/reports.

All that is left to be done now is make the command-tag in birt-plugin/plugin.xml point to the BIRT web viewer and pass in the correct path, solution and action values:

<command>http://localhost:8080/WebViewerExample/frameset?__report={solution}/{path}/{name}</command>

Start your Pentaho server and verify that you can see and execute the sample BIRT report. If this works, you have a fully functional Eclipse BIRT environment integrated in your Pentaho server.

Building Pentaho in three steps with CBF

2010-02-05T13:26:00.001-08:00

CBF

One of the most underestimated projects in the Pentahosphere is the Community Build Framework, CBF for short, an ant build script created by CDF-wizard Pedro Alves.

Anyone who has worked on several projects and/or Pentaho versions knows it very easily becomes hard to manage all configurations and solutions, not to mention versioning, backups, etc.

All of this becomes more or less trivial with CBF. The approach used by the CBF has many advantages: all project specific configuration and solution files are kept together, a configuration file tells which Pentaho server version to build and what application server to run it on, and patches are applied to the project files; no original Pentaho sources are modified.

Now, let's have a look at the three steps we need to take to get CBF running on your system. The server we'll be building will be a very basic one, but it will have all you need to get started with CBF and it won't take you any longer than 15 minutes to have it up and running.

Prepare

Your system needs to have some tools available before you can start building:

java: kicking in an open door: you need a java compiler to build your Pentaho server. Issue a 'javac -version' to make sure you are using a (decent) JDK.
ant: 'sudo apt-get install ant'
subversion: 'sudo apt-get install subversion'
xmltask.jar: download and move the jar file to $HOME/.ant/lib.
tomcat: download the tomcat server (5.5.28 is the latest version at the time of writing, haven't tried anything except trunk with Tomcat 6). Extract the downloaded archive to your system (/opt in my case)
a hypersonic database: Grab one from the downloaded svn sources or from a downloaded biserver you have on your system. Switch to your own database later on, but try to get CBF running with hypersonic for your first build.
grab a copy of the CBF's build.xml from the CBF wiki page. Extract to your CBF directory and rename the build.xml<VERSION> to build.xml.

Create

CBF uses system directories that are shared between all projects in your CBF setup and a per-project-directory.

The 'global' system directories are:

source code folder(s): every version of the source code will be kept in one folder. These source code files will be left unmodified. All changes to the original sources will be made through patches.
target-build: this is the default directory all source code files will be copied to to build the system. All patches will applied to this folder.
target-dist: the destination folder. This is where the Pentaho server will be copied to after everything has been built.

The target-build and target-dist are dealt with by the ant build system, no user intervention is needed here.

Let's check out the source code! Move to your CBF folder and issue the command below to check out the sources for version 3.5.2 into a folder called '3.5.2.stable':

svn co svn://source.pentaho.org/svnroot/bi-platform-v2/branches/3.5.2.stable

Apart from the system directories, we need a directory per project to store our patches, configuration files and solutions (reports, xactions, ...).

Move to your CBF directory and create a folder 'project-myProject', with three subdirectories: config, patches and solution.

Your project folder should look something like this:

-- project-myProject:

|------ config

|------ patches

|------ solution

build.xml

In the config directory, we'll put a file called build.properties. This file will tell the ant build system what and where we want to build.

A minimal build.properties contains, among others, the path to your java compiler and your solution, application server and Pentaho server information.

Modify the example below and save this as your build.properties:

## Path to your java compiler (javac)
javac.path = /usr/lib/jvm/java-6-sun/bin/

## Solution path and solution deploy path.

solution.path = /opt/pentaho/CBF/project-myProject/solution

solution.deploy.path = /opt/pentaho/CBF/project-myProject/solution

## Accepted server names are 'tomcat' and 'jboss'.
server.name = tomcat
## Path to your tomcat server.
tomcat.path = /opt/apache-tomcat-5.5.28/

## do you want to copy the 'steel-wheels' and 'bi-developers' samples to your server? true|false
copy.pentaho.samples = true

## options to pass to your Java Virtual Machine when starting the application server.
## Modify at will, or leave untouched if unsure.
java.opts = -Xmx512m -XX:MaxPermSize=512m -Xrunjdwp:transport=dt_socket,address=8765,server=y,suspend=n

## Which Pentaho sources do you want to use to build your server?
pentaho.dir = 3.5.2.stable/

## What is the target build directory?
pentaho.build.dir = target-build/

Next, we need to tell our server where to find the solution folder. As this is set in the 'solution-path' parameter in web.xml, we'll need to patch this file in our server.

Patches are put in the patches-directory in exactly the same directory structure as they are in the original source tree.

So, to patch web.xml found in '3.5.2.stable/bi-platform-appserver/webapps/pentaho/WEB-INF/web.xml', we create a directory called 'target-dist/server/webapps/pentaho/WEB-INF/' and copy the web.xml there.

In this file, we'll fill in the 'param-value' for the element 'param-name' to '@solution.deploy.path@'. The @###@-syntax fills in the value we specified for this variable in build.properties.

Your patches directory should now contain one file:

patches/target-dist/server/webapps/pentaho/WEB-INF/web.xml

Web.xml should hold at least one variable:

<web-app>

<context-param>

<param-name>solution-path</param-name>

<param-value>@solution.deploy.path@</param-value>

</context-param>

...
</web-app>

Your solution-folder can hold anything like any other (pentaho-)solutions folder. We have set 'copy.pentaho.samples' to 'true' in our build.properties. This will copy the bi-developers and steel-wheels samples to our pentaho-solutions folders, so there's no immediate need to add any other sources.

Build

This is the time to start your hypersonic database. Go to your sources or biserver/data directory and run start-hypersonic.sh.

Once your hypersonic (or other database) server is running, it's time to start building our server (Use 'ant -Dproject=myProject -p' to get a list of parameters to pass to build.xml).

Issue the command below to clean up all traces of previous builds and to build and run the system.

ant -Dproject=myProject dist-clean all run

Have a look at all the build messages scrolling by until you see the line 'INFO: Server startup in <number> ms', check your server and pop a cork to celebrate your entry in the wonderful world of CBF.

Crosstabs in PRD 3.5

2009-11-29T04:25:00.001-08:00

Crosstabs

Crosstabs are an easy and relatively easy way to visualize tabular data along two or more axes. They are an indispensable part of a modern report engine.

Although crosstabs still are an experimental feature in PRD 3.5, and as such are not up to par with their counterparts in Eclipse BIRT and the likes. They will not make it into 3.6 as a stable feature, but even being expermimental, they are already powerful enough to create some really useful reports.

Will Gorman's book proved once again to be the best source for information regarding Pentaho Reporting. This post is an elaboration on the mainly textual explanation in his book...

Create the Crosstab Report

We'll create a crosstab that will show the revenue and quantity orderd per product line for all months in a given year.
Start by opening the report designer and press CTRL-ALT-O. You will be shown the alert message below. Although the message says to restart PRD, the crosstab functionality is immediately available without restart.

UPDATE: The CTRL-ALT-O key combination has been replaced with a dialog in PRD 3.6. In Edit -> Preferences -> General, check the option 'Enable (unsupported) experimental features.

Create a new report, set it to (A4) landscape (File --> Page Setup) and create a datasource on SampleData with the query below.

select        distinct
                PRODUCTLINE,
               T.MONTH_NAME,
                sum(TOTALPRICE) as TOTALPRICE,
sum(QUANTITYORDERED) as QUANTITYORDERED
from         PRODUCTS P     inner join ORDERFACT O on (P.PRODUCTCODE = O.PRODUCTCODE)
                                       inner join DIM_TIME T on (O.ORDERDATE = T.TIME_ID)
where        T.YEAR_ID = 2004
group by    PRODUCTLINE,
                MONTH_NAME
order by    PRODUCTLINE,
                T.MONTH_ID
;

Next, in the 'Structure' panel, right click on the Master Report node and select 'Add Crosstab Group'. This will add a new crosstab band to your report canvas.

Now, move over to the Structure panel. PRD 3.5 doesn't know about drag an drop or the report canvas to create crosstabs (experimental feature, anyone?), so every single property will have to be set in the structure panel.

We want to see the sales results per product line for all months in a selected year.
First, we'll define a row dimension (Product Line), a column dimension (Months) and our first detail dimension (Total Sales).
In the Structure panel, open crosstab --> crosstab-row-group-body --> crosstab-row-group, specify 'PRODUCTLINE' as group in the Attributes panel.
Next, right click the Group Header and add a string-field. In the Attributes panel, specify PRODUCTLINE as the value for the field property.

More row groups can be added by right clicking 'crosstab-row-group' and selecting 'Add Crosstab Row Group'.

We'll stick with the product lines for now and add the months columns. Select crosstab-column-group-body --> crosstab-column-group and set the group property to MONTH_NAME.
Now, right click the Group Header, choose Add Element --> string-field and set the field property to MONTH_NAME.
Go to the 'Style' panel, CTRL-click the PRODUCTLINE and MONTH_NAME string-fields and set them to bold.

Finally, open the Details Body and add a number-field to the Details item. Set this number-fields' field property to TOTALPRICE.

Your crosstab column section should now look something like the one below.

If you run a preview of your report at this point, you'll notice a layout problem:

Although the data is displayed correctly, the month names have not been positioned correctly above the corresponding data columns.
This is easily solved by adding a label to the crosstab-row-group title-header (right click the crosstab-row-group's title-header, add element, label).

Remove or replace the default label text and set this label's width to the same value as the PRODUCTLINE string-field's.
I have applied some additional formatting, but your crosstab should now look something like this:

Add a second measure

Add a second number-field to the crosstab's Details and set its field property to QUANTITYORDERED.
We need this element to start after the total price, so set this element's x position to the width of the total sales element.

Next, change the width of the month name element in the crosstab-column-group's Group Header to the total width of the Total Sales and Quantity Ordered elements (110 in this example's case).
Add two more label-fields to the Group Header. Show these labels right below the month name field by setting their y-position to the height of the month name, right-align them and sync their x-position to the corresponding columns.

If you followed these steps, you now have a crosstab report that shows the sales and ordered quantities per month for every product line:

Grab your copy of the report here.

Conclusion

Although there is still quite a lot of work to do, it already is possible to create some fairly decent crosstabs in PRD 3.5.
I haven't been able to get row banding or any of the standard summary functions to work on crosstabs. Will's book use MDX totals and mention similar SQL-workarounds (like Oracle's window functions) to display summaries in crosstabs, but this should (and probably will) be supported in future PRD releases.

Using Javascript in PRD 3.5

2009-11-05T12:49:00.000-08:00

Introduction

One of the major changes that comes with Pentaho Report Designer (PRD) 3.5 is the ability to use Javascript code in reports.
Scripting allows you to create highly interactive reports that allow your report users to explore their data in ways that are not possible with traditional static reports.
We'll use javascript to create a tooltip that displays a product's properties. This javascript code will only be visible when the report is viewed as HTML, so other output formats (PDF, Excel, ...) are not impacted.

The Report

We'll create a sales overview per productline on the SampleData database that comes with Pentaho.
First of all, add a JDBC data source and create parameters for YEAR and MONTH.

select        distinct
                 DIM_TIME.YEAR_ID,
                 DIM_TIME.MONTH_DESC,
                 PRODUCTS.PRODUCTCODE,
                 PRODUCTNAME,
                 PRODUCTLINE,
                 PRODUCTDESCRIPTION,
                 PRODUCTSCALE,
                 PRODUCTVENDOR,
                 QUANTITYINSTOCK,
                 BUYPRICE,
                 MSRP,
                 SUM(QUANTITYORDERED) as QTY,
                 AVG(PRICEEACH) as PRICEEACH,
                 SUM(TOTALPRICE) as TOTAL
from        PRODUCTS    inner join ORDERFACT on (PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE)
                                   inner join DIM_TIME on (ORDERFACT.ORDERDATE = DIM_TIME.TIME_ID)
where        DIM_TIME.YEAR_ID = ${YEAR}
and        DIM_TIME.MONTH_ID = ${MONTH}
group by    PRODUCTS.PRODUCTCODE,
                 PRODUCTNAME,
                 PRODUCTLINE,
                 PRODUCTDESCRIPTION,
                 PRODUCTSCALE,
                 PRODUCTVENDOR,
                 QUANTITYINSTOCK,
                 BUYPRICE,
                 MSRP
order by    PRODUCTLINE
;

Next, create a group for the product line, make the group header visible, and add product name, quantity, price each and total to the details band.

The Javascript Code

With the report's structure in place, we're ready to add the Javascript to the report.
Download Walter Zorn's tooltip javascript library and unzip it.
To include the library in your report, select the master report, go to the attributes tab and add the line below in the 'append-body' of the html section.
This tag will be created as the first element in the <body> tag when added to the append-body property. The script throws an error if you try to put it in append-header (which puts it in the <head> section).

If you include external javascript files in your report and want to use your report on the BI server, you need to make sure the Javascript file is available to Pentaho's web application after publishing.
To access the script from the url below, I have put wz_tooltip.js in <APPSERVER>/webapps/pentaho/js.

Call the Product Properties

Next we're going to tell the report object [PRODUCTNAME] we want it to create a tooltip when the mouse hovers over it.
Select the product name and specify the on-mouse-over property in the html-events category. As we need to pass some report objects as product properties to the report, we have to specify a formula by clicking the green plus button.

="Tip('<table><tr><th colspan="2">Product Details:</th></tr><tr><td><b>Description:</b></td><td>" & SUBSTITUTE([PRODUCTDESCRIPTION]; "'"; "'") & "</td></tr><tr><td><b>Vendor:</b></td><td>" & [PRODUCTVENDOR] & "</td></tr><tr><td><b>Scale:</b></td><td>" & [PRODUCTSCALE] & "</td></tr><tr><td><b>In stock:</b></td><td>" & [QUANTITYINSTOCK] & "</td></tr><tr><td><b>Buy Price:</b></td><td>€ " & [BUYPRICE] & "</td></tr><tr><td><b>MSRP:</b></td><td>€ " & [MSRP] & "</td></tr></table>', BGCOLOR, '#f0f8fe', DELAY, 600, WIDTH, -450, OFFSETY, 15, FONTCOLOR, '#3b7eaf', BORDERCOLOR, '#3b7eaf');"

This formula calls the WZ library's Tip function with the string to display in the tooltip and some formatting parameters. The tooltip message can contain HTML code, so we create a table with a header that spans two columns. The left column of this table is filled with the property names ('Description, Vendor, Scale, In Stock, Buy Price and MSRP). The values for these properties are filled in when the HTML for this report is generated.
Some product descriptions contain single quotes that need to be escaped to avoid parsing troubles (bold section).
After the product detail table, some formatting options are passed to the script. Check the script's website for a full reference of all possible properties.

If you run the report at this point, you will see a tooltip when you hover the mouse over a product name shortly.
However, when you move the mouse away from product names, you'll notice that the last tooltip sticks to the pointer.

We have specified what happens when we move the mouse over the product name, now we need to tell the script what needs to happen when we move the pointer away from the product names.
There is no on-mouse-out property in the Attributes panel. I unzipped the .prpt file and found the on-mouse-over property's behavior is specified in layout.xml. I tried manually adding the onmouseout event in this file, but that didn't work (which makes sense).
I then added a little piece of Javascript code to the report that creates an event listener for mouseout events. With this event in place, we can safely call the library's UnTip() method to get rid of the tooltip when the cursor leaves that product names.

Add this piece of code to the Master Page's append-header attribute to put it in the <head> section of the report HTML document.
Put a call to the addMouseOutEvent to the on-mouse-over formula to avoid modifying the tooltip library.
Your formula should now look something like the one below, and the tooltip should disappear when you move the pointer away from the product names.

="addMouseOutEvent(this); Tip('<table><tr><th colspan="2">Product Details:</th></tr><tr><td><b>Description:</b></td><td>" & SUBSTITUTE([PRODUCTDESCRIPTION]; "'"; "'") & "</td></tr><tr><td><b>Vendor:</b></td><td>" & [PRODUCTVENDOR] & "</td></tr><tr><td><b>Scale:</b></td><td>" & [PRODUCTSCALE] & "</td></tr><tr><td><b>In stock:</b></td><td>" & [QUANTITYINSTOCK] & "</td></tr><tr><td><b>Buy Price:</b></td><td>€ " & [BUYPRICE] & "</td></tr><tr><td><b>MSRP:</b></td><td>€ " & [MSRP] & "</td></tr></table>', BGCOLOR, '#f0f8fe', DELAY, 600, WIDTH, -450, OFFSETY, 15, FONTCOLOR, '#3b7eaf', BORDERCOLOR, '#3b7eaf');"

Download this report and the tooltip script here.

Creating Sparklines in Pentaho Report Designer 3.5

2009-10-18T05:37:00.003-07:00

Sparklines

Sparklines in Pentaho Report Designer (PRD) 3.5 accept data in the form of a comma separated (CSV-like) list or as an array of number objects.

The types of sparklines that can be created are bar, line and pie chart. As has been discussed over and over and over again, pie charts are bad design. In my opinion, series/(spark)lines of pie chart only make matters (so much) worse, sop I'll just pretend I never heard about them and ignore them.

Sparklines can be created using a report element or through a function. Both approaches have the same results, but a function's results can be passed on to other functions or expressions.

Create a Data Source

We'll create a data source on the Pentaho SampleData database in MySQL. Notice how the highlighted line uses MySQL's group_concat function to create a comma separated list of all values in the group, which is exactly what we need.

Click the preview button to verify your query. The preview doesn't know about query parameters and will show a blank window if your query is (syntactically) correct.

Add the Sales Sparkline

Drag a line-sparkline element from the left toolbar into the details band. Double click and select 'SALESDETAILS'. The field and more optionscan be set from the panel on the right hand side. Make sure you are in the structure/attributes-tab. Leave the type to the default (line-sparkline), and select 'SALESDETAILS' as the field name.

The layout and visualization of the sparkline can be changed from the 'Style' tab properties.

Interesting changes are the lastcolor, which shows a dot in the chosen color to indicate the last value, and text-color to specify the line color.

Additionally, you may want to enable anti-aliasing in the 'Object' properties have smoother lines.

A preview of the sparkline in your report should look similar to the one below. Note the differences in sales data volumes between the product lines.

Add the Quantities data to the query

We'll now add data for a second sparkline to the report by adding the lines below to your query. We want this sparkline to show the average quantities for the last and the six preceding months.

Add six columns (QTYM1 through QTYM6) to your query, one per month for the quantities sold over the last six months for every product line.

Use functions to format the data

We'll need to create either an array of numbers or a CSV-like list from these columns.

To create a CSV-like list directly in the query, we could have used the MySQL concat-function. We have LibFormula functions to do this in the report. Go to the data tab, right-click 'Functions', click 'Add Function' and pick 'Common \ Open Formula'. In the function properties, change the name to 'QtySparkLine', and modify the formula like show below.

Creating a CSV-like list is very similar to creating an array: add an Open Formula function to the report, change the name to QtySparkLine, but change the formula to =[OBJ1] & "," & [OBJ2] ...

Add the Quantities Sparkline

Drag a bar-sparkline element to the detail band. Double click and select QtySparkLine, or go to Structure \ Attributes, and specify QtySparkLine as the field attribute value.

The 'sparkline' attribute group holds one element, 'spacing'. This property specifies the amount of spacing that is used between the bars in our sparkline. The default value is 2, let's change it to 1.

Switch to the Style tab. In the 'Sparkline' group, we have 'high-color' and 'lastcolor'. The lastcolor-property is the same as with the line-sparkline: it is used to specify the color of the last value in the sparkline. The QTY column, added to the right of the sparkline, provides the exact value of the last item in the sparkline for reference.

The high-color changes sets the bar color for all values in the sparkline that have a higher value than the last value (although I haven't found any documentation on this property and haven't looked it up in the source code yet).

You Style properties should look something like this:

Run

The sparklines in your report should now look similar to the ones below. That's all it there is to creating sparklines in PRD 3.5...

Dynamically Creating a Pentaho CDF Dashboard

2009-09-26T10:19:00.001-07:00

Dashboard Structure

I recently had to create a dashboard where objects needed to be created and added dynamically depending on the user's input.
As this dashboard shows the power and flexibility of Pedro Alves' Community Dashboard Framework, I have reworked it to work with the SampleData database that comes with a Pentaho installation.

The layout of this dashboard is pretty simple. There are two global parameters (year and month) that apply to all objects in the dashboard.

The user can select a single year, and has the option to choose one
single month or 'All' for all months in the selected year.

The dashboard is structured per row: three objects are shown per product line (the objects in this example are the top 10 for revenue per product, margin per product and revenue per country).

Similar to the month parameter's behavior, it is possible to choose a single product line or 'All' for the total of all product lines. If all product lines are chosen, the selected row is used to display the total for all product lines. Below the totals, a new line is created for every product line.

Apart from the 'All' option, an extra option '-' has been added to the month and product lines lists of values to prevent the first value in the list from being selected by default.

Template

The dashboard lives in a directory 'dyncdf' with a subdirectory 'dynamic-cdf'. Apart from the default files (index.xml, index.properties, ...), the
dashboard's properties are specified dynamic-cdf.xcdf.
The <style> tag specifies we'll use the template <solution-folder>/system/pentaho-cdf/template-dashboard-dynamic-cdf.html
This template contains the structure (html) of the document the dashboard will be put into:

The dashboard's structure is defined in template.html, the javascript code is saved in a separate file (scripts/dyncdf.js) and is called through the metalayer (first line in code block below). There are three static blocks (divs) that make up the layout of the dashboard:

paramDiv: holds the year and month parameters
navDivTop: provides a button to add and/or remove product lines.
navDivBottom: the same functionality as in navDivTop was added at the bottom of the
page again for usability reasons. navDivBottom is visible as soon as
the dashboard contains three rows.

The dashboard's content rows are created between navDivTop and navDivBottom.

Create the DIVs and dashboard components

As some dashboard content needs to be added and removed dynamically, it doesn't make sense not to generate all content dynamically (except for the year and month parameters).

The javascript code contains several functions, the most important being addPLine(auto) and removePLine(). addPLine(auto) creates the html
structure and objects for a new dashboard row. The 'auto' parameter indicates if we are creating a set of rows for all products lines: if null, a single row is created, if 'All', a new row is created for every product line in the loop.
removePLine() removes the last line from the dashboard.

The addPLine function calls createDiv(rowNb) to create the html structure for the new row and createPLine(auto), which creates the
product line selector objects for every new row. Upon being created, every product line selector component creates its own content components by calling createComponents. The createComponents passes through functions that create the CDF (JSON) objects, and then adds them to the dashboard (tmp = JSON object).

In the product lines' postChange function is checked whether the selected item is 'All' and news have to be created for all product lines.
The (MDX) queries for the three graph components are created depending on whether all or a single product line(s)/month(s) is/are selected.
The createDiv(auto) function creates dashboard rows for each desired product line in the structure below:

When 'All' in product lines is selected, after all rows have been created, a second loop runs over all newly created rows and sets the correct product line selected.
As Javascript does not fire an onChangeEvent when a selected item i a select element is changed programmatically, a call to 'Dashboards.processChange("pLineSelector" + i);' is necessary to notify the dashboard of the element's changed value.

Modifications to the default Pentaho installation

Modifications to the Steel Wheels cube

The default Steel Wheels cube is modified slightly to better fit this dashboard:

the Markets dimension has a new hierarchy with only the country
the Product dimension has a new hierarchy with Line and Product levels (and not the vendor)
the Time dimension has a new hierarchy with Years and Months levels (and not the Quarters)
a new Margin (calculated member) measure has been added (sales - (quantity * price each))

Modification to JFreeChart.xaction

The jFreeChartComponent in CDF maps all possible attributes in xactions to a 'one size fits all' Action Sequence.
There is no parameterizating of the bar color for bar charts in this component, but that only requires a small modification: the .xaction included with CDF contains 14 hard-coded parameters for the color-palette parameters. These can be parameterized by adding COLOR-PALETTE1 through COLOR-PALETTE14 as children of the
<inputs> element. Specifying the default-value allows to use the previously hard coded value if no color was passed from the component.

The palette-colors inputs can be called later in the ChartComponent definition:

Installation

Just download and unzip in your solutions folder.
All files should go in the right places. Make sure you have a 'sampledata' data source. Move the default steelwheels.mondrian.xml out of the way and publish the schema in the archive.
This was developed and tested on Firefox/Ubuntu. What I've done with this on Chrome and IE can hardly be called testing, but everything seemed to work. Opera returned some error that I didn't bother to investigate.