Welcome to BACON:CONSULT‎ > ‎Services‎ > ‎odoo‎ > ‎

Business Intelligence and Printing Reports in OpenERP


Contents

  1. 1 Introduction to Creating and Printing Reports in OpenERP
    1. 1.1 Index of Reporting Approaches Test
      1. 1.1.1 Pentaho Reporting and Configuration
        1. 1.1.1.1 Report Markup Lanague (RML) - The Open Report Engine process the .RML file inserting data from the database at each expression.
        2. 1.1.1.2 OpenOffice OpenLibre Report Designer (Plug-In) - using sxw2rml print engine and a plugin for OpenOffice / Openlibre
        3. 1.1.1.3 RML:XML Reports
        4. 1.1.1.4 WebKit Reports in OpenERP - Webkit - an alternative solution using htmltopdf print engine.  
        5. 1.1.1.5 Aeroo Reports in OpenERP
      2. 1.1.2 Report to test 
        1. 1.1.2.1 Jasper Reporting
        2. 1.1.2.2 Pexego OpenOffice Reports 
        3. 1.1.2.3 Microsoft Report Viewer (see also http://www.youtube.com/watch?v=sXJmRHgSAS8&feature=fvwrel)
        4. 1.1.2.4 Java Docs - http://www.javadocx.com/
        5. 1.1.2.5 Java Report Helper 
        6. 1.1.2.6 Terminatooor printing solution 
    2. 1.2 Reports in your OpenERP Implementation Project as a part of FRICE-W development
      1. 1.2.1 Examples of paper reports: 
      2. 1.2.2 Instructions from the OpenERP Module
  2. 2 Pentaho - OpenERP Report Integration by Willow IT
    1. 2.1 Report Designer and Java Component
    2. 2.2 Report Types
    3. 2.3 Report Parameters
    4. 2.4 Setup
    5. 2.5 Report Actions
    6. 2.6 Disclaimer
    7. 2.7 Library
      1. 2.7.1 Index of Reporting Approaches
        1. 2.7.1.1 Pentaho Reporting and ConfigurationReport Markup Lanague (RML)OpenOffice OpenLibre Report Designer (Plug-In)RML:XML ReportsWebKit Reports in OpenERPAeroo Reports in OpenERPAndre Schenkels Comments

Introduction to Creating and Printing Reports in OpenERP

This discussion is more of a technical review of various  approaches available in OpenERP for creating and printing reports, forms, labels, standard company stationery such as orders, invoices, delivery notes, etc. .  For each printing approach I've added / (will soon add)  implementation guides for each approach. 

The description is based on an OpenERP 6.1.1 sandbox implementation: 
The web client is running on a PC and is on the same local network (but not on the same machine). I haven't used GTK client for the testing, as this will be phase out during the next releases of OpenERP v7.0.


Index of Reporting Approaches Test

The Printing Solutions I've tested so far within OpenERP are:

(Not all these solutions worked for me)

Report to test 


These have not yet been tested and are on the 'to-do' list

Please note:  This webpage is basically my own notes and is no way complete. I'm not suggesting the approach I've taken is the correct or the most elegant approach.


Reports in your OpenERP Implementation Project as a part of FRICE-W development

As within all ERP projects (e.g. SAP, OpenERP, etc)  there is a group of developments known as FRICE-W. As this is a very special area of development,  I normally assign this to a separate development team in the project. Some parts of these developments can be done by 'off-shore' developers. This really depends on the completeness and accuracy of the functional designs. From experience though,  the specifications are merely broad requirements assembled from non-technical end-users and are not really adequate for designers to know exactly what the end-user require and see  too often mis-understandings arise due to lack of communications. Ideally reports (and other FRICE-W) activities should be developed on-site,  as an extremely high input from the clients' key-users is required and communication i paramount to avoid misunderstandings and also project delays (e.g. delays due to working in different time zones, etc).  

FRICE-W covers the following areas of development. 
  • Forms - The new user screens / views that need to be developed
  • Reports  - Statistical / accounting reports, Internal paper forms -like Picking Lists, Printed Stationary (Contracts, Order Confirmation, Invoices, etc) and labels!! 
  • Interfaces - interfaces to other solutions, like accounting, CRM, Warehouse management systems , Weighing machines, Bar Code Scanners, etc
  • Conversion - The activity of migrating the master data from your legacy system in your new ERP. This is probably one of the most complex areas in a project 
  • Enhancements - These are the new add-ons and new features that need to be developed
  • Workflow - Configuration of the ERP to react according to your new. 
This article focuses on Reports 

During most  ERP implementation projects the printing of Reports, Paper Forms and Labels are too often underestimated (as with all elements of FRICE-W activities).  Not only regarding the complexity of configuring forms, labels  & reports per se, but also the increditble amount of time it takes to set them up and test them; including the general set-up of the general printing solution. Let's be realistic - the customer will only see your new order confirmations and invoices after the implementation of an ERP - this should be an opportunity to impress your customer - so make sure you really get the calculation correct on these forms and test these flows throughly! 

For larger implementations, separate infrastructure may need to be implemented such as print servers, purchase new printers, configure label printers, scanners, etc. I've seen many project managers add Reports / Form as one of the last tasks on the project plan - with the false assumption that these exist or is an easy task. 

Don't believe that end-users will start to use the new ''on-line'' Forms rather than paper forms. I  would therefore highly recommend to any project manager that all report developments are started as soon as possible. Start to collect all of these paper forms during the detailed requirements / gap analysis to obtain a good estimate of the effort - For larger implementations add 50% contingency to your estimates!   Estimated effort needs to be included in the project plan for Blue-Priinting (Design) &  Build phases  so they are also ready in-time for Unit and User Acceptance Testing (UAT). In many projects I've seen the client also 'discovers' new forms, labels or report during UAT or just before or after go-live,  that they forgot to mention! 

Examples of paper reports: 

Paper Forms - Contracts, Quotations, Orders, Invoices, Proforma invoice, Picking List, Delivery Orders, Dispatch Notes (usually at least 1 per shipping company), Legal forms for delivery, Stock movement Notification, Production Orders. 

Labels - Product Label, Box Labels, Production Labels (In many projects the client want to introduce barcode scanners so this requires more testing)


Instructions from the OpenERP Module

Below are the instructions from the OpenERP 7.0 module

Pentaho - OpenERP Report Integration by Willow IT

This module integrates Pentaho's reporting engine with OpenERP.

This project was developed by Willow IT, using the libraries and extensions developed by De Bortoli Wines, Australia (Pieter van der Merwe in particular) for the Pentaho reporting system. The OpenERP addon also derives from and/or is inspired by the Jasper Reports addon developed by NaN-tic.

Willow IT contributions:
Deepak Seshadri - OpenERP-Pentaho server connector (Java) Richard deMeester - frontend and core functions, automated wizard and action implementation Douglas Parker - additional integration Jon Wilson - inspiration, testing, and whipping

Report Designer and Java Component

For notes on installing and running the report designer, refer to:

http://bit.ly/L4wPoC

The README document contains instructions relevant to building and running the java component.

Report Types

Broadly speaking, two types of data sources can be used to produce the reports. OpenERP object data sources or SQL query data sources.

  • Object data sources have the advantage that they can use OpenERP model columns, even those that are not stored in the database. This includes functional fields, related fields, properties. They can iterate through one2many and many2many subfields. They also respect OpenERP record rules.
  • SQL data sources have the advantage that they allow greater flexibility in selections, and other SQL features like grouping and selective inclusion of sub-queries. It also allows selection where no OpenERP model relationship exists. It does not respect OpenERP record rules, which may be seen as an advantage for creating summary type reports which may be run by users who may not be able to view low-level data. Because of this, you need to be careful.

Report Parameters

Prompted and hidden parameters are supported, as are mandatory values.

Most Pentaho report parameter types and features have been mapped, where practicable, to something which makes sense to an OpenERP environment. This means that a number of Java data types don't necessarily differentiate. For example, (Integer / Long / Short / BigInteger / et al) will all be treated as integers.

Default values can be passed, but default value formulas are not implemented. The one special default value formula =NOW() is the only exception, which returns datetime.date.today() as the default.

Hidden parameters must obviously receive and work with a default value of some sort. This default can be the Pentaho default, or can be sent to the report in the context in the format:
{'pentaho_defaults' : { .... }}

where the passed dictionary contains the parameter names as keys. See below for guidance on where to set this up.

Pentaho Display Types have been consolidated. Drop Down, Single Value List, etc, all display as OpenERP selection pull downs. Pentaho multi value selection types, such as Multi Value List, Check Box, etc, are implemented as single value selection pull downs. Date Picker uses the standard OpenERP date/time widget.

Other Pentaho parameter features should be considered unsupported, such as Post-Processing Formula, Display Value Formula, Visible Items, Validate Values, et al.

Setup

Some parameters may be required in Settings/Customization/System Parameters.

The address of the Pentaho server which is used to render the report is defined with the parameter:

  • pentaho.server.url

For object based data sources, the Pentaho server will use XML-RPC to connect to the current database using the interface and port as defined in the OpenERP config file, and the reporting user's credentials.

For SQL query based data sources, the Pentaho server will use the following parameters:

  • pentaho.postgres.host
  • pentaho.postgres.port
  • pentaho.postgres.login
  • pentaho.postgres.password

Report Actions

Reports are defined to OpenERP under Settings/Customization/Low Level Objects/Actions/Pentaho Reports.

Reports can be handled by OpenERP in two ways. They can be linked to a menu, or they can be linked to a model.

  • Model linked reports will appear in the right hand toolbar as per standard reports, or they can be specifically called with an action, such as a button. A record or number of records needs to be selected before the action will be invoked, and the ids of the selected records will be passed to the report as a list parameter called "ids". A report invoked this way will not prompt for any additional parameters. A front end custom wizard can be created if desired to invoke the action, but that wizard and the report would be very closely tied and need to be developed in conjunction with one other.
  • Menu linked reports will appear somewhere in a menu. They will pop up a window prompting for a report output type, and any additional (visible) parameters that are defined. Object ids are not passed to these reports, so selections are probably required if not all data is to be included.

The actions can override existing OpenERP actions (such as invoice prints) or can be new actions.

The service name is only important if it is overriding an existing service, or if the report is to be invoked from coded actions like buttons or wizards.

Output types specified here are defaults, and can be overridden by either a custom wizard or by menu linked reports. They will not be changeable for model linked reports which don't have specific coding.

The prpt (Pentaho report definition) file selected is stored in the database. Changing the report using the designer will require the report to be re-loaded.

A prpt file and action can easily be defined as part of a module and distributed this way. Be aware, though, if the module is upgraded from within OpenERP it could potentially reload the distributed report and may lose changes that were uploaded manually after module installation.

Security groups entered against the action will be respected in regard to action visibility - they play no role in the report execution.

If a context value is required to set a default value, it needs to be set against the created action. It comes up under Settings/Customization/Low Level Actions/Actions/Window Actions. It will already have a Context Value with the service_name defined, which should be left intact.

Disclaimer

This has been developed over time to meet specific requirements as we have needed to meet them. If something is wrong, or you think would make a great feature, please do let us know at:

support@willowit.com.au

Library

We will be endeavouring to create a library of sample and useful reports. Check at:

http://www.willowit.com.au/

where we will announce when and where this is available. In the meantime, if you develop any reports or templates that you would consider worth sharing, please email them through with some description or details.