Openbravo POS Reports and Charts Tutorial
Contents |
Introduction
This guide refers to the Openbravo POS 2.30 version and next versions. It aims to show to developers of Openbravo POS how to create and maintain reports and how to add these reports to the Openbravo POS menu. Instruction for 2.10-2.20 versions are included below.
A new structure for reporting was introduced in Openbravo 2.10 that makes it easier to create and modify reports. With this new structure developers of Openbravo POS will be able to add and modify easily new reports and charts without having to deal with the source code.
Reports are now defined using plain text files that developers can edit using his preferred text editor or a graphical designer. To install newly created reports is as simple as copy the report definition files to the reports folder and create a menu option for the report. All reports and charts of Openbravo POS has been ported to the new structure and new reports have been created.
The reports architecture in Openbravo ERP
Report files are stored in the reports sub folder of the installation folder of Openbravo POS. Inside this folder, reports can be organized in packages that is nothing else than a tree directory structure. For example the reports included in the base distribution of Openbravo POS are in the package com.openbravo.reports that means the subfolder com/openbravo/reports/.
A report definition in Openbravo POS consist in three or more files: The configuration file, the report file and the translation files.
- The configuration file is the most important one, here you define the title, the SQL sentence to get the report data, parameters, fields, what is the report file and what is the translations file.
- Openbravo POS uses the JasperReports engine to run the reports and the report file is the JasperReports file. You can edit this file with your preferred text editor or any available graphical tool for JasperReports like iReport.
- The translations file is used to define literals that need to be translated.
The definition file
The definition file is a file with extension .bs and is described using the BeanShell scripting language. This language is very similar to java or javascript and you will not need special skills to define a new Openbravo POS report.
This is the products.bs file that describes the Products list report
report = new com.openbravo.pos.reports.PanelReportBean();
report.setTitleKey("Menu.Products");
report.setReport("/com/openbravo/reports/products");
report.setResourceBundle("com/openbravo/reports/products_messages");
report.setSentence(
"SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, " +
"PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, " +
"(PRODUCTS.PRICESELL * (1 + TAXES.RATE)) AS PRICESELLTAX, " +
"TAXES.NAME AS TAXNAME, TAXES.RATE AS TAXRATE, PRODUCTS.CATEGORY, " +
"CATEGORIES.NAME AS CATEGORYNAME " +
"FROM PRODUCTS LEFT OUTER JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID " +
"LEFT OUTER JOIN TAXES ON PRODUCTS.TAX = TAXES.ID " +
"WHERE ?(QBF_FILTER) " +
"ORDER BY CATEGORIES.NAME, PRODUCTS.NAME");
report.addParameter("PRODUCTS.NAME");
report.addParameter("PRODUCTS.PRICEBUY");
report.addParameter("PRODUCTS.PRICESELL");
report.addParameter("PRODUCTS.CATEGORY");
report.addParameter("PRODUCTS.CODE");
report.addQBFFilter(new com.openbravo.pos.ticket.ProductFilter());
report.addField("ID", com.openbravo.data.loader.Datas.STRING);
report.addField("REFERENCE", com.openbravo.data.loader.Datas.STRING);
report.addField("CODE", com.openbravo.data.loader.Datas.STRING);
report.addField("NAME", com.openbravo.data.loader.Datas.STRING);
report.addField("PRICEBUY", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("PRICESELL", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("PRICESELLTAX", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("TAXNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("TAXRATE", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("CATEGORY", com.openbravo.data.loader.Datas.STRING);
report.addField("CATEGORYNAME", com.openbravo.data.loader.Datas.STRING);
Definition of the bean report object
report = new com.openbravo.pos.reports.PanelReportBean();
VERSION NOTE: In version 2.10 - 2.20 the keyword bean was used, in version 2.30 this had been changed to the keyword report in the .bs definition file
This line creates the report object. The variable name must be called report because this is the variable name Openbravo POS will use as the report definition, and the object created is one instance of the class com.openbravo.pos.reports.PanelReportBean that is the base class for all Openbravo POS reports. You will not need to modify this line in the reports you create.
Report properties
report.setTitleKey("Menu.Products");
report.setReport("/com/openbravo/reports/products");
report.setResourceBundle("com/openbravo/reports/products_messages")
These lines set the title of the report, the JasperReports .jrxml file and the localization properties file. To set the title of the report you have two methods .setTitleKey() where the parameter is a key that is localized in the standard localization Openbravo POS files and .setTitle() where the parameter is just the title of the report.
Report sentence
report.setSentence( "SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, " + "PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, " + "(PRODUCTS.PRICESELL * (1 + TAXES.RATE)) AS PRICESELLTAX, " + "TAXES.NAME AS TAXNAME, TAXES.RATE AS TAXRATE, PRODUCTS.CATEGORY, " + "CATEGORIES.NAME AS CATEGORYNAME " + "FROM PRODUCTS LEFT OUTER JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID " + "LEFT OUTER JOIN TAXES ON PRODUCTS.TAX = TAXES.ID " + "WHERE ?(QBF_FILTER) " + "ORDER BY CATEGORIES.NAME, PRODUCTS.NAME");
This line defines the SQL sentence used by the report. Pay attention to the ?(QBF_FILTER) tag that defines the location where Openbravo POS will include the SQL filter generated with the parameters declaration of the report.
Report parameters
report.addParameter("PRODUCTS.NAME");
report.addParameter("PRODUCTS.PRICEBUY");
report.addParameter("PRODUCTS.PRICESELL");
report.addParameter("PRODUCTS.CATEGORY");
report.addParameter("PRODUCTS.CODE");
report.addQBFFilter(new com.openbravo.pos.ticket.ProductFilter());
These lines defines the report parameters and the relation between the SQL sentence fields and the interface shown to the user to edit the parameter values.
There are several predefined QBF (Query by form) filters predefined in Openbravo POS and each QBF filter required a fixed num of parameters. You can combine more than one QBF filter. You only need to maintain the same order in the parameters and in the filters. This is an example of three QBF filter with its parameters definition in order:
report.addParameter("STOCKDIARY.DATENEW");
report.addParameter("STOCKDIARY.DATENEW");
report.addQBFFilter(new com.openbravo.pos.reports.JParamsDatesInterval());
report.addParameter("LOCATIONS.ID");
report.addQBFFilter(new com.openbravo.pos.reports.JParamsLocationWithFirst());
report.addParameter("STOCKDIARY.REASON");
report.addQBFFilter(new com.openbravo.pos.reports.JParamsReason());
- com.openbravo.pos.ticket.ProductFilter
This filter is used to filter products and requieres five field parameters: the name, the buy price, the sell price, the category ID and the barcode of the product.
- com.openbravo.pos.reports.JParamsDatesInterval
This filter is used to select a dates period, it requires two date fields, usually the same field.
- com.openbravo.pos.reports.JParamsLocation, com.openbravo.pos.reports.JParamsLocationWithFirst
These filters are used to select the warehouse location. It requires the location ID field. The difference between both filters is that with JParamsLocation is mandatory to select a warehouse location and with JParamsLocationWithFirst is not.
- com.openbravo.pos.reports.JParamsReason
This filter is used to select a warehouse diary movement reason. It requires the reason field.
- com.openbravo.pos.reports.JParamsCustomer
This filter is used to select a customer. It requires the customer ID and the customer name fields.
- com.openbravo.pos.reports.JParamsText
This is a generic filter for a field. It supports three constructors: JParamsText(String lable) That defines the title label and filters any string field. JParamsText(String label, Formats format) That defines the title label and the format used to display the value. And JParamsText(String label, Formats format, Datas data) that defines the title label, the format used to display the value and the field data type.
The following Formatsare available:
Formats.INT Formats.DOUBLE Formats.CURRENCY Formats.PERCENT Formats.DATE Formats.TIME Formats.TIMESTAMP Formats.BOOLEAN Formats.STRING
And the following Datas
Datas.INT Datas.DOUBLE Datas.TIMESTAMP Datas.BOOLEAN Datas.STRING
Report fields
report.addField("ID", com.openbravo.data.loader.Datas.STRING);
report.addField("REFERENCE", com.openbravo.data.loader.Datas.STRING);
report.addField("CODE", com.openbravo.data.loader.Datas.STRING);
report.addField("NAME", com.openbravo.data.loader.Datas.STRING);
report.addField("PRICEBUY", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("PRICESELL", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("PRICESELLTAX", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("TAXNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("TAXRATE", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("CATEGORY", com.openbravo.data.loader.Datas.STRING);
report.addField("CATEGORYNAME", com.openbravo.data.loader.Datas.STRING);
These lines defines the name of the report fields, and the report field types. These fields must match with the SQL returned fields.
The following Datas are available:
Datas.INT Datas.DOUBLE Datas.TIMESTAMP Datas.BOOLEAN Datas.STRING Datas.BYTES Datas.IMAGE Datas.OBJECT Datas.SERIALIZABLE Datas.NULL
The report file
The report file is a file with extension .jrxml with JasperReport syntax. You can create and edit report files using your favourite text editor but it is easier to use a graphical editor like iReport.
There is a limitation running reports inside Openbravo POS. The only data set used to populate the report is the data set generated in the definition file and for this reason there is not possible to use other data sets for example to populate subreports.
Running reports inside Openbravo POS you will have access to utility functions that helps with the reports definition development:
Localization
The localization bundles are passed to the report engine and can be used like this:
$R{label.title}
Formating values
You can format values using the Format class of Openbravo POS to format values using the formating configuration of Openbravo POS. For example:
com.openbravo.format.Formats.CURRENCY.formatValue($F{PRICESELLTAXDIFF})
Images included
You can access to the images included in the binaries of Openbravo POS using the ImageUtil class. For example:
com.openbravo.data.loader.ImageUtils.readImageFromResource("/com/openbravo/images/poweredby.png")
Argument values access
All the parameter values are passed to the report in the ARGS report parameter as an Object array.
Barcode creation
You can create barcode images using the following methods.
For EAN-13 barcodes:
com.openbravo.pos.util.BarcodeImage.getBarcodeEAN13($F{CODE})
For Code 128 barcodes:
com.openbravo.pos.util.BarcodeImage.getBarcodeCODE128($F{CODE})
The localization file
The localization file is a properties file and uses the same syntax and structure as the rest of localization files in Openbravo POS. Here there is a guide how to localize Openbravo POS OpenbravoPOS_Localization