Reporting - Pentaho and OpenERP

Pentaho Reporting for OpenERP 





Introduction

This tools is really cool and one of my favourites. 

Pentaho, written in Java,  is very powerful tool and can be used along with OpenERP. Pentaho's approach is useful for general reporting or for reports created by the final user (self service reporting). For complex reports with charts, cross linking and scripting facilities, the Pentaho Report Designer is used and is the reporting engine integrated in Pentaho Business Intelligence. Other excellent reporting tools that can be used in Pentaho are Jasper Reports by JasperSoft and Eclipse BIRT by Actuate. They are both excellent and well known tools, but Pentaho Report Designer has the advantage to be more integrated and easily deployable in Pentaho solutions.

Pentaho has a built in report designer and enables you to extract records dynamically from an OpenERP database.  If you are an OpenERP module developer,  I would recommend installing this tool anyway,  as  it allows you to view all objects in an OpenERP database and all fields and relationships. This has saved me hours trying other methods to identify field names and relationships within the OpenERP database. 

The Pentaho OpenERP module  allows you to integrate and print reports which have been created using the Pentaho report designer. The Pentaho Java Print server needs to be installed. Details if how to install this are also included below. 

Disclaimer: These details below are my own notes,  based on installations I have done. These notes are not endorsed by the developers of Pentaho or OpenERP in any way. These notes may be incomplete so please use them with caution and at your own risk. 


Setting up  Pentaho Reporting for OpenERP 7.0

(For the demo below to work you'll need to install the sales and manufacturing modules installed in OpenERP 7.0 - with demo data!) 

Step 1 - Install Pentaho

Download an install Pentaho reports from their website here ->:


Step2 - Learn Pentaho
  • Read some background documentation https://github.com/WillowIT/Pentaho-reports-for-OpenERP/blob/master/README.md
  • Walk though the Pentaho examples. Even though the connection to the OpenERP database is totally different to OpenERP the procedure for creating drop down lists is the same. Run the demo application and get the drop-down menu working to filter a query -> example  Note:  the instructions refer to an older version and therefore has an error. When you get to the point where you need to enter the link between  Query1 and  Query2 you'll need to create a 'where' clause instead as follows: 

  • If you get stuck,  then here is the example Pentaho report file for you to upload into your own Pentaho instance -> demo

Step 3 - Install for OpenERP connection and connection to an OpenERP Database


  • You'll then see the OpenERP menu appear.


Step 4 - Create the 1st Query as the main report

A query is created with all the fields required for the report. 

pentaho reports for openerp


Step 5 - Create a 2nd Query for the Filter in the Drop down menus for interactive reporting 
This step creates a filter for the first query,  in the form of a drop-down box. The drop-down box offers the user to select one manufacturing order which can then printed.  

This is a similar process as with the example exercise (as mentioned in step 2. The main difference is that the database connection to OpenERP is different to the JDBC example.  

You'll need to create a second query for the drop-down menu in the same way as the example exercise. To do this right-click the database (yellow database symbol) and create a new connection as follows: 

Pentaho reporting in OpenERP

  • Enter a query name 
  • Enter the IP address of your server. You'll need to wait for a few seconds before the connection occurs, then the Database dropdown menu will give a list of all database instances on that server. If you don't get these drop down menus then there is something wrong with the server connection. 
  • Enter your username and password of that database. 
  • Enter the model name. If no model names appear in the drop-down menu then the user name or password is probably incorrect.  
 
creating database connection2

Select the Search Field tab and in the left-hand pane scroll down until you see the name field. This is the Production Order name which looks like this MO/00043

Drag that across to the right hand pane and change the name of the field name to something more descriptive. This doesn't change the name of the database field but just changes the reference name from within Pentaho. 



Press OK

That's it!  The second query has now been created. 


Step 6 - Creating a the drop-down menu


Place the cursor over the second query called ProdOrderQuery, then click the icon  'add new master report parameters'. 

creating new parameters for drop-down menu


Step 7 - Configuring the drop-down menu

  • Select the new query create in the left panel of the 'Edit Parmater' dialog menu. 
  • Enter a name for the parameter such as ProdOrder. Remember this name as this is required in the next step. 
  • Create a label - This is just a message to the user so when they see the drop-down box they know what to do.
  • Change the value type to 'String' 
    In the 'Prompt' Section
  • Display Type = Drop Down
  • Query = the name of the 2nd query
  • name of the field used in the 2nd query. In this case it was changed to ProductionOrder
  • keep the display name the same as value
    Press OK 


editing pentaho parameters



Step 8 - Linking everything together 


Now we'll link the Drop-Down menu, Query 1 and  Query 2 (the filter) together.

  • Open  Query 1 again, by right-clicking the query and selecting 'Edit Query'
edit query1

Goto the filters tab 

  • Double click on the path [mrp.production] to select it. 
  • The press the GREEN icon + on the right side to create a new filter. 
  • Enter the name of the paramater that was create in previous step. In this case I called it ${ProdOrder}. (Note these are curley brackets) 
  • select any other  field before pressing OK -  as I notice that the value is not always saved.
  • Press OK
  • Open the query again to ensure the values have been saved 




Step 7 - Creating a simple filtered report. 


  • Right-click the query 1 and press 'Select Query'
  • This will list all the fields for that query

 
  • Drag and Drop a few fields over to the 'Details' Section of the report 




Step 8 - Running the report


  • Press the green 'run' icon and select print preview



  • The report will run showing the drop-down list (with all production orders) and the details of the production orders
  • You'll see the first production order selected as default and all records associated with that production order listed (if the auto-update on selection button is selected). 
  • By selecting other production orders in the drop-down menu, other corresponding production orders will be displayed. 

run final report


Now the user interface is working the next step is to get a more elegant presentation of the report (see examples in section below) 

Here is my  final Pentaho example -> Demo File that you can load into your own Pentaho. 

It's important that you have the sales and manufacturing modules installed with demo data for this example to work.

Summary 

As a summary, below is a model which depicts how the two queries and the Drop-Down menu are related. The first query is used to create the main report. You can see all the fields that will be used. 

The second query merely is used within the drop-down list and acts as a filter. The drop down list is created an associated to the second query. 

The name of the drop-down list is entered in the 1st query as a filer.  

Values displayed in the drop-down list are first populated and the user will be prompted to select one record drop down list. The results of the 1st query are then  displayed. 

(Click on Image to enlarge)
Pentaho Reports and Forms for OpenERP by BaconConsult




Installing and Configuration of the Pentaho Java Print Server

This section explains how to install and config the Pentaho Java Print server on an Ubuntu 12.04 LTS server. This is necessary if you want to print reports directly within OpenERP. 


Printing Pentaho Reports from within OpenERP 7.0

This section explains how to embedd the Pentaho report created with the Pentaho report designer directly in OpenERP

Example Forms Created using Pentaho:

(just click on the images to enlarge)












Refs:

  • WillowIT Wiki -> https://github.com/WillowIT/Pentaho-reports-for-OpenERP/wiki
  • WillowIT Project Site -> :  https://github.com/WillowIT/Pentaho-reports-for-OpenERP. This includes:
    • A read-me, with a link to instructions for setting up the designer, including the OpenERP access written by Pieter van der Meer: http://bit.ly/L4wPoC
    • The java print server component, and some guidelines on how to build that component
    • The OpenERP module that needs to be installed. __openerp__.py contains some help
    • Difference between reports with object and sql data sources
    • Discussions about report parameters and parameter handling, and the module's ability to create a wizard on the fly to prompt for parameters
    • OpenERP set-up and configuration
    • Binding reports to OpenERP actions
    • Security considerations

Sample Reports:

Video regarding Pentaho sub-reports

Background info:

General Tutorial:
Just search for Pentaho Tutorial and you'll find many excellent resources and videos like these:

Even though not related to OpenERP application, this is a well structured Pentaho tutorial in German which give some general overview of the workflow capabiltiies:

OpenERP object input:

OpenERP object Output: