Session 5.3 Goodies


How to create an XLS Report with Formulas


Introduction

This how to will add a new XLS Report with Formulas, using a Process Definition and create a menu entry to invoke it. Formulas are one of the most useful features in XLS sheets, used to perform calculations in order to determine the value contained in a given cell. They allow to build dynamic and flexible reports, that in some cases can be useful in terms of perfomance, because without using formulas, the corresponding calculations should be done in the report query itself.

The implementation requires development experience with Process Definitions Reports. See the following page for background information:

Bulbgraph.png  The ability to define Reports from Process Definition is available from 3.0PR15Q2

Example Module

This howto is supported by an example module which includes the simple report described in the examples of this document. The report is named Sales Order XLS Formula Report and prints in a XLS document a list of Sales Orders. It includes some formula samples (SUM, AVERAGE, MAX, MIN), defined dynamically.

The code of the example module can be downloaded from this mercurial repository: https://code.openbravo.com/erp/mods/org.openbravo.platform.features

Formula Definition

The JasperReports engine provides a feature which allows using a formula as content of a text field. This feature is only taken into account when the document is exported to XLS format and ignored in other formats like PDF.

In order to enable the formula usage, the XLS exporter should have the net.sf.jasperreports.export.xls.detect.cell.type set to true. This value is always true when launching the report from a Process Definition, so the developer does not need to care about it.

To create a formula field inside an excel report, a new text field should be added:

<textField evaluationTime="Report" isBlankWhenNull="false">
<reportElement key="textField-2" x="315" y="123" width="280" height="23" uuid="847022ab-408c-40b5-9695-c4854d03960e">
<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(G7:G"+$V{END_ROW}+")"]]></propertyExpression>
</reportElement>
<box rightPadding="5">
<leftPen lineWidth="4.0" lineColor="#C0C0C0"/>
<bottomPen lineWidth="1.0" lineColor="#C0C0C0"/>
<rightPen lineWidth="4.0" lineColor="#C0C0C0"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[Integer.valueOf(0)]]></textFieldExpression>
</textField>

Details to consider:

  • The <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(G7:G"+$V{END_ROW}+")"]]> contains the formula definition. It will display the sum of the column G, from row 7 until the last value present in the column.
    • CDATA section: used when we want to include a dynamic expression.
    • $V{END_ROW}: variable defined in the report to store the last position with a value in a column.
  • The <textFieldExpression><![CDATA[Integer.valueOf(0)]]> contains the value 0, this value will be used when exporting to all other output formats but XLS.

Our report will show some information about sales orders, with some formula fields defined as explained, used as a report summary. You can see the .jrxml template here.

Report Definition in Application Dictionary

We need to define our report definition in the Application Dictionary. This is explained here.

In this case in the Report Definition tab we use the XLS Template field as we want to generate a XLS Report.

Result

The result is a dynamic XLS Report, with four formula fields that summarize the displayed information. Thus, it is possible to change any value of the report and the formula fields will be recalculated automatically.

XlsReportProcessDefinition.pngXlsReportResult.png



Upgrade Jasper Libraries/Functional&Technical Specifications


Introduction

The objective of this project is the upgrade of the JasperReports library which is used within Openbravo for report generation.

Functional requirements

Jaspersoft Studio

As of version 5.5.0, Jaspersoft Studio is the official design client for JasperReports. iReport will remain as a supported product in maintenance-only mode until December 31, 2015.

With this project, the JasperReports library is being upgraded to version 6.0.0. For this reason, the Jaspersoft Studio is going to be the supported and recommended design client to work with Openbravo reports. The recommended version is 6.0.0. It can be downloaded from here.

New Features

Apart from the ability to use the new design client, several new features are included inside the new version, that will be available and ready to use in Openbravo.

  • Advanced Excel features: Dynamic Sheet Names, Format Pattern Property, Column Width Adjustment Properties etc. More information can be found here.
  • Custom Functions in Report Expressions: ability to include built-in functions which can be invoked directly in reports. They can be used in the expression of any text field. For additional information see here.
  • Export API: new API for setting exporter's input, output and configuration, which replaces the now deprecated parameter based exporter configuration. The ReportingUtils class already includes some methods for this new API.
  • Exporting to JSON Format Using Report Metadata: it is possible to export the report in JSON format, allowing the generated data in the report to be properly stored, transmitted or manipulated by specific engines.
  • Map Report: the map component in JasperReports allows to generate the most common Google maps inside a Report.
  • New PDF Export Configurations: new PDF exporter configuration properties (net.sf.jasperreports.export.pdf.permissions.allowed and net.sf.jasperreports.export.pdf.permissions.denied) to control PDF file permissions from within the report template or globally, from JasperReports configuration context.
  • New XLS Exporter: new Apache POI based XLS report data source.
  • Table of contents: Ability to create a table of contents with hyperlinks at the beginning of the document.
  • Report Parts: parts are external report templates, similar to subreports, and allow creation of documents with variable page size and/or orientation; parts can be displayed on separate tabs by report viewers and simplify creation of Table of Contents structures.
  • Report Book: new type of report in which sections are made of parts instead of bands. For more detail, see the following link.

Technical requirements

The technical requirement of this project is to get up-to-date report designs, in accordance with the current JasperReports schema.

For the already existing reports, it will be necessary to update its definition by replacing or updating the obsolete report elements being currently used. I should be check if the new java classes provided by the JasperReports API have changed (See the API Change number PR15Q3.3).

JasperReports distribution provides some utility tasks that will be used in order to update all the standard .jrxml and .jasper report definition automatically. An update sample task can be found here.

On the other hand, the new JasperReport library includes a dependency with the iText library, version 5.5.0. The version used in Openbravo until now is 2.1.7 so this library should be upgraded too. Due to this change, the following should be noted:

  • The java package com.lowagie.text has been replaced with com.itextpdf.text
  • This new iText version is licensed under AGPL V3 license with LGPL Exception. That means that it is only allowed to call this library through interfaces provided in the Jaspersoft LGPL Library and not in any way use this library except through the Jaspersoft LGPL Library.

The code will be reviewed and modified to ensure that the license terms are fullfilled. This results in a public API change reported here (number PR15Q3.4).

Along with this library, two new libraries are required to be included in the distribution

  • Jackson: the Jackson library is a JSON parser for Java, used internally by JasperReports
  • Apache POI: Apache POI is used by JasperReports in the XLS exporter and data source

Finally, the published modules should be reviewed in order to check how they are affected by this new library changes. In particular, the following modules are affected:

  • Analytics (org.openbravo.client.analytics)
  • Integration with Google Apps (org.openbravo.integration.google)
  • Print and Send (com.openbravo.gps.printandsend)

A new version of each one of them should be published, adapting the code if required, removing the duplicated libraries and a new dependecy with core should be established for each one of them.

Performance Requirements

The only performance requirement of this project is just to make the library upgrade transparent in terms of report rendering time, i.e., after the library upgrade the time spent to render the reports should be, at least, equal to the time spent before the upgrade.