How to create an XLS Report with Formulas

Contents 1 Introduction 2 Example Module 3 Formula Definition 4 Report Definition in Application Dictionary 5 Result

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:

• How_to_create_a_Report_using_Process_Definition

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.