Pentaho Reporting 3.5 for Java Developers

Pentaho Reporting lets you create, generate, and distribute rich and sophisticated report content from different data sources. Knowing how to use it quickly and efficiently gives you the edge in producing reports from your database. If you have been looking for a book that has plenty of easy-to-understand instructions and also contains lots of examples and screenshots, this is where your search ends.

also read:

Java Tutorials

Java EE Tutorials

Design Patterns Tutorials

Java File IO Tutorials

This book shows you how to replace or build your enterprise reporting solution from scratch with Pentaho’s Reporting Suite. Through detailed examples, it dives deeply into all aspects of Pentaho’s reporting functionalities, providing you with the knowledge you need to master report creation.

What This Book Covers

Chapter 1—An Introduction to Pentaho Reporting provides a quick overview of Pentaho
Reporting, including a feature summary and architectural summary, as well as a history of the product.

Chapter 2—Pentaho Reporting Client and Enterprise Examples tells how to install and
create reports, and how to embed reports in your J2EE and client Java applications. Chapter 3— Pentaho Reporting Examples in the Real World tells how to connect to a JDBC data source and create realistic inventory, balance, and invoice reports, including charts and sub-reports.

Chapter 4—Design and Layout in Pentaho’s Report Designer takes a deep dive into Pentaho’s Report Designer, learning how to create great-looking reports.

Chapter 5—Working with Data Sources teaches the various ways to connect your report to live data, including JDBC, Hibernate, Java Beans, OLAP, and many other data sources.

Chapter 6—Including Charts and Graphics in Reports is about incorporating Pie, Bar, Line, and many other chart types in your reports, as well as including dynamic images in your report.

Chapter 7—Parameterization, Functions, Formulas, and Internationalization in Reports
defines parameters for dynamic report generation. It helps you write formulas and use available functions for rich summary and calculated values in your reports, along with dynamically adjusting colors and styles using expressions in your report.
Chapter 8—Adding Sub-Reports and Cross Tabs to Reports gives an overview of how to
build reports that include side-by-side sub-reports and cross tabs.

Chapter 9—Building Interactive Reports teaches how to add dynamic interaction to HTML and Swing reports, for immediate feedback and dashboard-like functionality.

Chapter 10—API-based Report Generation is about building reports from XML and by using Pentaho Reporting’s Java Bean API.
Chapter 11—Extending Pentaho Reporting teaches how to write custom functions and
elements within Pentaho Reporting.

Chapter 12—Additional Pentaho Reporting Topics discovers how to use Pentaho Reporting with the Pentaho BI Server, including Pentaho Metadata. It aids in learning more about Pentaho Reporting’s open source approach, and how you can contribute to the free software movement.

Including Charts and Graphics in Reports

In this chapter, you’ll learn how to incorporate charts and graphics into Pentaho Reports. You’ll learn about the different types of charts supported, and how to configure them in Pentaho Report Designer. You’ll also learn how to populate a chart with various types of data.
In addition to learning all about charts, this chapter also covers the various methods for including visual information in your report, including embedding images and Java graphics in your report.

Supported charts

Pentaho Reporting relies on JFreeChart, an open source Java chart library, for charting visualization within reports. From within Report Designer, many chart types are supported. In the chart editor, two areas of properties appear when editing a chart. The first area of properties is related to chart rendering, and the second tabbed area of properties is related to the data that populates a chart.

Following is the screenshot of the chart editor within Pentaho Report Designer:

All chart types receive their data from three general types of datasets. The first type is known as a Category Dataset , where the dataset series and values are grouped by categories. A series is like a sub-group. If the exact category and series appear, the chart will sum the values into a single result. The following table is a simple example of a category dataset:

Category	Series	Sale Price
Store 1 Sales	Cash	$14
Store 1 Sales	Credit	$12
Store 2 Sales	Cash	$100
Store 2 Sales	Credit	$120

Pentaho Reporting builds a Category Dataset using the CategorySetDataCollector. Also available is the PivotCategorySetCollector, which pivots the category and series data. Collector classes implement Pentaho Reporting’s Function API. The second type of dataset is known as an XY Series Dataset, which is a two dimensional group of values that may be plotted in various forms. In this dataset, the series may be used to draw different lines, and so on. Here is a simple example of an XY series dataset:

Series	Cost of Goods (X)	Sale Price (Y)
Cash	10	14
Credit	11	12
Cash	92	100
Credit	105	120

Note that X is often referred to as the domain, and Y is referred to as the range.

Pentaho Reporting builds an XY Series Dataset using the XYSeriesCollector. The XYZSeriesCollector also exists for three dimensional data. The third type of dataset is known as a Time Series Dataset , which is a two dimensional group of values that are plotted based on a time and date. The Time Series Dataset is more like an XY Series than a Category Dataset, as the time scale is displayed in a linear fashion with appropriate distances between the different time references.

Pentaho Reporting builds a Time Series Dataset using the TimeSeriesCollector.

Common chart rendering properties

Most charts share a common set of properties. The following properties are common across most charts. Any exceptions are mentioned as part of the specific chart type.

Common category series rendering properties

The following properties appear in charts that render category information:

Common XY series rendering properties

The following properties appear in charts that render XY series information.

Common dataset properties

The following properties are common across all chart datasets:

Common category series dataset properties

The following properties are common across all charts that utilize category series dataset for populating the chart:

Common XY series dataset properties

The following properties are common across all charts that utilize the XY series dataset for populating a chart:

Now that you’ve reviewed the common set of properties for all charts, you’ll begin to explore the individual charts, including going through their configurable properties, as well as providing a quick example.

Area chart

The area chart displays a category dataset as a line, with the area underneath the line filled in. Multiple areas may appear depending on the number of series provided. The area chart is useful for visualizing the differences between two or more sets of data. It utilizes the common properties defined in the previous tables, including the category series common properties. The area chart defines no additional properties.

Area chart example

This example will demonstrate the area chart’s capabilities. First, you’ll need a rich enough dataset to demonstrate this and all the other charts in this chapter. You’ll reuse the ElectroBarn HSQLDB data source configured in Chapter 3. To begin, launch Pentaho Report Designer and create a new report.

Now, select the Data tab. Right-click on the Data Sets tree element, and select the JDBC data source. If ElectroBarn is not already configured as a connection type, click the Connections add image button and fill in the following values, customizing the database location for your particular environment:

Click the Test button to verify your connection, and then click OK when you are done.

You need to define a SQL statement to populate your chart. You’ll define a simple query that takes a look at the inventory data. Add a new query with the following SQL code:

	SELECT
		"INVENTORY"."ITEMCATEGORY",
		"INVENTORY"."SALEPRICE",
		"INVENTORY"."COST"
	FROM
		"INVENTORY"
	ORDER BY
		"INVENTORY"."ITEMCATEGORY" ASC

Click OK. You’re now ready to add a chart to your empty report. For this example, select the Chart report element from the palette and drag it into the Report Header. Double-click on the chart, or right-click on the chart and select Chart….

Once the Edit Chart dialog appears, select the Area Chart .

In the Primary DataSource tab, select the ITEMCATEGORY data field as your category-column. For your value-columns, select SALEPRICE and COST. Enter the strings Sale Price and Cost as the series–by-value values. When rendering an area chart, the order of value columns is important. If a larger value is rendered after a smaller value, the smaller value will not appear on the chart.

Once you configured the data for the chart, you can also make some customization to the rendering. Set horizontal to True, as well as specifying the bg-color as yellow.
Finally, set the show-legend property to True. Click the OK button and then
preview your report to see the results!

Bar chart

The bar chart displays individual bars broken out into individual categories and series. Bar charts are useful for comparing relative sizes of data across categories. The bar chart utilizes the common properties defined earlier, including the category series common properties.

The bar chart defines the following additional rendering properties:

Bar chart example

You’ll now build an example bar chart. Create a new report with the ElectroBarn data source, and use the following SQL query, which investigates purchase quantity and payment type:

	SELECT
		"INVENTORY"."ITEMCATEGORY",
		"PURCHASES"."PAYMENTTYPE",
		"PURCHASEITEMS"."QUANTITY"
	FROM
		"PURCHASES" INNER JOIN "PURCHASEITEMS" ON
			"PURCHASES"."PURCHASEID" = "PURCHASEITEMS"."PURCHASEID"
		INNER JOIN "INVENTORY" ON "PURCHASEITEMS"."ITEMID" =
			"INVENTORY"."ITEMID"
	ORDER BY
		"INVENTORY"."ITEMCATEGORY" ASC,
		"PURCHASES"."PAYMENTTYPE" ASC

Place a Chart element in the Report Header of the report, selecting bar as its type.

To begin, configure the dataset properties for your bar chart. Set category-column to ITEMCATEGORY, value-columns to QUANTITY, and series-by-field to PAYMENTTYPE. By setting the series-by-field property, the chart will create a series for each PAYMENTTYPE in the dataset.
Now, you’ll customize the look of your chart. First, set the X-Axis show-labels property to True and text-format to {2}. This will display the value of each bar at the top of the bar. Then set max-label-width to 2.0, so that you can easily see all the category names in the chart. Finally, set the show-legend to True, in order to see what types of payments map to which bar color. You’re now ready to preview your chart!

Line chart

The line chart displays connected lines between categories for each series provided. This chart is useful for visualizing trends. The line chart utilizes the common properties defined in the previous tables, including the category series common properties. The line chart defines the following additional rendering properties:

Note that the stacked and stacked-percent properties do not apply to the line chart type.

Line chart example

In this example, reuse the SQL query and dataset sections from your area chart example. Select the Line chart type , and customize the chart with show-markers set to True as well as line-size set to 4.0. The result should look like this:

Pie chart

The pie chart displays a sliced, multi-colored pie with individual slices consisting of individual series information. The pie chart uses its own dataset type, versus using a category or XY series dataset. The pie chart utilizes the common properties defined above, but does not utilize the category or XY dataset properties. Instead, it defines its own properties for providing chart data using the PieDataSetCollector. The pie chart defines the following rendering properties:

Note that the pie chart does not share the common properties horizontal, series-color, stacked, or series-names. The pie chart defines the following dataset properties:

Pie chart example

For the pie chart example, you’ll compare the various costs of inventory items to one another by category. First, you’ll need to define an SQL query as shown next:

	SELECT
		"INVENTORY"."ITEMCATEGORY",
		"INVENTORY"."ITEMNAME",
		"INVENTORY"."COST"
	FROM
		"INVENTORY"
	ORDER BY
		"INVENTORY"."ITEMCATEGORY" ASC,
		"INVENTORY"."ITEMNAME" ASC

You’ll then need to define a Group Header for your report. Right-click on the Groups section within the report structure and edit the root group, naming the group Item Category. Select the ITEMCATEGORY field as the only field in the Selected Items list. Expand the Group node in the structure tree, and select the Group Header. Now, uncheck the hide-on-canvas property, so you can view the Group Header in the canvas.

Drag-and-drop the ITEMCATEGORY field at the top of the Group Header. Place a chart below the text field, and click Edit Chart…
Select the Pie chart type . You’ll start configuring the chart by selecting the correct dataset. For the value-column, select the COST field. For the series-by-field property, select the ITEMNAME field. You’ll also need to tell the chart collector to reset the data after each group. Set the reset-group property to the already defined Item Category group.

Finally, you’ll want to customize some of the rendering properties. Set the explode-slice to maxValue, and set the explode-pct to 0.5. This will highlight the most expensive item in each category. Also set show-legend to False to hide the legend and show-labels to True to display the individual pie slice labels.

Click the OK button and preview the report. You should see a group of charts as shown in the following figure:

Ring chart

The ring chart is identical to the pie chart, except that it renders as a ring versus a complete pie. In addition to sharing all the properties similar to the pie chart, it also defines the following rendering property :

Ring chart example

For this example, simply open the defined pie chart example and select the Ring chart type. Also, set the section-depth to 0.1, in order to generate the following effect:

Multi pie chart

The multi pie chart renders a group of pie charts, based on a category dataset. This meta-chart renders individual series data as a pie chart, each broken into individual categories within the individual pie charts. The multi pie chart utilizes the common properties defined above, including the category dataset properties. In addition to the standard set of properties, it also defines the following two properties:

Note that the horizontal, series-color, stacked and stacked-percent properties do not apply to this chart type.

Multi pie chart example

This example demonstrates the distribution of purchased item types, based on payment type. To begin, create a new report. You’ll reuse the bar chart’s SQL query.

Now, place a new Chart element into the Report Header. Edit the chart, selecting Multi Pie as the chart type. To configure the dataset for this chart, select ITEMCATEGORY as the category-column. Set the value-columns property to QUANTITY and the series-by-field to PAYMENTTYPE.

Waterfall chart

The waterfall chart displays a unique stacked bar chart that spans categories. This chart is useful when comparing categories to one another. The last category in a waterfall chart normally equals the total of all the other categories to render appropriately, but this is based on the dataset, not the chart rendering. The waterfall chart utilizes the common properties defined above, including the category dataset properties. The stacked property is not available for this chart. There are no additional properties defined for the waterfall chart.

Waterfall chart example

In this example, you’ll compare by type, the quantity of items in your inventory. Normally, the last category would be used to display the total values. The chart will render the data provided with or without a summary series, so you’ll just use the example SQL query from the bar chart example. Add a Chart element to the Report Header and select Waterfall as the chart type. Set the category-column to ITEMCATEGORY, the value-columns to QUANTITY, and the series-by-value property to Quantity. Now, apply your changes and preview the results.

Bar line chart

The bar line chart combines the bar and line charts, allowing visualization of trends with categories, along with comparisons. The bar line chart is unique in that it requires two category datasets to populate the chart. The first dataset populates the bar chart, and the second dataset populates the line chart. The bar line chart utilizes the common properties defined above, including the category dataset properties.

This chart also inherits the properties from both the bar chart, as well as the line chart. This chart also has certain additional properties, which are listed in the following table:

As part of the bar line chart, a second y-axis is defined for the lines. The property group Y2-Axis is available with similar properties as the standard y-axis.

Bar line chart example

To demonstrate the bar line chart, you’ll reuse the SQL query from the area chart example. Create a new report, and add a Chart element to the Report Header. Edit the chart, and select Bar Line as the chart type.
You’ll begin by configuring the first dataset. Set the category-column to ITEMCATEGORY, the value-columns to COST, and the series-by-value property to Cost. To configure the second dataset, set the category-column to ITEMCATEGORY, the value-columns to SALEPRICE, and the series-by-value property to Sale Price.

Set the x-axis-label-width to 2.0, and reduce the x-font size to 7. Also, set show-legend to True.

You’re now ready to preview the bar line chart.

Bubble chart

The bubble chart allows you to view three dimensions of data. The first two dimensions are your traditional X and Y dimensions, also known as domain and range. The third dimension is expressed by the size of the individual bubbles rendered.

The bubble chart utilizes the common properties defined above, including the XY series dataset properties. The bubble chart also defines the following properties:

The bubble chart defines the following additional dataset property:

Bubble chart example

In this example, you need to define a three dimensional SQL query to populate the chart. You’ll use inventory information, and calculate Item Category Margin:

	SELECT
		"INVENTORY"."ITEMCATEGORY",
		"INVENTORY"."ONHAND",
		"INVENTORY"."ONORDER",
		"INVENTORY"."COST",
		"INVENTORY"."SALEPRICE",
		"INVENTORY"."SALEPRICE" - "INVENTORY"."COST" MARGIN
	FROM
		"INVENTORY"
	ORDER BY
		"INVENTORY"."ITEMCATEGORY" ASC

Now that you have a SQL query to work with, add a Chart element to the Report Header and select Bubble as the chart type. First, you’ll populate the correct dataset fields. Set the series-by-field property to ITEMCATEGORY. Now, set the X, Y, and Z value columns to ONHAND, SALEPRICE, and MARGIN.
You’re now ready to customize the chart rendering. Set the x-title to On Hand, the y-title to Sales Price, the max-bubble-size to 100, and the show-legend property to True. The final result should look like this:

Scatter chart

The scatter chart renders all items in a series as points within a chart. This chart type utilizes the common properties defined above, including the XY series dataset properties. The scatter chart also defines the following two properties:

Scatter chart example

For this example, you’ll reuse the SQL query defined in your bubble chart example, as well as the default rendering properties configured. Simply select the Scatter chart type in the chart editor . The chart below shows Sales Price and On Hand values:

XY Area, XY Bar and XY Line charts

The XY Area, XY Bar, and XY Line charts graph an XY series dataset as an area, bar, or a simple line chart. These chart types utilize the common properties defined above, including the XY series dataset properties. The XY Bar chart also uses the property show-bar-borders, which is defined earlier in the bar chart. The XY Area and XY Line charts share the properties line-style, line-size, and show-markers, defined earlier in the line chart.

In addition to the standard XY Series Dataset, XY charts may use a Time Series Dataset to render data. To use the TimeSeriesCollector, you can select it in the Primary DataSource drop-down list. The Time Series Dataset is similar to the Category Dataset, but instead of a category it defines a category-time-column. The field selected for the category-time-column must be of the type java.util.Date.

Also defined is the time-period-type, which defines at what interval of time should the results be grouped together. Valid values for this property include Millisecond, Second, Minute, Hour, Day, Week, Month, Quarter, and Year.

XY charts example

In this example, you’ll reuse the SQL query defined in the bubble chart example, as well as the default rendering properties configured for each of the individual charts, XY Area , XY Bar , and XY Line Chart . You’ll also reuse the X and Y dataset configuration specified for the scatter chart.

Extended XY Line chart

The Extended XY Line chart allows the rendering of three additional chart types—StepChart, StepAreaChart, and DifferenceChart. The Step chart types display an XY series dataset as a set of steps, and the Difference Chart renders two XY series and highlights the differences between the two. The Extended XY Line chart utilizes the common properties defined above, including the XY series dataset properties. The Extended XY Line chart also defines the following property:

Extended XY Line chart example

In this example, you’ll reuse the SQL query defined in the bubble chart example, as well as the default rendering properties configured for each of the individual charts. Select Extended XY Line as the chart type and specify StepChart, StepAreaChart and DifferenceChart as the Chart Type to see the different renderings:

You’ve now worked with all the major chart types within Pentaho Reporting. Under the covers, charts are simply dynamic images that are generated and included in your reports. You’ll now learn more about including images within reports.

Radar chart

The Radar chart renders a web-like chart that displays a categorical dataset. The Radar chart utilizes the common properties defined above, including the category series common properties. The Radar chart also defines the following properties:

Radar chart example

In this example, reuse the SQL query and dataset sections from your area chart example. The result should look like this:

Including static images in your report

To include static images in your report, select the image report element
from the report designer palette and place it in your report. Double-click on the element, or right-click on the element and select Edit Content to select the static image. This brings up a resource dialog, where you can browse to the specific file location. You may Link to or Embed the image in the PRPT file. An example of a static image, with the ElectroBarn logo, is provided in Chapter 3.
The image report element uses Pentaho Reporting’s ResourceManager API to load the image. The ResourceManager interface is located in the org.pentaho.reporting.libraries.resourceloader package.

Including dynamic images in your report

To add dynamic images to your report, use the content-field report element . The content field accepts different types of image inputs for rendering. The first approach is dynamically changing the image location within your dataset. If you have a field that contains a URL or file system location to your image, the content-field element will render the specified image.

The second approach is to populate the content-field with an object of type java.awt.
Image for rendering. This approach would require a custom-implemented TableModel (as described in Chapter 5), or a custom function that returns an Image object. The third approach is to populate the content-field with an object that contains the following method, which is determined through Java introspection:

	void draw(Graphics2D g2, Rectangle2D area);

In addition to this API, Pentaho Reporting also defines an extended org.pentaho. reporting.engine.classic.core.util.ReportDrawable API with the following methods, for more detailed access into the report rendering process:

void draw(Graphics2D g2, Rectangle2D area);
void setConfiguration(Configuration config);
void setStyleSheet(StyleSheet style);
void setResourceBundleFactory(ResourceBundleFactory
bundleFactory);
ImageMap getImageMap(final Rectangle2D bounds);

A custom TableModel implementation or custom function would also be required to make this object available to the Reporting engine.

Summary

In this chapter, you learned how to incorporate many chart types into your reports in many different ways. You learned how to configure a chart’s dataset as well as customizing how each chart type looks in a report. You learned how to populate a category series dataset, as well as an XY series dataset, and make that data available to the various types of charts that render in your report. You also learned how to include static and dynamic images, as well as graphics, in your reports.