How to create reports using Pentaho Report Designer, a tutorial

Pentaho Business Intelligence paltform provides several tools to design and deploy reports, the easiest is the Pentaho BI web reporting wizard (see pentaho_reporting).

While this approach is useful for basic or self service reporting, for complex reports with charts, cross linking and scripting a tool like Pentaho Report Designer is more appropriate. Pentaho Report Designer is the reporting engine integrated in Pentaho Business Intelligence.

Other excellent Pentaho compliant reporting tools are Jasper Reports and Eclipse BIRT, however Pentaho Report Designer has the advantage to be more integrated and easily deployable.

Connect a database in Pentaho Report Designer

Pentaho Report Designer is an open source reporting tool available at http://sourceforge.net/projects/pentaho/files/

Once unzipped the application can be run with report-designer.bat (report-designer.sh in unix). To create a new report press File->New and then Data->Add datasource->JDBC. Pentaho Report Designer connects with several data source providers, for example SQL databaseMondrian OLAP and Pentaho Data Integration (Kettle).

In this example we'll use a common JDBC connection for SQL databases. To add a new connection press the plus icon on the JDBC dialog.

Tutorial add a data base connection in pentaho report designer

On the new dialog set the data base connection credentials, in this example a preexistent MySQL database named SugarBI, with the following parameters:

Connection name: SugarBI
Connection type: MySQL
Host name: <host>
Database name: <database name>
Port number: 3306
User name: root
Password: <your password>
Access: Native (JDBC)

Tutorial add a JDBC data connection in pentaho report designer

These parameters are specific to MySQL and changes depending on the data base vendor, for example Oracle, Microsoft SQL Server, etc. Make sure the correct connection type is selected and the vendor JDBC driver is copied in /report-designer/lib/jdbc folder for Pentaho Report Designer. The correct JDBC driver can be dowloaded from the database producer's web site.

This JDBC driver needs to be copied in the biserver/tomcat/lib and administration-console/jdbc folders of Pentaho Business Intelligence as well to allow the Pentaho web platform to execute the query. In case of MySQL databases the JDBC driver is already provided and these steps are not necessary.

Now press Test to check the connection and then press ok. In case of connection error check the connection parameters and if the JDBC driver is present in /report-designer/lib/jdbc. 

Now press the plus icon and fill the SQL query text and query name, like in the following picture.

Pentaho report designer set data SQL query

To check if the query works press Preview button and then Ok to close the dialog. 

In a real enterprise application a JNDI connection would be a better solution than a direct JDBC connection. A JNDI connection, which is explained at the end of this post, defines a JDBC connection alias and avoids the hassle to update all the reports in case of database parameters changes.

Build a report with Report Designer

After the SQL connection configuartion the Data tab (on the right) should display the connection name with all its fields. Placing a field in the report is very straighforward and can be accomplished by dragging and dropping the field on the Details band.

Pentaho report designer build report

To set a page title click on "Ab" button in the left toolbar and drop it on Page Header band and to set a column label simply drag the "Ab" button over the Report Header band.

A page number can be added by right clicking Functions (right Data tab) and selecting Add functions..->Common->Pages of Pages. The new Pages of Pages field can then be dragged on the Page Footer.

Pentaho report designer build report

Pentaho Report Designer allows to test the final result at any time by pressing the play icon and choosing an output format, for example PDF or HTML.

How to publish a report in Pentaho

Once built the report can be run directly on Pentaho Business Intelligence suite. Pentaho Report Designer provides direct access to Pentaho's repository, however the access needs to be enabled first. To enable the repository access edit biserver\pentaho-solutions\system\publisher_config.xml and set the password in the publisher-password tag:

<publisher-config>
<publisher-password>your password here</publisher-password>
</publisher-config>

See pentaho_publish_password_how_to for more information.

Now restart Pentaho Business Intelligence server and press File->Publish from Pentaho Report Designer.

Pentaho report designer publish report

The default credentials are:

URL: http://localhost:8080/pentaho
User: joe
Password: password

Click Ok and if Pentaho the server is running a form like this one should appear:

Pentaho report designer publish report

To publish the report the following fields needs to be set:

  • the file name, title and description
  • the server folder location (for example steel-wheels/reports)
  • an output format (for example PDF) 
  • the publish password as set in the publisher-password earlier

Press ok, the report should appear on the Pentaho repository browser of the Pentaho Business Intelligence suite:

Pentaho report designer publish pentaho repository

Using a JNDI connection

The example is based so far on a direct JDBC connection, which is usefull for testing prouposes. In a real world application with many reports and a database configuration which can change, a JNDI connection is recommended. Not only this will slightly increase the performance of the report generation, but also it allows a greater maintenability. 

As explained in how_to_register_jndi_datasource_pentaho_connection_example this task can be accomplished in some few steps:

  • Make sure all JDBC drivers of you data base vendor are copied in the /biserver/tomcat/lib and /administration-console/jdbc folders of the Pentaho Business Intelligent Suite. The JDBC driver needs to be copied in the folder /report-designer/lib/jdbc of the Pentaho Report Designer as well.
  • In Pentaho Administration Console (run administration-console/start-pac and point your browser to http://localhost:8099) or in Pentaho Business Intelligence Suite (File->New->Data Source..) define a new data base connection named, for example, SugarBI.
  • Now in Pentaho Report Designer tool edit c:/documents and settings/<user>/.pentaho/simple-jndi/default.properties with the database connection details, for example:
  • SugarBI/type=javax.sql.DataSource
    SugarBI/driver=com.mysql.jdbc.Driver
    SugarBI/user=root
    SugarBI/password=<password>a
    SugarBI/url=jdbc:mysql://localhost/sugarbi

  • In this way both the Pentaho Business Intelligence Suite and Pentaho Report Designer can use a common name to connect to the database. 
    This name can be set on any report as JNDI connection type (see picture). In case of database parameter changes on the Pentaho Report Designer default.properties file and the Pentaho Business Intelligence Suite data source connection details need to be updated.

Tutorial add a JNDI datasource in Report Designer

 

tags: