June 27, 2006
Submitted by Mike D'Amour, Pentaho Team Developer

We have received lots of questions lately regarding the use of charts in a Pentaho report. This article is meant to address the tip of the iceberg, embedding a chart in your Pentaho report at the report level and at the group level.

I will use the sample Hypersonic database that comes with the Pre-configured Install (PCI) to build a report that displays actual and budget numbers, grouped by Region and Department. First we'll embed a chart that represents the report as a whole, then I'll show you how to create one chart per region, representing just that region's data.

Resources Before You Get Started

Before you dive in, be sure you have the following resources.

Pentaho Pre-Configured Install, version 1.1.6 or later
Pentaho Getting Started Guide, version 1.1.6 or later
Pentaho Report Design Wizard, version 1.1.6 or later
Pentaho Report Design Wizard User's Guide, version 1.1.6 or later
NOTE!!
In order for Report Design Wizard 1.1.6 to properly render the chart, you MUST PERFORM THE FOLLOWING SIMPLE STEPS! This bug has been fixed as of the 1.1.7 release.
- 1. Open the reportwizard.bat (or reportwizard_*.sh file, if your *nix based)
  2. Add ./lib/jfreechart-1.0.1.jar to the end of the classpath.
  3. Save and close the file. My reportwizard.bat looks like this now:
    
    set PATH=%path%;./lib/swt-win32 java -Djava.endorsed.dirs="./lib/jdbc" -cp ./resources;./bin;./classes;./lib/pentaho-reporting.jar;./lib/activation.jar;./lib/asm-attrs.jar;./lib/bsf-2.3.0.jar;./lib/bsh-1.3.0.jar;./lib/castor-0.9.9.jar;./lib/commons-collections-3.1.jar;./lib/commons-dbcp-1.2.1.jar;./lib/commons-pool-1.2.jar;./lib/dom4j-1.6.1.jar;./lib/eigenbase-properties.jar;./lib/eigenbase-resgen.jar;./lib/eigenbase-xom.jar;./lib/itext-1.4.jar;./lib/javacup.jar;./lib/jaxen.jar;./lib/jcommon-logginglog4jlog-1.0.2.jar;./lib/jcommon-1.0.4.jar;./lib/jcommon-xml-1.0.4.jar;./lib/jfreereport-0.8.7-3.jar;./lib/js.jar;./lib/jug-lgpl-2.0.0.jar;./lib/libfonts-0.1.7.jar;./lib/log4j-1.2.8.jar;./lib/mondrian.jar;./lib/pentaho.jar;./lib/pixie-0.8.5.jar;./lib/poi-3.0-alpha1-20050704.jar;./lib/libloader-0.1.1.jar;./lib/saxon8.jar;./lib/saxon8-dom.jar;./lib/saxon8-jdom.jar;./lib/saxon8-sql.jar;./lib/saxon8-xom.jar;./lib/saxon8-xpath.jar;./lib/simple-jndi-0.11.1.jar;./lib/swt-win32/swt.jar;./lib/xercesImpl.jar;./lib/xml-apis.jar;./lib/nicky.jar;./lib/jfreechart-1.0.1.jar org.pentaho.jfreereport.wizard.ReportWizard

Build Your Report

Unzip the Pentaho PCI and run through some of the samples, using the Pentaho Getting Started Guide to guide you. The sample databases will be started as part of the PCI startup process. For this exercise, you only need the PCI sample data, which can be downloaded separately. I recommend you use the PCI because a natural next step after following this exercise will be to publish your report, and the PCI is the easiest place to publish your report to.
Unzip the Report Design Wizard, using the Pentaho Report Design Wizard Guide as your guide.
Run the Report Design Wizard ( execute the reportwizard.bat|.sh file).
Choose "Blank Template", then click the Next button.
On the Data Source and Query tab, I chose JNDI, and selected the SampleData dataset.
On the same step in the wizard, I defined the following query in the Query Details box:
```
SELECT REGION, DEPARTMENT, ACTUAL, BUDGET FROM QUADRANT_ACTUALS
```
Click Next. The next step defines the report layout. Move Region and Department to the Group By box, and Actual and Budget to the Selected Items box.
Click Next twice, skipping steps 5 and 6. These steps require no changes for this exercise.
Step 7 is where we have been wanting to go all along. It is here that you can define a report level chart. Under the Options section, check the Use Chart checkbox.
Click the Configure Chart... button, which should give you the Chart configuration dialog.
In the Chart Title text box, enter "Actual vs. Budget".
For Chart Type, accept the default "bar" type.
The Category is the column from your SQL query that defines the data groupings for your chart series data. We want to see actual and budget by region and department, so choose DEPARTMENT from the Category combo box.
Move Actual and Budget from the Series Column list to the Series list. Do the same on the Values tab. These will be your data points.
The Horizontal Offset setting aligns the chart horizontally in the report. To obtain the look that the chart is centered, I will set the Horizontal Offset to 20.
Accept all other values as defaults for the chart settings. Click OK on the Chart Configuration dialog, and click the wizard's preview button to see what we have so far.
So that looks good, you say, but the actual and budget values are aggregated for all regions, which isn't quite so useful. What we really want to see is a chart graphing actual and budget per department FOR EACH REGION. Here is where we will explore group level charting.
Start by UN-CHECKING the Use Chart checkbox on Step 7 of the Wizard. That is the report level charting and we aren't interested in that anymore (fickle report designers aren't we?
Go to Step 5 in the wizard by clicking on the "5" at the top of the window.
In the Groups box, you should see Region and Department. Click on Region and look at the right side of the window. You should see a Use Chart checkbox! Woohoo! Check that checkbox, and Click the Configure Chart button. You should see our familiar Chart Configuration dialog.
We need to do things a bit (not much) differently, since we are creating group level reporting. Our chart title (Actual vs. Budget), chart type (bar), and Category (department) are all the same, so set them now.
Move Actual and Budget from the Series Column list to the Series list. Do the same on the Values tab. These will be your data points.
The key difference and the magic behind group level reporting is the Reset Group. The Reset Group option indicates the parameter that triggers a new set of data for each chart. For our exercise, that would be the Region, right? Go ahead and set the Reset Group to Region.
Click OK to exit the Chart Configuration dialog. Click the wizard's preview button to see what we have now.
Much better! Now we have actual and budget data by department for the region Central. If you scroll through your report, you will see for each region grouping a chart displayed with the appropriate contextual data.

At this point, you can publish your report to the server, or you can take advantage of the powerful features in the Report Design Wizard to customize the look and feel of you report. I'll leave that as an exercise for you and a leisurely read of the Pentaho Report Design Wizard User's Guide.