JavaBeat

BIRT 2.6 Data Analysis and Reporting

by Leave a Comment

BIRT 2.6 Data Analysis and Reporting

BIRT is an open source business intelligence and reporting tool, built on top of the Eclipse Framework. BIRT is used by developers for building reports that can best represent data and tell a story of that data that is easy to follow. In addition, BIRT can be integrated into a product, to allow that product to provide reporting capabilities. This book will walk the user through the basics of building reports with BIRT and introduce them to the various sections of the BIRT environment.

also read:

What This Book Covers

Chapter 1, Getting Started, is an introduction to BIRT. It gives the reader an idea of the BIRT features, community landscape, and the various websites out there that are dedicated to BIRT.
Chapter 2, Installing BIRT, guides the user through the various ways of installing BIRT.
Chapter 3, The BIRT Environment and First Report, introduces the BIRT workspace environment by walking the reader through a simple report example.
Chapter 4, Visual Report Items, presents us with various visual report items that are available for use in a report design.
Chapter 5, Working with Data, discusses how to retrieve data from databases, fl at text files, web services, and other data sources.
Chapter 6, Report Parameters, explains how to get input from the report user, along with how to apply these report parameters in filtering data.
Chapter 7, Report Projects and Libraries, looks at reusing report sections and sharing resources through libraries and report projects.
Chapter 8, Charts, Hyperlinks, and Drilldowns, shows how to build summary data using charts and linking sections of charts to reports. In addition, hyper linking used in online reports to link and show detailed data in other reports is discussed here.
Chapter 9, Scripting and Event Handling, discusses report generation through event handling, as well as how to manipulate and format data using simple JavaScript expressions. Some advanced scripting examples are provided based on common requests.
Chapter 10, Deployment, looks at how does a developer can get reports out into the world for consumption.

Charts, Hyperlinks, and Drilldowns

It has been said that a picture is worth a thousand words. In the world of reporting, we call these pictures as Charts. With the help of charts, one can present a large amount of data with relative ease, in comparison to presenting with the help of huge data outputs. Charts are useful when used with raw data to drive home points such as percentages, or when used with other charts to build dashboards.
BIRT has an exceptional charting engine. BIRT can create a number of different Chart types, including the tried and tested pie chart, bar chart, and line charts. In addition, there are several other chart types that were extras to BIRT’s commercial counterpart such as the Meter Chart and Gantt chart.
In the following sections, we are going to look at building a few different charts centered around employee sales performance. First, we will build a pie chart that will illustrate the top employees based on sales. We will then create a gauge chart that will show the progress an employee has made to meet a quota. Finally, we will create a bar chart that will show sales performance for employees across a time period.

Pie chart

In the following exercise, we are going to look at how to build a pie chart. A pie chart is a very common report type used in business to display percentages. In this example, we will create a pie chart to show what percentage an employee contributed to the total amount of sales. This will give us a visual representation of who the top employees are in terms of sales.

  1. Create a new report titled Employee_Sales_Percentage.rptDesign.
  2. From the ClassicCarsLibrary.rptDesign library, add the dsClassicCarsdata source to the report.

  3. Create a new dataset called totalSales using the following query:

    4. [code]
    select
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
    CLASSICMODELS.EMPLOYEES.LASTNAME || ‘, ‘ || CLASSICMODELS.
    EMPLOYEES.FIRSTNAME name,
    sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales
    from
    CLASSICMODELS.EMPLOYEES,
    CLASSICMODELS.ORDERS,
    CLASSICMODELS.ORDERDETAILS,
    CLASSICMODELS.CUSTOMERS
    where
    CLASSICMODELS.CUSTOMERS.SALESREPEMPLOYEENUMBER =
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER
    and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.
    CUSTOMERS.CUSTOMERNUMBER
    and CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS.
    ORDERS.ORDERNUMBER
    and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
    group by
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
    CLASSICMODELS.EMPLOYEES.LASTNAME,
    CLASSICMODELS.EMPLOYEES.FIRSTNAME
    [/code]

  4. Create two report parameters called startDate and endDate as date types and link them to the two report parameters. Use 2005-01-01 as the startDate’s default value, and 2005-05-01 as the endDate’s default value.

  5. Drag a chart component from the Palette to the Report Designer.

  6. In the Chart dialog, select Pie chart. Change the Output format from SVG to PNG. Typically, iuse either PNG or JPEG, as SVG is not a universally
    supported format. However, SVG does have a distinct advantage over both PNG and JPEG in that SVG images are smaller in size due to the format’s vector nature.

  7. Open the Select Data tab. Under the Select Data section, select Use Data Set and choose the totalSales dataset.
  8. We can drag the column headers from the dataset to the slice definitions. Drag the SALES column to the Slice Size Definition and the NAME column to the Category Definition. To make selecting the correct header easier, we can use the Show data preview checkbox to see what data is in each column. Without it, the preview box will show only the column names.

  9. Select the Format Chart tab. Under the Title section, enter Employee Sales
    Percentages     as the chart title. In this editor, we change how the chart looks and feels.

  10. Click Finish.
  11. Resize the chart to take up report page. This can be done by dragging the corner of the chart or by entering the height and width values in the property editor.

  12. The chart will look something like the following screenshot when previewed:


So, we have built a fairly simple report so far. This report is a pie chart with a legend
that shows us the employee’s color code, with an excerpt that shows us the value of that slice.

Pie chart with explosions

While the pie chart we saw in the preceding section is nice, it would be a little easier
to view if the slices were exploded, and if the slices had an outline. Let’s take a look
at how to modify some of the chart’s properties to do this.

  1. Double-click on the chart in the report design. This will reopen the chart dialog. Another option is to right-click on the chart and choose Format Chart….

  2. Open the Format Chart… tab.
  3. Open the Value Series section.

  4. Under the Slice section, change the By Distance value to 6.
  5. For Slice Outline, choose the color black from the palette.

  6. Save the chart and preview it. The following screenshot shows what the chart
    looks like with the explosion and slice outline added.

Pie charts—working with percentages

Things are starting to look better. But let’s say we want to change the chart to display
percentages instead of numbers and add a little interactivity to the chart to display the sales numbers when a section is clicked on.

  1. Double-click on the chart to edit it.
  2. Select the Value Series section.
  3. Click on the Labels button.

  4. Under Values, remove the Value Data option.

  5. Under Values, add Percentile Value Data.
  6. Click the Interactivity button.
  8. Choose Mouse Click from the Event listbox.
  10. From the Action listbox, choose Invoke Script.
  12. Click the Expression Editor button.
  14. Using the editor, click on the Variables | Chart Data Point | Value Data option to insert the valueData variable into the editor. Surround it with an alert() method call. It should look like the next screenshot:
  16. Click OK. Now click on the << arrows in the Series Interactivity window.
  18. Click Finish in the Chart Editor.

When we preview the report, the chart pops up, formatted in the manner we want. If we click on one of the pie slices, an alert window will pop up showing the value for that series.

Gauge chart

With the next chart, the guage chart, we will expand on the interactivity a bit and demonstrate how drill-downs work. In addition to viewing the static chart, we want the user to be able to click on the chart and have it pull up an external report with the details for a particular user. This is called a drilldown.

The following meter chart will demonstrate an employees sales vs. a target amount per month, let’s say 3000, on a 5000 dollar scale. This will be broken out and grouped monthly in the query statement. When the user clicks on the chart, it will bring them to the detail report we created last chapter for Employee sales.

  1. Create a new report titled Employee_Sales_Guage.rptDesign.
  2. From the library, drag over the dsClassicCars data source.
  3. Create a new dataset called employeeSales using the following query:

    4. [code]
    select
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
    CLASSICMODELS.EMPLOYEES.LASTNAME || ‘, ‘ || CLASSICMODELS.
    EMPLOYEES.FIRSTNAME name,
    sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales,
    rtrim(char(year(CLASSICMODELS.ORDERS.ORDERDATE))) || ‘-‘ ||
    rtrim(char(month(CLASSICMODELS.ORDERS.ORDERDATE))) orderDate
    from
    CLASSICMODELS.EMPLOYEES,
    CLASSICMODELS.CUSTOMERS,
    CLASSICMODELS.ORDERS,
    CLASSICMODELS.ORDERDETAILS
    where
    CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.
    ORDERDETAILS.ORDERNUMBER
    and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS.
    CUSTOMERS.SALESREPEMPLOYEENUMBER
    and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.
    CUSTOMERS.CUSTOMERNUMBER
    and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
    group by
    year(CLASSICMODELS.ORDERS.ORDERDATE),
    month(CLASSICMODELS.ORDERS.ORDERDATE),
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
    CLASSICMODELS.EMPLOYEES.LASTNAME,
    CLASSICMODELS.EMPLOYEES.FIRSTNAME
    [/code]

  4. Link the two dataset parameters to the report parameters, startDate and endDate respectively.

  5. Drag the employeeSales dataset to the report designer.

  6. Delete the last two columns from the table.

  7. Create a group called GroupByEmployee in the table and set it to group on the EMPLOYEENUMBER.


  8. Create another group below GroupByEmployee and call it groupByDate. Set it to group on the ORDERDATE field.

  9. Merge all of the cells in the orderDate details row into one large cell. Be sure to delete any data items that are still present such as the NAME and EMPLOYEENUMBER items.

  10. In the new large cell, create a chart element.

  11. Select Meter as the type, and select SuperImpose Meter as the chart type. If we need to know which type of chart we are picking, hover the mouse over the chart for a second and its description will pop up. Set the Output Format
    to PNG.

  12. In the Select Data tab, create another dial by selecting the Meter Value Definition drop-down list and selecting <New Series..>.

  13. For the expression for Dial 1, select row[“SALES”] by either dragging the column over from the Data Preview pane or by using the Expression Editor.
  14. The value for Dial 2 should be set to 3000.

  15. As Category Definition, use row[“NAME”] by either dragging the column over from the Data Preview section or using the Expression Editor.

  16. In the Format Chart tab, under the Value Series section, click the Scale button.

  17. Enter 0 for the Min value and 5000 for the Max value. We have now set up the scale.

  18. On the Edit Chart tab, under the Legend section, uncheck the Visible checkbox.

  19. Select Chart Area.

  20. Click the Interactivity button.

  21. Select Mouse Click from the Event listbox and select Hyperlink from the Action listbox.

  22. Click the Add Button.

  23. In the Name field, insert NewHyperlink.

  24. Click the Edit Base URL… button.
  25. Select Drill-through as the hyperlink type.

  26. For the Report Design, navigate and select the report Employee Sales Details Report.rptDesign. This report was created in the preceding chapter.
  27. Select the startDate and endDate parameters under the Parameters drop down, which selects the parameters in the target report. Under Values, enter params[“startDate”].value and params[“endDate”].value, or whatever names that have been used used for the current report’s startDate and endDate parameters.

  28. Under Step 2, select Target Bookmark. From the drop-down list, select row[“EmployeeNumber”].
  29. Under Step 4, select New Window.
  30. We can skip Step 5 as we are sticking to our default output format, which is HTML. We could have the target report open in any format that we have an emitter registered for. Emitters are plugins for BIRT that produce output in different formats such as HTML, PDF, Microsoft Excel, or Microsoft Word.

  31. Click OK to exit the dialog and save the report.
  32. Delete the table header.

  33. Delete the EMPLOYEENUMBER data element.

  34. Drag the EMPLOYEENUMBER column from the Data Explorer, next to the OrderDate report item.

  35. Now run the report. When we click on any of the charts, a detailed report will open and the control will automatically jump to the employees section, as specified in the Bookmark in the target report.

Of course, this example can be heavily modified to simplify things. The charts could be consolidated into single chart, with more needles for each month instead of separate charts for each month, by moving the chart into the EmployeeNumber group’s header or footer row and adding a group by date in the chart editors dialog under the Select Data tab. It is also possible to make the needles interactive and to filter down to a specific user and date range by modifying the bookmarks in the target report, but I will leave that up to the reader to discover. The following is a modified version of the the report that has created different series in the chart itself for the date groupings.

Bar chart

In the earlier section, we saw some of the power that charting can have with reports,by adding both the graphical representation of data and the interactivity for users to see the details of that data in a separate report. We also saw how to pass data through to the target report by using the expression. In this report, the hyperlink will take us to an internal bookmark, which means it will jump us to a location inside the same report containing the details for our report. We will also look at aggregating values inside the chart so that we can provide a simple query that just retrieves the necessary details and lets BIRT handle the tricky stuff for us.

  1. Create a new report called EmployeeSalesPerformanceReport.rptDesign.
  2. Use the dsClassicCars data source in the library.
  3. Create a new dataset called employeeSales, using the following query:

    4. [code]
    select
    CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER,
    CLASSICMODELS.EMPLOYEES.LASTNAME || ‘, ‘ || CLASSICMODELS.
    EMPLOYEES.FIRSTNAME name,
    CLASSICMODELS.ORDERDETAILS.PRICEEACH,
    CLASSICMODELS.ORDERS.ORDERDATE,
    CLASSICMODELS.PRODUCTS.PRODUCTNAME
    from
    CLASSICMODELS.EMPLOYEES,
    CLASSICMODELS.CUSTOMERS,
    CLASSICMODELS.ORDERS,
    CLASSICMODELS.ORDERDETAILS,
    CLASSICMODELS.PRODUCTS
    where
    CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.
    ORDERDETAILS.ORDERNUMBER
    and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS.
    CUSTOMERS.SALESREPEMPLOYEENUMBER
    and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.
    CUSTOMERS.CUSTOMERNUMBER
    and CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.
    ORDERDETAILS.PRODUCTCODE
    and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
    [/code]

  4. Map the dataset parameters to report parameters startDate and endDate.

  5. Drag a table element over from the palette.

  6. Set Number of columns to 3 and Number of details to 1, and map the
    dataset element to the employeeSales dataset.

  7. In the header row, insert a new row.

  8. In the top most header, merge all the cells into one large cell.

  9. Insert a chart element into the large cell.

  10. Select Bar chart as the type.
  11. Select the stacked Bar Chart as the subtype.
  12. Set the Output Format to PNG.

  13. Go to the Select Data tab.

  14. For the series value, drag in the PRICEEACH column.

  15. For the Category (x) Series, use the following expression:

    16. [code]
    (row["ORDERDATE"].getYear() + 1900).toString() + ‘-‘ +
    (row["ORDERDATE"].getMonth() + 1).toString()
    [/code]

    This may not make much sense now, but we will explain it further in the next chapter.

  16. For the Optional Y Series Grouping, drag in the NAME column.

  17. In the Inherit Data From Container drop-down list, select Inherit Columns only. This will set up the chart to ignore any grouping at the table level and let the chart engine do its down grouping.

  18. Next to the Category (x) Series expression editor button , there is a button to
    edit group and sorting. Click that button.

  19. Select Ascending from the Data Sorting drop down.
  20. In the Grouping checkbox, check the Enable option.

  21. Keep the other values at their defaults and click OK.
  22. Click Finish and save the report.
  23. Select the table, and create a group on EMPLOYEENUMBER.

  24. Create a grouping on OrderDate.
  25. For the detail row, drag over the PRODUCTNAME into the second column and PRICEEACH into the third column.

  26. From the Palette, drag over an Aggregation element into the groupFooter
    for the OrderDate grouping.

  27. Create the aggregation on the PRICEEACH field for the salesDateGroup.

  28. Add in any formatting that might spruce up the look of our report.

  29. Select the OrderDate groupings header row . Select the Bookmark tab under the property editor. Use the following expression for the bookmark:

    30. [code]
    row["EMPLOYEENUMBER"] + row["ORDERDATE"]
    [/code]

  30. Double-click on the chart to enter the Chart Editor.
  31. Select the Format Chart tab.

  32. In the Edit Chart window, select the Value (Y) Series section and click the
    Interactivity button.

  33. In the Event drop-down list select Mouse Click, whereas in the Action
    listbox select Hyperlink.

  34. Click the Add button.
  35. Enter ChartHyperlink for the Name.

  36. Click the Edit base URL… button.
  37. In the hyperlink dialog box, select Internal Bookmark as the type.

  38. Select the following from the drop down:

    39. [code]
    row["EMPLOYEENUMBER"] + row["ORDERDATE"]
    [/code]

  39. Click OK to save the report. Now run it.

Now, when we click on any of the different colored sections of the bar chart, it will bring us to the details section within our own report.

Summary

In this chapter, we have seen how to create several different chart types. We have seen how we can create interactivity within a single report and how to drill down into a separate report using charts and passing through parameters. We have seen how to aggregate data within a chart to serve as a summary for the more detailed data. We have opened the dialog to edit properties of charts and seen how a few of the properties affect the look and feel of charts as well as the behavior.
In the next chapter, we are going to explore the basic of scripting in BIRT reports.
Throughout this book we have been using a basic type of script called an expression. We will explore this in following chapters and expand into event handling as well.

Pentaho Reporting 3.5 for Java Developers

by Leave a Comment

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:

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:
[code]
SELECT
"INVENTORY"."ITEMCATEGORY",
"INVENTORY"."SALEPRICE",
"INVENTORY"."COST"
FROM
"INVENTORY"
ORDER BY
"INVENTORY"."ITEMCATEGORY" ASC
[/code]
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:
[code]
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
[/code]
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:
[code]
SELECT
"INVENTORY"."ITEMCATEGORY",
"INVENTORY"."ITEMNAME",
"INVENTORY"."COST"
FROM
"INVENTORY"
ORDER BY
"INVENTORY"."ITEMCATEGORY" ASC,
"INVENTORY"."ITEMNAME" ASC
[/code]
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:
[code]
SELECT
"INVENTORY"."ITEMCATEGORY",
"INVENTORY"."ONHAND",
"INVENTORY"."ONORDER",
"INVENTORY"."COST",
"INVENTORY"."SALEPRICE",
"INVENTORY"."SALEPRICE" – "INVENTORY"."COST" MARGIN
FROM
"INVENTORY"
ORDER BY
"INVENTORY"."ITEMCATEGORY" ASC
[/code]
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:
[code lang=”java”]
void draw(Graphics2D g2, Rectangle2D area);
[/code]
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.