Pentaho Report Designer

PRD is the acronym for Pentaho Reporting Designer and it is Pentaho’s report generator tool.

PRD enables the creation of reports with filters, graphics, sub-reports, etc, in PDF, Excel or HTML format and enables publishing directly at Pentaho BI Server.

Main features:

  • Report creation assistance;
  • Similar to other commercial tools;
  • It is possible to use iReport to create reports;
  • “Pixel perfect” report.

Installing PRD

Steps to install PRD:

Driver JDBC Installation

In order to use a data source at Pentaho Reporting, it is necessary to include JDBC drive before the initialization of report development:

report-designer/lib/jdbc

Main databases - URLs and Driver Classes

PostgreSQL

URL: jdbc:postgresql://localhost:5432/seu_banco
Driver Class: org.postgresql.Driver

MySQL

URL: jdbc:mysql://localhost:3306/seu_banco
Driver Class: com.mysql.jdbc.Driver

Oracle

URL: jdbc:oracle:thin:@localhost:1521:seu_banco
Driver Class: oracle.jdbc.driver.OracleDriver

Firebird

URL: jdbc:firebirdsql:localhost/3050:c://db//SeuBanco.fdb
Driver Class: org.firebirdsql.jdbc.FBDriver

Microsoft SQL Server

URL: jdbc:jtds:sqlserver://localhost:1433/dwprod
Driver Class: net.sourceforge.jtds.jdbc.Driver

Configuring Data Source

  1. Click on report designer.bat (Windows) or .sh (Linux) in the following folder:
 prd-ce-5.4.0.1-130\report-designer

  1. Click on New Report;
  2. Right click on Data Set >> JDBC >> SampleData:

  1. Add a query and paste your select ;

There is still the Query Designer resource that can be used to set up your query. To do so, click on:

Creating a Simple Report

  • PAGE HEADER

The content inserted into Page Header will appear at the top of the report. It is generally used to present the report name, company logo, description of existing fields or other information that you wish at the top of your report.

  • DETAILS

Here the existing fields at SQL query are inserted. Click on the desired column and drag it inside Details area.

  • REPORT HEADER

At Report Header the title of the report and the name of fields introduced in Details are inserted.

To insert, simply click on Label object at toolbar and drag it inside Report Header area.

  • PAGE FOOTER

At Page Footer area it is possible to insert a description about the report type, a page counter, and other required information.

Press to display your report:

To create a report using a template:

  1. Click on Report Wizard:

  1. Select the desired Template and click on Next:

  1. Provide the database connection, select the desired query and click on Next:

  1. Define your report layout, set a data grouping, specify the fields that will appear and click on Next:

  1. Format the fields, insert masks, alignment, aggregation functions and then click on Finish:

  1. Now your report is ready for publishing. In case you do not wish to make changes, the layout is ready and you can save the report.

  1. In order to publish the report, click on File > Publish:

  1. Click on OK:

Insert user and password with publishing permissions ate BI Server

  1. Select the folder where your report will be exposed. Choose the name you would like to appear and the default output type of the report:

  1. Now your report has already been published:

Working with functions

Pre-defined functions

With the purpose of knowing all company’s available credit and how many page does the report have, we will use the pre-defined functions at PRD.

Click on Data and then right click on Functions >> Add Functions

  • SUM FUNCTION

Click on Summary > Sum >> OK

  • Function Name: all the credit
  • Field Name: field that will be added

  • INSERTING PAGE NUMBER

Click on Common > Page of Pages > OK

  • Function Name: page
  • Format Pattern: Page {0} of {1}
  • Page Increment: 1
  • Start Page Number: 1

Creating Sub-reports

In order to create a sub-report that presents the 5 clients with the largest credit limit we have to:

  1. Drag Sub-report icon inside Report Footer ;
  2. Double-click on Open;

  1. Create a query with only the fields that will be used on the sub-report;

  1. Create a new layout to the sub-report;

  1. Click on the sub-report and inform the name of the second query;

Main Report: Sub-Report

Inserting Parameters

To add parameters:

  1. Right click on Parameter > Add Parameter:

  • PARAMETER CONFIGURATION

Complete the fields:

Name: country                               
Label: Country:
Value Type: Object                            
Display Type: Drop Down
Query: Query 1                              
Value: COUNTRY
Value: COUNTRY

Put country parameter, created in the query, between ${}:

Inserting Charts

Drag chart icon inside Report Footer:

Double-click on chart to edit information:

There are different chart models and their configuration are very similar.

Nevertheless the big secret resides on match metrics and chart.

  • BAR CHART

Drag bar char icon into Report Footer:

Double-click on chart to choose the model and edit information:

* RING CHART

Drag chart icon into Report Footer:

Double-click on chart to choose chart model and edit information:

  • AREA CHART

Drag chart icon into Report Footer:

Double-click on chart to choose chart model and edit information:

Creating a Drill Down Report

Pentaho presents, as default, Steel Wheels and its Order Status report. We will use them in our examples.

1) Create a new report (File > New);

Caution: Be sure that Bi Server is working.

2) Add JDBC connection using the following SQL code:

SELECT "ORDERFACT"."STATUS", sum("CUSTOMER_W_TER"."CREDITLIMIT") AS Sales
FROM "ORDERFACT" INNER JOIN "CUSTOMER_W_TER" ON "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" WHERE "ORDERFACT"."STATUS" NOT LIKE 'Shipped' GROUP BY "ORDERFACT"."STATUS" ORDER BY "ORDERFACT"."STATUS" ASC

3) Add a chart:

4) Double-click on chart and configure as following:

Commom:

  • Category-column: STATUS
  • Value-columns: [SALES]

5) You have to link Order Status. To do so insert the formula:

Values properties:

url-formula: Click on button.

6) At “Formula Editor” window, insert the following properties:

Category: User-Definied

Function: DRILLDOWN

7) Double-Click at DRILLDOWN:

8) Complete the following properties:

Location: Pentaho Repository
Server URL: http://localhost:8080/pentaho
Path: /public/bi-developers/legacy-steel-wheels/steel-wheels-4.8/reports/Order Status.prpt

9) Complete the following Report Parameters:

oStatus: =["chart::category-key"]

10) Complete the following System Parameters:

showParameters: =[STATUS]

Click Ok and save report.

11) Publish report on BI-Server

Define HTML as the output type.

12) After BI-Server publication, you must have this:

In order to test, click on Resolved, Or any other column.

13) Order Status Report:

Creation of warnings

  • PRD Indicators

At PRD you can use images and add a condition (If) to display, for example, a red circle whether the number is negative or a green circle whether the number is positive. In this example, circles are JPEG images.

Steps:

Include images in the report file:

File > Resources > Import

Your images are ready for use.

Now, add the image to report. Click on the following icon and drag it into your chosen location.

The last step resides in defining the value of the field that will depend on a specific condition.

Left click on image field. Choose attributes tab (at the bottom right corner of the screen) and find the value attribute. Use the following formula:

=IF(["MTD Act/Plan Var %"]<0;"green_1";IF(["MTD Act/Plan Var %"]<0.2;"yellow_1"; "red_1"))

Hence, the images green_1, yellow_1 and red_1 will be exhibited depending on ["MTD Act / Plano Var%"] value.

  • Colours Rule

If you do not wish to use images, but change the text colours according to the result you should follow these steps.

1) Select the field where you wish to apply the rule

2) At Style tab click to edit “text-color”

3) The following window will open:

Click on “...”

4) Add the following expression:

=IF([CREDITLIMIT]< 60000;"red";IF([CREDITLIMIT] < 100000;"yellow";"green"))

5) Press “OK” and “Close”.