01. Creating Your First Report

This section walks you through the steps needed to create your first report. You will use the sample SQL database that ships with the Pentaho Report Designer to obtain a list of products. Based on this data you will build a catalogue of products for the Steel-Wheels company.

The Report

Concept: Audience and content

Steel-Wheels is a wholesale trading company that sells model toys.

A simple catalogue of products the "steel-wheels" company offers. The catalogue is aimed at wholesalers who need a quick overview over the product lines and the available products within each line, along with a description, the prices and the availability of the product.

To make it easier for our customers to find the products within the catalogue, the items should be grouped by product line and sorted by their name.

Data: Acquiring the Report Data

The sample reports that ship with Pentaho Reporting use the "steel-wheels" dataset. This dataset is a simplified sample dataset describing a model toy company. We can get all the data for this report from a single database table. The "PRODUCTS" table contains everything we need for this report.

The data for the report can be obtained with a SQL query.

We can get all the information we need with the SQL query

SELECT
     PRODUCTS.PRODUCTLINE,
     PRODUCTS.PRODUCTVENDOR,
     PRODUCTS.PRODUCTCODE,
     PRODUCTS.PRODUCTNAME,
     PRODUCTS.PRODUCTSCALE,
     PRODUCTS.PRODUCTDESCRIPTION,
     PRODUCTS.QUANTITYINSTOCK,
     PRODUCTS.BUYPRICE,
     PRODUCTS.MSRP
FROM
     PRODUCTS

 
The result table looks like this:

The conceptual requirements prescribed that the data must be sorted by the Product Line and the Product Name. The Pentaho Reporting System does not sort data itself. Instead, it relies on the databases to provide suitably sorted results. Databases are highly optimized products that can employ advanced techniques like indexes and smart sort algorithms to produce sorted results as fast as possible.

To tell the database that the results must be sorted, SQL offers the "ORDER BY" clause. The final query will look like this:

SELECT
     PRODUCTS.PRODUCTLINE,
     PRODUCTS.PRODUCTVENDOR,
     PRODUCTS.PRODUCTCODE,
     PRODUCTS.PRODUCTNAME,
     PRODUCTS.PRODUCTSCALE,
     PRODUCTS.PRODUCTDESCRIPTION,
     PRODUCTS.QUANTITYINSTOCK,
     PRODUCTS.BUYPRICE,
     PRODUCTS.MSRP
FROM
     PRODUCTS
ORDER BY
     PRODUCTLINE ASC,
     PRODUCTVENDOR ASC,
     PRODUCTCODE ASC

Designing the report

The report must list the product properties (name, code, scale, description, stock and prices). The listing should be broken down by the product line, so that for instance, first all Cars, then all Planes and then the Ships are listed under one heading. The catalogue also needs a cover page and a back page with ordering informations.

The Report Definition

What bands exist, in what order are they printed

The report definition is what you compose in the main area of the graphical Report Designer. This definition provides the reporting engine with the location of each field on the report. The report definition consist of several sections called bands, such bands may include a report header, a page header, the item band and others.
The following image represents a schematic report as it may appear on the page:

The standard bands available in all reports are marked with different colours in the following image.

• Blue: the page header band. This band is usually printed at the top of each page
• Turquoise: the report header band. This band is printed once when the report starts
• Green: the first group's header. The header is printed whenever the groups value changes, when compared to the previous row
• Orange: the second group's header. The header is printed whenever the groups value changes, when compared to the previous row
• Red: The details header. That band is always printed before any detail band will be printed.
• Grey: The item band. This band is printed for each row available in the data table
• Dark Red: The Details Footer. This is printed after the detail bands are printed.
• Dark-Orange: The second group's footer.
• Dark-Green: The first Group's header.
• Turquoise: the report footer band. This band is printed once at the end of the report
• Dark blue: the page footer band. This band is printed once at the bottom of each page

Inside the Report Designer, the editor shows a template version of the bands. When the report runs, the data is filled in. Note that the bands in the Report Designer have a label on the left side describing what this band is used for.

If you adjusted a band to be very small, it may be possible that you cannot read the descriptive label any more. In this case, hover over the label and a tool tip displays the text.

There can also be multiple group header and group footer bands. Group header are printed from the outer most group to the inner most group. Group footer are printed from the inner most group's footer to the outer most group footer.

Report Designer Start up

The Pentaho Report Designer Version Checker

Pentaho Report Designer includes a version checker that prompts you when new versions of Report Designer are available. When performing a version check, the server makes an HTTP call to Pentaho Report Designer and exchanges information. This information is used to determine if an update is available and to analyse Report Designer usage in different environments. The version checker helps Pentaho to plan and prioritize platform certifications, testing plans, and localization plans.

Note: Version checker is available for all components of the Pentaho BI Platform.

When you first install the Report Designer, the following dialogue box appears prompting you to enable the version checker:

If you enable the version checker, you receive periodic update notices as shown below. These notices appear when there is a major General Availability (GA) release. Click the version you want, then click Update to begin downloading.

You can enable automatic version checker in Report Designer if you did not enable it during installation. Go to Edit -> Preferences and click Enable Version Checker:

By enabling the Notify me when (unsupported) development releases are available option, you receive update notification when there are development releases. These too will be in the list of available downloads. Keep in mind, however, that unsupported development releases may not have the full functionality and features available in General Availability releases.

If you opt out of the version checker, you can still check for updates manually. In Report Designer, go to Help -> Check for Updates:

Welcome Page

The Report Designer displays a Welcome page at start up. The Welcome page provides you with the option to start creating a new report with the Report Design Wizard or to start with an empty report. The Welcome page also provides access to sample reports and recently opened reports. Click New Report to open the report workspace.

The report designer workspace

In the centre of the workspace a large white area represents the main working area where you build a report. On the left side of the Report Designer workspace is the Palette that lists the graphical elements of the report. On the right side of the workspace, the Structure panel allows you to see a hierarchical representation of the report. The Properties panel allows you to adjust the settings of the currently available selection. In the lower portion of the workspace, the Messages panel displays useful information, hints, warnings, and errors that apply to your current report definition.

Creating a New Report

In this tutorial, we create a new report from scratch without using the Report Design Wizard. After you selected New Report from the Welcome Pane or "File->New", you will see a new report tab opening up. The workspace is divided into several bands as shown in the following picture.

The Structure Panel on the right hand side provides you a view over the visual elements of a report in the "design" tab and a data-centric view in the Data-tab. The data tab allows you to define where the report data comes from and how this data is processed during the report processing.

The Structure-view allows you to define how your report will look like and how your data is printed.

Each report needs data to print, so lets start with adding a data source.

Adding a Data Source

A report generally consists of data supplied by a data source in the form of a table and a report definition that defines how the report must be formatted or printed. The columns can by identified by an unique column name with a certain data type.

Pentaho Reporting comes with a wide variety of data sources. During the preparation phase, we decided to use a SQL database to read the data from.

Select the Data tab in the right hand structural pane.

Select the "Add Data Sources" button in the toolbar, or right click with your mouse on the data-sources node to open up the context menu.

Select the "JDBC" entry.

The JDBC data source editor opens up.

Select the "SampleData (Memory)" connection.

Each data source can hold multiple queries per connection. The connection is shared between all queries. Most data sources use named queries, which allow you to assign a symbolic name for each query. These names are easier to handle than the potentially complex query strings.

Create a new query click on the green plus in the upper right corner next to the "Available Queries" box. A new query named "Default 1" gets added to the list of available queries.

Select this query-name and enter the SQL query into the Query Box:

SELECT
     PRODUCTS.PRODUCTLINE,
     PRODUCTS.PRODUCTVENDOR,
     PRODUCTS.PRODUCTCODE,
     PRODUCTS.PRODUCTNAME,
     PRODUCTS.PRODUCTSCALE,
     PRODUCTS.PRODUCTDESCRIPTION,
     PRODUCTS.QUANTITYINSTOCK,
     PRODUCTS.BUYPRICE,
     PRODUCTS.MSRP
FROM
     PRODUCTS
ORDER BY
     PRODUCTLINE ASC,
     PRODUCTVENDOR ASC,
     PRODUCTCODE ASC

Click Preview to see what data gets returned. You should see this dialog:

Now that we have some data, lets add some fields to the report layout.

Saving the report

It is always a good idea to save your work regularly. To save your report, use "File -> Save". In the following file selector, choose a suitable location and enter a file name for your report.

Add Detail fields

When you add your first data source with a query to a report, the report designer automatically selects the first query in that data-source as the main query. Your data-tab now should look like this:

When generating a report, the reporting engine will print all elements of the detail band with the data from all rows. So the reporting engine prints as many detail bands as there are rows in the data-source's result table.

Our result table contains one item per row, so placing all item information into the detail band will be ideal for the catalogue.

Add report elements to the detail band. For that, drag the field "PRODUCTNAME" from the data-source tab into the details area. Place it on the upper left corner of the area.
Next drag the fields "PRODUCTSCALE", QUANTITYINSTOCK, BUYPRICE and MSRP into the details area and align all elements next to each other.

When you have to move elements around, select them first with a single mouse click. You will see a blue outline with eight handles around the element. Click inside the element and drag the element to its new position. You can drag one of the handles to increase or decrease the size of the element.

Your report designer should look like this now:

It is now time to preview the report. During the preview the reporting engine generate a full report for you. This way you can see how your elements look with real data filled in when the report renders.

To enter the preview mode, click on the "Preview" icon in the lower tool-bar.

Your report should now look similar to this image:

To return to the design mode click on the "Design Mode" icon. This icon is at the same spot as the preview icon.

Add Page Footer fields

With the limited sample data available, the catalogue only takes 3 pages to print. When the catalogue is larger, your readers will have a hard time navigating through the many pages. So let's help them by putting page-numbers into the bottom area of the page.

Each page of a report contains two special areas. At the top of every page, you will find the page-header area. The bottom of the page is occupied by the page footer area. The remaining space is available for the actual report content.

The reporting engine generates Page numbers using a report function. Switch to the data-tab and select the "Add Functions" icon.

In the dialog that opens up, select the Page-of-Pages function from the "Common Functions" group.

Once you selected that function and click on the OK button, the function will be added to the report.

Functions behave like fields from a data source. Drag the function into the report's page-footer.

The preview now shows you the report with the current page and the total number of pages in the page-footer.

Add Group

One of the requirements for this report was, that the items of the catalogue should be grouped by their product line.

Grouping is a great way to divide long lists of data along meaningful separators. With groups you can keep similar items together and allows you to visually separate these items from other groups of items. You will also need groups to perform various aggregations over the data, like printing the number of items in a group or calculating sums or averages.

Add a group by switching to the "structure view". The structure view shows you the visual elements of your report. In the tree, you see the report-header and footer as well as a default group that contains the details band and details header and footer.

Select the "group" node and click on the "Edit Groups" icon in the tool-bar.

Select the "Add Group .." option in the following popup.

In the dialog that opens up, enter the group name "Product Line Group". Then select the "PRODUCTLINE" field and add it to the list of group fields by clicking the yellow side-ways pointing arrow between the lists.

After pressing OK you will see the group in the report structure view.

Add a static label to your report by dragging the Label icon from the palette on the left side of the Report Designer into the group header.

You can now edit the label text by double clicking into the label. The label in the group header changes into a editor. Click on the ".." button to open a window where you can edit your text to "Product Line:".

Next switch to the data-tab on the right hand side and drag the "PRODUCTLINE" field into the group header.

Preview your report to see how the group heading divides the list of items. This picture shows the second page of the resulting report.

Formatting Elements

The report does not look very appealing at this stage. So let's add a touch of colour and lets make the heading stand out more.

Pentaho Reporting offers a large number of styling options for your elements. Most of these styling options are similar to what you can find in a text processing application like OpenOffice.

You can change the font size and font styles like "bold" or "italics". You can also alter the text and background colour of elements.

To format a element, you must select the element either in the design view or in the structure tree. Root level bands like the "Group Header" or the "Details band" can only be selected in the structure tree.

Pentaho Reporting offers three ways to change the font style or text colour. You can use the Format menu (Format -> Font). This opens a new dialog that offers a large set of options to alter the appearance of your report elements.

The most widely used options are also available via the lower tool-bar.

And finally: The style table in the below the structure tree on the right hand side of the window offers power users access to all existing style and attribute settings a element offers.

Select the label and the field in the header and increase the font size to 16 and make them bold by clicking on the bold button in the tool-bar. When you apply the formatting, you will notice that the text product line element is no longer fully displayed. In Pentaho Reporting, the width of an element is always fixed. To make sure that all your report elements print correctly, you must give them enough space.

Therefore increase the width of the "PRODUCTLINE" element by selecting only this element. Clear the selection by clicking outside of box of the selected elements. Then click once on the PRODUCTLINE element to select it again. You can now drag the right hand border of the element to the right to increase the width of the element.

It is always a good idea to make the start of a new group more obvious. This helps the reader of the report to scan the contents of the report faster. Add extra space for each group by giving more space to the group header. Move the two elements more downwards. Alternatively you can edit the "y" position in the style table.

The second page of the report preview should now look similar to this picture.

Giving your bands a different background colour can also aid your readers in comprehending your report better. Give your group header a background colour so that your readers can tell the start of the group.

Your next preview should show you this report on the second page:

Add Summary Functions

Useful reports are more than just plain lists of data. With the help of summary functions, you can give your reader aggregated information, which helps them to gain a quick overview over the data in the report. With summary functions you can count items, calculate sums, averages and minimum or maximum values. If you choose to not print any detail information by leaving the details section empty, you can condense thousands of rows of data into a single page.

For this example report, we are going to add a simple count of items to the footer of the group.

Pentaho Reporting provides two ways of generating summary information.

The simple mode allows you to print the summary information, but does not allow you to use that information in other formulas or functions. It is great for quickly printing values without cluttering your report with loads of functions.

The complex mode requires you to add a named function to the report (similar to what you did for the Page-of-Pages functionality of the Page-Footer). You can then print the function's result or you can use the calculated function result in other calculations.

For now, let us concentrate on the simple method and lets add a count of items to the group footer.

Drag a label and a number field into the group-footer. Select the label and click on it again to enter the edit-mode. Type the text "Number of Items:". Then select the number field, and set "aggregation-type" property to "Count".

Select preview to see the result.