Self service Pentaho Reporting for simple reports

One of the most requested features in Business Intelligence is the ability to let the final user to perform custom data analysis without the need of IT staff.

Pentaho Business Intelligence Suite provides an intutive reporting wizard, easily accesible by the final user even without programming skills. For more complex reports more advanced reporting tools are also available, like JasperReportsBIRT or Pentaho Report Designer (see how_to_use_pentaho_report_designer) However these tools require good development skills.

Create a data base connection

The first step is to define a database connection and a SQL query to fetch data.

Start Pentaho Business Intelligence Suite and log in at http://localhost:8080/pentaho as admin (Joe user). Now click on File->Manage->Data Sources.. The next window shows all the available data sources, as in the picture.

List pentaho data sources

To add a new data source click on the plus icon and in the new window select Database as Source type, like in the picture:

Add pentaho database connection

Now click the plus icon again, a form will appear asking for the connection parameters. In the example below a preexistent MySQL database "SugarBI" will be used, but you should use your own database with some data.

Add pentaho database connection

Pentaho Business Intellitence supports all JDBC compliant SQL databases, however in the default installation it provides HSQLDB and MySQL driver only.

To connect Pentaho with databases like Oracle Database, Microsoft SQL Server, IBM DB2 etc., the vendor specific JDBC driver must be copied in Pentaho bisever/tomcat/lib folder and administration-console/jdbc folder. After rebooting the Pentaho platform the database driver will be avaiable.

After setting up your own database connection it's time to set a SQL query to retrieve the data.

Define a report data source

After pressing ok to confirm the database connection, write on the "SQL Query" text field a query that retrieves some data to be analized. Make sure your new connection (DemoConnection) is selected as Connection and the query doesn't contains the final ; character.

Define pentaho datasource connection

Press Data Preview to check your data and then Finish to confirm the changes. The next window asks if you want to customize the labels and formatting of your new data source.

Pentaho reporting metadata

Press Customize model to define additional presentation settings of your new datasource, such as column labels or data formatting, otherwise the default Pentaho assigned values can be used.

Pentaho reporting define metadata

Now press Ok, the new datasource will be avaiable on Pentaho Business Intelligence Suite and the user will be able to create new reports starting from this connection.

Create a new report

Once the datasource and data model are defined, it's time to build a new report using Pentaho quick reporting tool.

From Pentaho platform press File -> New -> New Report. In the next window choose your datasource as Data Source, apply a Template, select an preview format (for example PDF) and then press Next.

Pentaho report wizard

The reporting wizard is quite straightforward. To add new column, groups or filters to the report simply drag the columns defined in the model and drop them in the panels on the right. 

Pentaho create report

In the next page more presentation information can be defined for each column, like constraints, sorting, formattinc etc.

Pentaho create report

In the last page press Go button (Preview) to show a preview of the final report during the creation process. To save the report press the floppy icon and choose a repository where saving the new report.

Pentaho create report

 

Pentaho create report

Now the report is saved in the repository and can be executed from the repository browser.

Pentaho create report

Easy, reports can be saved and customized at any time. The only requirements is to define a database connection and a query to retrieve data which can be used to populate the reports.

When reports are more complex (chartings, cross linking, etc) the Pentaho Reporting Designer provides a more sophisticated soultion (see how_to_use_pentaho_report_designer).

tags: