Integration of Jasper Reports into OpenERP

Albert Cervera i Areny Jan 15, 2009

This time I won't talk about Koo but another project we've started at NaN a few days ago. It's the possibility of using Jasper Reports as any other type of report such as RML.

The module is currently hosted in the server-modules directory of the Koo project at launchpad (https://launchpad.net/openobject-client-kde). Not because both projects have any relation (Jasper Reports work with any OpenERP client you want to use) but because we already had the repository set up.

The module let's you work in two different ways:


  • SQL based report
  • XML based report

In first one, as expected, you create a Jasper report (possibly using iReport) connecting directly to the database through JDBC. Once the user wants to see the report, the jasper_report module will call your report with the connection of the database the user is using in that given time.

The XML based report is much more interesting as it lets you easily browse model fields and relations and add them to the report from within iReport itself. We've kept integration work to a minimum usable and thus it needs some "strange" (though easy) steps before you can use this functionality, but allows for high productivity on report design.

Let's take a look what the steps you need to take to create an XML based jasper report.

The job starts at the Administration -> Custom -> Jasper Reports -> Create Data Template wizard that the jasper_report module adds to OpenERP's menu. We'll choose the main model we want data to come from (and the model in which you'll add the report as any other report type). In this example we'll choose 'res.partner'. You must also select the depth in which you want the file to be created. For example if you want to be able to browse from partner to the country name of an address you need at least depth three (address->country->name). Using depths higher than 5 can be time and resource consuming, depending on the model you start, so take care not to use too high values you most possibly won't need.



Next step of the wizard let's us store an XML file we'll save on disk. ("/tmp/jasper.xml", for example).



Afterwards, we can start iReport and the first thing to do is create an XML datasource pointing to the file we've just stored on disk.



Now you can create a new report, and using the current connection go to edit the report query.



You'll need to choose XPath as query language. On the right, you'll see the XML file structure that matches 'res.partner' model fields and relations. You must select "/data/record" as record node (by right clicking on it, for example). Then you can add all the fields you want to use on the report with their relative path (Note there's a bug in iReport 3.1.2 that forces you to click ok and then edit the query again, otherwise all fields are added with absolute path, which jasper_report module won't accept).



As you can see selecting fields including read-only ones and relations fields is really simple, avoiding lots of SQL joins and searching tables and fields.

Once we have all the fields we want we can use them in the report, and use all iReport/Jasper Reports power, including subreports, charts, crosstabs, etc.

In our example, though we don't want one record per partner, but one record per partner address. In such a case you'll need to add a property to the report called "OPENERP_RELATIONS" as seen in the image below.



Once we've finished the report we can store the ".jrxml" file in our module's directory and write "module_report.xml" file as you would with any other report:

< report string="Jasper Partners" model="res.partner" auto="True" name="res.partner.jaspertest" rml="your_module/partner.jrxml" id="res_partner_jaspertest" menu="True" header="False" />

Now you have your Jasper Report ready to use! And you can freely use SQL and XML based reports as the module automatically knows how to call them.

Note that the module is still under development and there are some known limitations but we think it has a great effort/result ratio because with very little effort we've been able to improve reporting possibilities a lot.

From now on we'll keep improving the module which currently only works (has been tested) with OpenERP 4.2. Of course a higher integration level with iReport would be fantastic but it's not in our road map in the short term. So if anyone is willing to contribute with code or sponsorisation it will be very welcomed.

Top