Getting started with Report Designer

Build and publish a basic report with Pentaho Report Designer.

In this guide

• About Report Designer

• Create a report with Report Designer

• Design your report

• Refine your report

• More about row banding, data formatting, and alignment

• Add a chart to your report

• Add parameters to your report

• Publish your report

About Report Designer

Pentaho Report Designer is a sophisticated report creation tool that you can use standalone, or as part of the larger Pentaho Business Analytics suite. It enables professionals to create highly detailed reports based on adequately prepared data from virtually any data source. The purpose of this document is to provide instructions for creating a simple Report Designer report.

This section contains walk-throughs for creating content in Report Designer. You must have Pentaho's sample database installed and available in order to follow the tutorials. Sample data is installed by default with Report Designer, though you or your system administrator may have removed it prior to production deployment. If it is been removed, you can simply reinstall Report Designer to get it back.

Create a report with Report Designer

Perform the steps below to create a report using Report Designer:

1. Start Report Designer. Go to Start > Programs > Pentaho Enterprise Edition > Design Tools > Report Designer.

   The Report Designer home page appears.

   Note: If you would like to change the size of the layout bands to give yourself more area to work in without changing the dimensions of the published report, you can click and drag the percentage number in the upper left corner of the workspace. The default is 100%, but if you click and drag it diagonally toward the upper left or lower right corners, the view will zoom in or out. If you want to reset the view to 100%, double-click the upper left corner where the percentage shows.

2. Click New Report in the Welcome dialog box.

   The design workspace appears.

3. In the right pane, click the Data tab.

4. For the purpose of this exercise, right-click Data Sets and choose JDBC. Alternatively, you can click the yellow database icon to display the JDBC Data Source dialog box.

   The JDBC Data Source dialog box appears.

5. Under Connections, select SampleData (Hypersonic).

6. Next to Available Queries, click the Plus Sign to add queries.

   

   JDBC Data Source dialog box

   Query 1 appears under Available Queries and the edit (pencil) icon is now available, as shown below.

7. Click the editing icon ().

   The SQL Query Designer window opens. The SQL Query Designer provides you with a graphical environment which allows you to work with the data even if you do not understand SQL, the standard programming language for retrieving content from databases.

8. Select PUBLIC in the schema filter menu, and then double-click ORDERFACT so that the table appears in the workspace, as shown below.

   

   SQL Query Designer
9. In the SQL Query Designer workspace, right-click ORDERFACT and choose deselect all, as shown below.

   

   Clear all, SQL Query Designer
10. Now, select the ORDERNUMBER, QUANTITYORDERED, PRICEEACH, and ORDERDATE fields in the ORDERFACT table, as shown below.

    

    Orderfact fields
11. Double-click PRODUCTS so that the table also appears in the workspace.

    Notice the line joining the ORDERFACT and PRODUCTS tables together.

12. Clear all the PRODUCTS table fields, and then select just the PRODUCTNAME and PRODUCTLINE fields, as shown below.

    

    Products table
13. For the purpose of this exercise, click the Syntax tab in the lower left portion of the SQL Query Designer workspace to display a simple SQL statement associated with the tables.

    Notice that PRODUCTCODE is the common field between the ORDERFACT and PRODUCTS tables, as highlighted below:

    

    Syntax tab
14. Click OK in the syntax window to return to the JDBC Data Source dialog box.

    The SQL statement appears on the right under Query.

15. Click OK in the JDBC Data Source dialog box.

    

    Query 1 fields

    The fields associated with your tables are now under Query 1, as shown below:

You are now ready to start designing your report.

Design your report

This exercise walks you through the process of designing the look-and-feel of your report.

1. Under the View item in the Report Designer menu bar, click Element Alignment Hints and Snap to Elements to enable them.

   These options help you to align the elements of your report.

2. In the Design page, under Query 1, click and drag the ORDERNUMBER field into the Details band, ensuring that the top line of the field name and the top line of the Details band match up.

3. Place the ORDERDATE, PRODUCTNAME, QUANTITYORDERED, and PRICEEACH fields into the Details band.

   Take care not to overlap the fields or your report will not display correctly.

4. Use the resizing handles to make the PRODUCTNAME field larger and the QUANTITYORDERED field smaller, as shown in the following example:

   

   Resizing
5. Click (Preview) or select it from the View menu option to preview your report, as shown below:

   

   Report preview
6. Click (Edit) to return to the workspace view.

You have created your first report. But, wait... There's a problem. Without headers, report users will have a hard time understanding its content. You must continue refining your report, see Refine your report.

Refine your report

You have created a report in the previous exercise but now you need to make the report more descriptive so that users can understand the content in the report. Follow these instructions to refine your report.

1. Click and drag a label () from the tools palette in the left panel to the Page Header band.

   The Structure tab updates, as shown in the following figure:

   

   Structure tab
2. Click inside the Label item and type Order Report

3. Double-click inside the Order Report label to select the text, then select a larger font size (18 point) and apply boldface, as shown below.

   

   Font resizing

   The changes are applied to the text. However, now that the text is bigger you may not see all of it, so use your resizing handles and enlarge the label until you can see all of the text. Alternatively, you can stretch the resizing handles all the way to each edge of the workspace and click the align center icon in the toolbar so that the text is automatically placed in the center of the report page.

4. With the Order Report label still selected, click down arrow of the font color icon in the toolbar. Select a color for your label.

   The font color changes. This page header will appear on every page of your report.

5. Now, create column headers by clicking the Details Header icon under the Structure tab in the right panel.

   The Style and Attributes tabs appear under the Structure tab.

6. Under common in the Attributes tab, change the Value of the hide-on-canvas option to False.

   The Details Header band appears in your workspace.

7. In the toolbar, click the Select Objects icon () .

   Notice that the icon changes to a cross hair as you move into the workspace.

8. Move your mouse to the far right of the Details pane. Now, drag your mouse to the far left, over all your column objects to select them, as shown in the following example:

   

   Selected objects
9. Click CTRL C to copy your objects and CTRL V to paste them into the Details Header pane.

   Note: Alternatively, you can choose Copy from the right-click menu.

10. Under Format in the Report Designer menu bar, select Morph > label.

    The column objects are changed to labels.

11. Type the correct heading names for each of your columns: Order No., Order Date, Product Name, Quan., and Price Each.

    Your headers will align correctly over your columns.

12. Click the Preview icon () to display your report.

    The following image shows an example of how the report might appear. The report looks good but you may want to make it even easier to read by applying some banding.

    

    Report example
13. In the toolbar, go to Format > Row Banding.

14. In the Row Banding dialog box, choose Yellow from the drop-down list next to Visible Color and click OK.

15. Click the Preview icon () to display your report.

    The following figure shows an example of how the report might appear:

    

    Report with row banding
16. In the menu bar, go to File > Save to save your report in the .../report-designer/samples folder. Enter Orders into the File Name text box.

    Note: See More about row banding, data formatting, and alignment for additional information about refining your report.

Now that your report is easier to read, you can consider adding a chart.

More about row banding, data formatting, and alignment

Row banding

By creating a row band element, you can select the specific fields in your report that will display a row band. For example, you may want to emphasize specific fields and not others on a line. You can give your row band element any name you choose. In the example below, the row band Element is called row-band-element.

Row Banding dialog box

After you create your element, go back to the report and select the columns (fields) whose data will always be displayed with a row band. You must also type row-band-element in the name field under Attributes. In the example below, the data associated with each of the columns in the report will display a row band. Notice the banding in the report preview.

Name field, Attributes

Data formatting

Report Designer uses default formats for dates and numbers. You can change how dates and numbers display by selecting the object (field) and selecting the appropriate value for the format from the drop-down list next to format (under Attributes). In the example below, the dates associated with the Order Date field will display as MM-dd-yy.

Format field, Attributes

When you preview the report, notice that it displays in a cleaner format:

Order Date formatting applied

Note: You can type a value for your own format if you know the correct JavaScript string nomenclature.

Alignment

To align multiple objects press SHIFT CLICK to select each object. Then, choose an alignment option from the Format menu. Alternatively, you can click the Select Objects icon () and drag your mouse over the objects you want to select and then choose an alignment option.

In the example below, the selected objects will be aligned in the middle of the band.

Alignment selection, Format menu

Add a chart to your report

In the previous exercise, you have seen a small subset of features associated with Report Designer. In this exercise, you will add a chart to your report.

1. Click File > Open and find the report you just saved. Click OK.

2. In the Palette, click and drag a Chart icon () into the Report Footer band.

3. Use the resizing handles to center and stretch the chart. You can also adjust the width of the band, as shown in the following example:

   

   Chart resizing handles
4. Double-click the sample chart.

5. The type of chart most appropriate for your report is a pie chart. In the top portion of the Edit Chart dialog box, select the pie chart icon.

   The pie chart properties that define its look and feel are listed in the left pane of the Edit Chart dialog box. Properties associated with the data in the chart are listed in the right pane.

6. Go to the Title properties. Next to the chart-title property, enter Product Pie Chart.

7. Under Common in the Primary DataSource tab, click the down arrow next to value-column then click the Ellipsis button to open the Select Field dialog box.

   The value-column specifies the actual values (measures) you want to chart. In this instance, you want to chart the quantity ordered, as shown in the following figure:

   

   Select field
8. Select QUANTITYORDERED from the list and click OK.

9. Under Series, click the Ellipsis button next to series-by-field to open the Edit Array dialog box.

   Series-by-field specifies the field you are charting. In this instance you are charting by product line. Series are usually placed in the chart legend.

10. Click the Add icon ().

11. Click in the blank field to expose the drop-down arrow.

12. Select PRODUCTLINE from the list and click OK.

13. Click OK to exit the Edit Chart dialog box.

14. Click the Preview icon () to display your report.

15. When the report displays, go to end of the last page to see the following example:

    

    Displayed report
16. Save your report.

    If you want to display a bar or line chart instead of a pie chart, use the chart settings, as shown in the following image:

    

    Chart settings

    You have to add the series-by-value setting manually in the Edit Array dialog box. Click the Add icon and type SALES, and then click the Add icon again and type COST. Running the report displays the bar chart, as shown in the following example:

    

    Bar chart

    You are now ready to add a parameter to your report.

Add parameters to your report

Previously, you added a table and a chart to your report. Now, you will make your report interactive by setting parameters. When you set parameters, users are prompted for a value or values when they run the report.

1. In the Report Designer, if it is not already open, click File > Open and select to open your Orders report.

2. In the menu bar, go to Data > Add Parameter. Alternatively, you can click Master Report Parameter () under the Data Tab in the Report Designer workspace.

   The Add Parameter dialog box appears.

3. In the Add Parameter dialog box, enter enter_prodline in the Name text field.

4. Enter Select Line in the Label text field.

5. Next to Display Type, select Drop Down so users can select a product line.

6. Click on JDBC (SampleData (Hypersonic) under DataSources, and then click Edit icon () to add a query that supplies the values (motorcycles, cars, ships, and so on) from which users of the report must choose.

   The JDBC Data Source dialog box appears.

7. Under Connections, select SampleData (Memory).

8. Next to Available Queries click Add icon ().

   A new query placeholder is added to the list (Query 2).

9. In the Query Name text field, enter prodlineList.

10. Enter your SQL query in the Query box. Either copy and paste the following SQL statements directly under Query in the Static Query tab:

    SELECT DISTINCT
         "PRODUCTS"."PRODUCTLINE"
    FROM
         "PRODUCTS"

    By entering these lines, report users see a prompt when they open the report in the Pentaho User Console that allows users to enter a product line. That way, users can examine orders by product line. If you do not add the lines, the report displays orders for all product lines.

    Or, use the SQL Query Designer to build your query as shown in the following steps:

    1. Click Edit icon () to the upper right of the State Query tab.

    2. In the schema filter menu of the SQL Query Designer, select PUBLIC.

    3. Double-click the PRODUCTS table to select it.

    4. In the right panel, click PRODUCTS and choose Deselect All.

    5. Right-click SELECT in the upper left panel and choose Distinct.

    6. In the right panel, select PRODUCTLINE.

    7. Click OK to exit the SQL Query Designer and go to the next step.

11. Click OK to exit Data Source dialog box.

12. In the Add Parameter dialog box under DataSources, select prodlineList.

13. Next to Value Type, select String.

14. Optionally, type a default value (for example, Motorcycles) in the Default Value text box, as shown in the following example:

    

    Add Parameter dialog box
15. Click OK to exit the Add Parameter dialog box.

16. Now that you have created a product line parameter, you must map it back to your query (Query 1). Under Data, double-click Query 1, to open Query 1 in the JDBC Data Source dialog box.

17. Click Edit icon () to the upper right of the State Query tab to access the SQL Query Designer, right-click PRODUCTLINE in the right panel, and then select add where condition.

    The condition.edit dialog box appears.

18. Type ${enter_prodline} into the edit area in the lower panel of the dialog box as shown in the following example, and then click OK:

    

    Condition.edit dialog box
19. Click OK to exit the SQL Query Designer.

20. Click OK to exit the Data Source dialog box.

21. Click the Preview button () and notice the new product line menu, as shown below:

    

    Product line menu
22. Save and close the report.

You are now ready to publish your report.

Publish your report

You have created and formatted a simple report, added a chart, and added a parameter. Now you are ready to share the report with your users.

1. In Report Designer, click File > Open to open the report you just created.

2. Click File > Publish. Alternatively, click the Publishes the report on a Pentaho server icon ().

   If you have not saved the report, a warning message reminds you to save it. The Login dialog box appears, pre-populated with credentials valid for the evaluation. Make sure that the server's URL is set to http://localhost:8080/pentaho/ as shown below.

   

   Login dialog box
3. Click OK.

   The Publish to Server dialog box appears.

4. In the dialog box, type in a report title and description into the appropriate fields.

5. Under Location, save the report in the ...public/Steel Wheels folder.

6. Select html as the Output Type and click OK.

   A success message appears.

7. Click Yes to go directly to the User Console to view the report you just published.

   If you want to access the report later, log on to the Pentaho Server by going to http://localhost:8080/pentaho/ in a web browser, then navigate to the Reporting Examples directory in the Solution Browser.

   You should see your published report in the list. If not, click Tools > Refresh Repository.

8. Log on as Admin.

   The default admin password is password.

   Your report displays in the User Console, as shown below:

   

   Displayed report
9. In the User Console, select your product line parameter from the drop-down list. Accept the default under Output Type.​

You now have a report that users can view at any time.