Getting started with Report Designer

Use this topic to create and refine a sample report in Report Designer.

You use Pentaho’s sample database in these steps. Sample data is installed by default with Report Designer.

In this topic

• About Pentaho Report Designer

• Create a report with Report Designer

• Design your report

• Refine your report

• Row banding, data formatting, and alignment

• Add a chart to your report

• Add parameters to your report

• Publish your report

About Pentaho Report Designer

Pentaho Report Designer is a report creation tool. You can use it standalone or as part of the Pentaho Business Analytics suite.

It helps you build detailed reports from prepared data. You can connect to most data sources.

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.

   To change the zoom level, drag the percentage in the upper-left corner. Double-click it to reset to 100%.

2. Click New Report in the Welcome dialog box.

   The design workspace appears.

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

4. Right-click Data Sets and select JDBC.

   You can also click the yellow database icon.

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.

7. Click the edit icon ().

   The SQL Query Designer window opens.

8. Select PUBLIC in the schema filter menu. Double-click ORDERFACT so the table appears in the workspace.

   

   SQL Query Designer
9. In the SQL Query Designer workspace, right-click ORDERFACT and select deselect all.

   

   Clear all, SQL Query Designer
10. Select the ORDERNUMBER, QUANTITYORDERED, PRICEEACH, and ORDERDATE fields.

    

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

    Notice the line joining the ORDERFACT and PRODUCTS tables.

12. Clear all PRODUCTS fields. Then select PRODUCTNAME and PRODUCTLINE.

    

    Products table
13. Click the Syntax tab to view the SQL statement.

    Notice that PRODUCTCODE joins the tables.

    

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

    The SQL statement appears under Query.

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

    

    Query 1 fields

    The fields now appear under Query 1.

Next: Design your report.

Design your report

This exercise walks you through designing your report layout.

1. Under View, select Element Alignment Hints and Snap to Elements.

   These options help align elements.

2. Under Query 1, drag ORDERNUMBER into the Details band.

3. Add ORDERDATE, PRODUCTNAME, QUANTITYORDERED, and PRICEEACH to the Details band.

   Do not overlap fields.

4. Resize PRODUCTNAME larger. Resize QUANTITYORDERED smaller.

   

   Resizing
5. Click Preview ().

   

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

Next: Refine your report.

Refine your report

You created a report in the previous exercise. Now add labels, headers, and row banding.

1. Drag a label () to the Page Header band.

   The Structure tab updates.

   

   Structure tab
2. Click inside the label and type Order Report.

3. Select the label text. Set the font size to 18. Apply bold.

   

   Font resizing
4. With the label selected, set a font color.

   The page header appears on every page.

5. Create column headers. Click Details Header under the Structure tab.

   The Style and Attributes tabs appear.

6. Under common in Attributes, set hide-on-canvas to False.

   The Details Header band appears.

7. Click the Select Objects icon ().

8. Select all column objects in the Details pane.

   

   Selected objects
9. Press Ctrl+C to copy. Press Ctrl+V to paste into the Details Header pane.

10. Select Format > Morph > label.

    The objects change to labels.

11. Type the header labels: Order No., Order Date, Product Name, Quan., and Price Each.

12. Click Preview ().

    

    Report example
13. Select Format > Row Banding.

14. In the Row Banding dialog box, select Yellow for Visible Color. Click OK.

15. Click Preview.

    

    Report with row banding
16. Select File > Save. Save to .../report-designer/samples. Use Orders as the file name.

Next: Row banding, data formatting, and alignment.

Row banding, data formatting, and alignment

Row banding

Create a row band element to control which fields show banding. You can name the row band element anything.

In this example, the row band element is named row-band-element.

Row Banding dialog box

After you create the element, select the fields to band. In Attributes, type row-band-element in the name field.

Name field, Attributes

Data formatting

Report Designer uses default formats for dates and numbers. You can change formats under Attributes.

Select a field. Then select a value for format.

In this example, Order Date uses MM-dd-yy.

Format field, Attributes

Preview the report to confirm the results.

Order Date formatting applied

You can also type a custom format string. Use the JavaScript date and number format syntax.

Alignment

To align multiple objects, select them first. Then choose an alignment option under Format.

To multi-select, press Shift and click each object. You can also use Select Objects () and drag to select.

Alignment selection, Format menu

Next: Add a chart to your report.

Add a chart to your report

In this exercise, you add a chart to your report.

1. Select File > Open. Open the report you saved earlier.

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

3. Resize and center the chart.

   

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

5. Select the pie chart icon.

   Chart properties are listed on the left. Data properties are listed on the right.

6. Under Title, set chart-title to Product Pie Chart.

7. Under Common in Primary DataSource, set value-column. Click the ellipsis to open the Select Field dialog box.

8. Select QUANTITYORDERED and click OK.

9. Under Series, click the ellipsis next to series-by-field.

   The Edit Array dialog box opens.

10. Click the Add icon ().

11. Select PRODUCTLINE and click OK.

12. Click OK to close the Edit Chart dialog box.

13. Click Preview ().

14. Scroll to the last page.

    

    Displayed report
15. Save your report.

To use a bar or line chart, change the chart type. Add series-by-value entries for SALES and COST.

Next: Add parameters to your report.

Add parameters to your report

Now make your report interactive by setting parameters. Users get prompted for values when they run the report.

1. In Report Designer, open your Orders report.

2. Select Data > Add Parameter.

   You can also select Master Report Parameter () under the Data tab.

   The Add Parameter dialog box appears.

3. In Name, enter enter_prodline.

4. In Label, enter Select Line.

5. For Display Type, select Drop Down.

6. Under DataSources, select JDBC (SampleData (Hypersonic). Click the Edit icon ().

   The JDBC Data Source dialog box appears.

7. Under Connections, select SampleData (Memory).

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

   Query 2 is added.

9. In Query Name, enter prodlineList.

10. In Query, enter the following SQL:

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

    You can also build the query in SQL Query Designer.

11. Click OK to close the Data Source dialog box.

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

13. For Value Type, select String.

14. Optional: Set a default value, such as Motorcycles.

    

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

16. Map the parameter back to Query 1. Under Data, double-click Query 1.

17. Click the Edit icon () to open SQL Query Designer. Right-click PRODUCTLINE and select add where condition.

18. In the condition editor, enter ${enter_prodline}. Click OK.

    

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

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

21. Click Preview.

    

    Product line menu
22. Save and close the report.

Next: Publish your report.

Publish your report

Now publish the report to a Pentaho server.

1. In Report Designer, open the report you created.

2. Select File > Publish.

   You can also select Publishes the report on a Pentaho server ().

3. In the Login dialog box, confirm the server URL is http://localhost:8080/pentaho/.

   

   Login dialog box
4. Click OK.

   The Publish to Server dialog box appears.

5. Enter a report title and description.

6. Under Location, save to ...public/Steel Wheels.

7. Set Output Type to html and click OK.

   A success message appears.

8. Click Yes to open the User Console and view the report.

   To view it later, go to http://localhost:8080/pentaho/. Then browse to the Reporting Examples directory.

9. Log in as Admin.

   The default password is password.

10. Select a product line parameter value. Keep the default Output Type.

Your report is now available to users.