Pentaho Report with OpenERP

Pentaho report designer is one of the best  tool that allow us to create pixel-perfect reports of our data in PDF, Excel, HTML, Text, Rich-Text-File, XML and CSV combined with  OpenERP.

Using pentaho we can easily transform all our data into meaningful information to our audience. A screenshot of pentaho report designer is shown below.

1.Designing Elements

As any other reporting tool there is pentaho report is  also separated by different bands.

Page  Header   : It will display at top of the report.

Report Header : Its the header for the entire  report. its only print once for a report.

Groups : We can group particular things as group. Whenever the group value change the item will be printed as new group

Details Band    : It contains all the iterated details.

Report footer : Its the footer for whole report. It prints only once.

Page Footer  : This is the footer page for each page.

In pentaho report designer have a tool bar at left panel. It includes several useful designing tools. that are describing below.

– It is used to provide labels which are static .

• – Its used for variable text fields. That depends on a field which described in our Data set

• – Its used for variable number field. That depends on a field value which described in our dataset, and , are also used to variable date and message fields.
• and are used to setup images in our report.
• are used to place Round,Rectangle ,vertical line, and horizontal line in our report respectively
• Survey scale is used to represent a graphical band having value in between 0 to 10
• using this tool we can insert chart representation in our report
• is used to include barcodes in our report
• Band is used to separate our data and provide different properties for elements that contains in this band
• We can add subreport by using this tool and include independent reports in our main report
• are used to manage index of report

We can change the properties of each element from the panel which shown in right side as follows

We can setup font size, style, position,etc from here.

Data Elements

We  can integrate our postgresql database details in to pentaho report dynamically with the help of sql queries. There is toolbar in right panel named as Data is used to integrate our data in to report.

Steps to connect our database with pentaho report

Data sets

• Right click on and select JDBC
• In connection filed add new connection as follows
  • Connection name : Any name that you wish
  • Connection type  : PostgreSQL
  • Access : Native (JDBC)
  • Hostname : your database host name
  • Database name : your current database name
  • Port number : your database port number(normally 5432 for postgresql)
  • username : your database use name
  • Password : your database password
  • Then press test connection if it correct it will show success message
• Next we need to add data selection by using the connection that we configured in previous step.

• • In available query press button to add new query. We can use any query as per our requirement.Then press OK
  • All the selected data fields will displayed under our data panel. We just need to drag and drop the elements in our report.

Functions

• Pentaho report designer provide several default functions for several functionalities like pagenumber, find sum of columns,etc. we can easily add this functions to our report using

Parameters

parameters are used to pass values into our report. pentaho automatically collect the following parameters from openerp

• ids: the context ids when the report action is called. This is almost always needed for object based reports to be meaningful.
• user_id: the id of the user running the report.
• user_name: the display name of the user running the report.
• context_lang: the language in effect when the report action is executed.
• context_tz: the time zone in effect when the report action is executed.

We can also pass our own parameters from openERP by passing {‘pentaho_defaults’: { …. }} in context that can also be accessible from pentaho.