OpenOffice.org and ERP Reports

Reports have always been the least interesting part of the programming activities for me. I recall being told by the CEO of a company for whom we were doing a project, “See, you said it can't be done. My man has given me the report I wanted.” His man had used a word processor, cut and pasted from half a dozen of the reports we had given and prepared the report the CEO wanted. He may even have massaged the numbers while at it!

Reporting tools and technologies are a lot better today. I could have given him the report he wanted as a pdf file. It is also much easier to accommodate requests for an extra column – just reduce the font size :) We can even make the CEO happy with colourful reports, graphs and logos.

Utility of an ERP system is greatly determined by reports. Ability to generate exception reports, detailed reports, custom reports, reports specific to a role, etc are all very important. Hence, we need to understand how easily can existing reports be modified and new custom reports generated.

Among the best known reporting tools in the Open Source world is JasperReports. It is widely used in the Java world. The report specifications are given as an XML file. There is a gui tool, iReport, for creating the XML report specifications. ERP applications like Compiere, Adempiere, OFBiz all provide integration with JasperReports.

Reporting Tools

What about TinyErp and Python?

Python-world has a product called ReportLab PDF Toolkit. We use this library from within Python. Here's a 'hello world' example from the ReportLab site:

from reportlab.pdfgen import canvas

from reportlab.lib.units import cm

c = canvas.Canvas("hello.pdf")

c.drawString(9*cm, 22*cm, "Hello World!")

c.showPage()

c.save()

As one would expect, programming such a report is similar to programming a gui. We control the drawing position, the fonts, the foreground/background colours. We can draw geometrical objects, insert images, etc. It would be fun but time consuming. Hence, as in the case of JasperReports an XML specification for the report is useful. TinyERP uses a dialect of XML, RML (Report Markup Language) format also introduced by ReportLab. A simple example looks like:

<!DOCTYPE document SYSTEM "rml_1_0.dtd">

<document filename="example_2.pdf">

<stylesheet>

</stylesheet>



<story>

<para>

This is the "story". This is the part of the RML document where

your text is placed.

</para>

<para>

It should be enclosed in "para" and "/para" tags to turn it into

paragraphs.

</para>

</story>

</document>

Not surprisingly, the RML file has two main sections – a style sheet section and a content section. TinyERP comes with renderers like rml2pdf, which will produce a finished report in PDF format using the ReportLab PDF Toolkit.

Visual Tool for Reporting

This still leaves us with visual tool for creating a formatting the reports. If cutting and pasting in a word processor creates a good report, why not use that as the design tool?

This is precisely the approach used by TinyERP. We design the report in OpenOffice. It has, however, to be saved in the older OO1.0 format.

We can use tables to create a proper layout, insert images, control font size, etc. After creating the layout, we use the script tiny_sxw2rml.py to convert the sxw file to a rml template.

What about the dynamic content?

Expressions of the form '[[ <expr> ]]' are assumed to be dynamic content in the rml template to be replaced at run time. The '<expr>' in the square brackets is Python code using some objects available in the report template which is evaluated and inserted at run time. Typical simple examples would be object.name, object.email_id, etc. We will also need to repeat over objects. This is achieved by using a function repeatIn(object, variable). For example, for an invoice we will iterate over the selected objects, repeatIn(objects, 'o') and for each of selected object, we will iterate over the invoice lines, repeatIn(o.invoice_line, 'i').

We take the default invoice.sxw file for printing an invoice. The code for a line item in the invoice looks like:

[[repeatIn(o.invoice_line,'l') ]]

[[ l.name ]]

[[ ', '.join([lt.name for lt in l.invoice_line_tax_id]) ]]

[[ '%.2f' % l.quantity ]] [[l.uos_id and l.uos_id.name or '' ]]

[[ '%.2f' % l.price_unit ]]

[[ l.discount and '%.2f' % (l.discount) or '' ]]

[[ '%.2f' %l.price_subtotal ]] [[o.currency_id.code ]]

We print the item name, the taxes applicable, the quantity with units, price, and discount and the subtotal along with currency.

We then make a few modifications and change the look of the report. The before and after invoices are attached below.

Not all capabilities of OpenOffice can be used in the report. An interesting project would be to use sxw2rml.py as the model and create a odf2rml.py, while adding some more of the functionality of OpenOffice in the reports.

Summary

In principle, the ideas above could be applied on a wider range. E.g. the following was an interesting proposal by Anant Narayanan for a Google Summer of Code project to use Tiny's open source version of rml2pdf to convert docbooks documentation to PDF format: http://www.kix.in/soc/07/docbook2pdf-fedora.pdf

The reporting capabilities with TinyERP can help one create and customise reports without too much effort. With this we conclude the 3 articles on TinyERP and the lessons learnt include:

Using OpenOffice as a layout engine for reports.

The separation of the logic engine and the display engine is striking. Instead of using the tinyerp client, one can use eTiny, which is a web-based front end built using TurboGears. The eTiny code size is less than half of the original tinyerp client.

It is surprising and intriguing to use a web application framework just for the user interface and talk to the application engine using xml-rpc.

Oh, incidentally, TurboGears uses CherryPy and I love cherry pies.