View source | Discuss this page | Page history | Printable version   

How to create an XLS Report with Formulas

Contents

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:

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.png
XlsReportResult.png

Retrieved from "http://wiki.openbravo.com/wiki/How_to_create_an_XLS_Report_with_Formulas"

This page has been accessed 6,560 times. This page was last modified on 28 July 2015, at 07:07. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.