Session 5.1 Reporting

How to create a Report

Objetive

The goal of this how to is to describe the steps required to create a new report in Openbravo 3. The example explained is a simple report with a list of products.

Acknowledgments

Before starting make sure that you have read the prerequisite knowledge article. And you are confident with Openbravo modularity concepts

Setting up Jaspersoft Studio

Jaspersoft Studio design client is supported since 3.0PR15Q3. For older releases check this section.

You need to download Jaspersoft Studio, a graphical tool that allows you create and modify JasperReports templates (.jrxml files).

• Download Jaspersoft Studio version 6.0.0 (v6.0.0). If using a higher version then it is necessary to ensure that the reports are generated for the JasperReport 6.0.0 version. More info can be found here.
• On Linux: just download the .tgz file and uncompress it, execute the binary Jaspersoft Studio located inside the main folder
• On Windows: Download and execute the .exe file

Configuring Jaspersoft Studio

Some properties of Jaspersoft Studio need to be modified in order to work properly. In short, you need to make sure:

• You modified the JasperReport property net.sf.jasperreports.awt.ignore.missing.font and set it to true
• Not use any Scriplet class
• Use Java as default expression language

Jasperreport can be used in hw manager, but the version should be 1.0.2801 or higher

Setting up Classpath

In Jaspersoft Studio, each report is supposed to be part of a project. So, you first need to create a new project (File > New > Project).

The project has a classpath, and here is where you can add the jars you need.

• Right-click on the project name: Properties > Java Build Path
• Move to Libraries tab
• Click Add External Jars button
• Add the desired library.
• Click OK

Creating the Template

• Go to File > New
• Pick Jasper Report
• The New Report Wizard will be opened

• Select a Report Template, (Blank following our example)
• Define a Report Name
• Define the file Location in the project.

Later on we will copy this .jrxml file inside our Openbravo module that is going to keep our Report and the required configuration in the Application Dictionary also.

• Define the Report Data Source: by clicking on "New", a new database connection can be configured using the Data Adapter Wizard
• Click New
• Pick Database JDBC Connection and click Next
• Fill all the fields
  • Name: openbravo (or any name you like, e.g. pi)
  • JDBC Driver: PostgreSQL (org.postgresql.Driver). In this case we'll use PostgreSQL
  • JDBC URL: jdbc:postgresql://localhost:5432/openbravo where 5432 is the port where PostgresSQL is running and openbravo is the SSID of our database
  • Username: tad (you can check your username/password in Openbravo.properties configuration file)
  • Password: tad
• Click Finish button to generate the JDBC Connection
• Test your connection
• Save

Now we have to configure the query: we are going to list the products present in the database.

• Right-click on the Report Outline menu, and select Dataset and Query. Here is where we have to set the query of the report and it is also possible to switch between the available database connections in case we want to test the query.

• The products are stored in the M_Product table

  SELECT m_product_id, value, name FROM m_product

• We have to add the fields based on your query which we want to use in the report, so we are going to add:
  • m_product_id
  • value
  • name

• Click OK
• Remember to clear the Scriptlet class and modify the Language for expressions
• Right-click on the Report Outline menu, and select Show Properties.
• In the report properties in the right, look for the following:
  • Clear the Scriptlet class
  • Choose Java as Language
• Save your changes

Let's now design the Report Layout

• Put a static text as report title: Product List
• Place the fields in the Detail band and a title in the Column Header band
• Save your changes

• Switch to the Preview subtab to get a report preview

Note: It is recommended to use Dejavu fonts in jasper reports because this fonts support most of the characters in almost all languages. Besides, Dejavu typography is the family of fonts that Openbravo included in jasperreports-fonts library.

Openbravo Runtime Environment

The standard reports in Openbravo (src/org/openbravo/erpReports) make use of several methods that reside inside the Openbravo Runtime Environment, which cannot be executed at design time. For this reason, we provide the following .jar file that encapsulates the following adapted methods of the org.openbravo.erpCommon.utility.Utility Class:

• public static BufferedImage showImageLogo: returns a logo image that is already included in the JAR archive
• public static String applyCountryDateFormat: always returns the date formatted in this pattern dd-MM-yyyy
• public static DecimalFormat getCountryNumberFormat: just returns the same DecimalFormat received as parameter

For this, to be able to preview the standard Openbravo reports from JasperStudio, it suffices just to import the .jar file into the classpath of the project within JasperStudio.

Adding images and logos to a report using the ShowImage API

You can use the Image BLOB reference to display an image for a specific report, or one of the Company logos in the application. First you need to add an image object to your report, set the expression class to "java.awt.Image" and the expression image to a call to the ShowImage function of the Utility class (if you want it to display a standard ImageBLOB image reference, that corresponds to a field added to a tab), or to the ShowImageLogo function if you want to display the logo of an Organization or Client.

Images loaded with this method must not have alpha channel. A transparency layer is not supported by the function that loads images in Jasper Reports.

• If you want to use the ShowImage function, you need to make the expression image look like:
  • org.openbravo.erpCommon.utility.Utility.showImage("IMAGEID")

IMAGEID needs to be the UUID of the image you want to show. You could set this value using a Jasper parameter.

• If you want to use the ShowImageLogo function to show one of the logos, you have several options.
  • This one will show the Company logo at System level:
    • org.openbravo.erpCommon.utility.Utility.showImageLogo("yourcompanylogin")
  • This one will show the Company log at Client level (the client used will be the one the user logged at):
    • org.openbravo.erpCommon.utility.Utility.showImageLogo("yourcompanymenu")
  • This one will show the Company logo at Organization level:
    • org.openbravo.erpCommon.utility.Utility.showImageLogo("yourcompanydoc", "ORGANIZATIONID")

ORGANIZATIONID needs to be the UUID of the Organization whose log you want to show. You could set this value using a Jasper argument. An example could be org.openbravo.erpCommon.utility.Utility.showImageLogo("yourcompanydoc", "4387D62C6486481AB3D148442A6AD34E") being 4387D62C6486481AB3D148442A6AD34E the organization ID.

Registering the Report in Application Dictionary

Creating the Report

This feature is available from 3.0PR15Q2

From 3.0PR15Q2 it is possible to create a report using a process definition. For more information see here.

• Using the System Administrator role
• Using the quick-launch, open: Process Definition window
  • You can find it in the menu: Application Dictionary > Process Definition
• Create a new record
• Fill all required fields
  • Module: Pick your module
  • Search Key: OBPF_ProductList (Is a best practice to start with your module's DB_Prefix)
  • Name: Product List
  • UI Pattern: Report (Using JR templates)
  • Data Access Level: Client/Organization
  • Handler: use the default org.openbravo.client.application.report.BaseReportActionHandler

We must copy the .jrxml template file generated with Jaspersoft Studio into our module. When using Process Definition to generate a Report, templates need to be stored in the web folder of the module. In our example we place it in the following location: /web/org.openbravo.platform.features/jasper

• Navigate to the Report Definition tab
• Fill the PDF template field with the location of the .jrxml file

Creating the Menu record

• Using the System Administrator role
• Open the Menu window
• Create a new record
• Fill all required fields:
  • Module: Your module
  • Name: Name of the menu entry (Product List)
  • Description: Description of the action related to the menu entry
  • Action: Pick Process Definition
  • Process Definition: Pick your Process Definition (Product List)

Compiling

After you have registered the report and menu entry in the Application Dictionary, you need to compile to generated the the necessary code.

ant smartbuild

Once the compilation has completed, refresh your Eclipse project, and restart your tomcat server.

Testing the Report

If you have completed all the steps, you should be able to open your Product List report form the quick-lauch, or menu entry.

Further Details

Layout

For information on how JasperReports handles the layout check Making HTML, XLS or CSV friendly reports

Also, a tutorial with the basics of how to design a report can be found here.

Configuring Cell Type in XLS Reports

Starting from 3.0PR15Q3, by default, Openbravo reporting engine exports the XLS data as strings. This is done in order ensure that the exported data can be read after opening the report with the vast majority of spreadsheet applications.

If we want to have a particular format in a cell of our XLS report, and for example, display numbers inside a numeric cell, this default configuration can be overridden at template level.

To override this configuration, the following must be done inside the .jrxml report template:

1. Add the net.sf.jasperreports.export.xls.detect.cell.type property with true as its value.
2. Add a pattern for the text field that will be displayed in the XLS cell. With <pattern> tag a fixed pattern can be set and with the <patternExpression> tag it is possible to define a dynamic pattern.

Please note that the decimal and thousands separators used for the numeric cells exported in this way, will be those defined inside the spreadsheet program itself (LibreOffice Calc, Excel,...). An example of report which applies this configuration can be found here.

Creating a Report Using Report and Process

Report and Process window was the habitual way to define reports before PR15Q2, as the Process Definition option was not available. In the following tutorial you can find an example about how to create a Report in this way.

Creating a Reports in old releases

Before 3.0PR15Q3 version, another report design client was used, called iReport.

To get more information about this and further details on reporting in old releases, you can go here.

Report Compilation

This feature is available starting from 3.0PR18Q3.

When printing a report in the application, it is previously compiled at runtime. The result of this report compilation is cached if there are no modules in in development status.

Besides, it is possible to handle the state of this cache through a JMX extension. Thus, this extension allows to:

• See if the cache is enabled.
• Enable/Disable the cache.
• See the list of reports whose compilation is stored in cache.
• Clear the cache contents.

How to create a Report with iReport

Objetive

The goal of this how to is to describe the steps required to create a new report in Openbravo 3. The example explained is a simple report with a list of products.

Note: iReport was the recommended design client before PR15Q3. Since PR15Q3 Jaspersoft Studio is the recommended report designer. Go here to know how to create a Report using Jaspersoft Studio.

Acknowledgments

Before starting make sure that you have read the prerequisite knowledge article. And you are confident with Openbravo modularity concepts

Setting up iReport

You need to download iReport, a graphical tool that allows you create and modify JasperReports templates (.jrxml files).

• Download iReport 4.0.1
• On Linux: just download the .tar.gz or .zip file and uncompress it, execute the binary

  bin/ireport

• On Windows: Download and execute the .exe file

Configuring iReport

Some properties of iReport need to be modified in order to work properly. This configuration settings is explained in iReport configuration In short, you need to make sure:

• You modified the JasperReport property net.sf.jasperreports.awt.ignore.missing.font and set it to true
• Not use any Scriplet class
• Use Java as default expression language

Setting up Classpath

• Open from the menu options: Tools > Options
• Move to Classpath tab
• Click Add Jar button
• Go to your Openbravo sources folder. Under lib/runtime folder, search for postgresql*jdbc*.jar and pick it.
• Click OK

Defining a Database Connection

• Click the Report Datasources icon
• Click New
• Pick Database JDBC Connection and click Next
• Fill all the fields
  • Name: openbravo (or any name you like, e.g. pi)
  • JDBC Driver: PostgreSQL (org.postgresql.Driver)
  • Server Address: your database server address, e.g. localhost
  • Database: pi (or the name of your database)
  • Username: tad (you can check your username/password in Openbravo.properties configuration file)
  • Password: tad
• Click Wizzard button to generate the JDBC URL
• Test your connection
• Save

Creating the Template

• File > New
• Pick Report
• Click Launch Report Wizzard

• Define a Report Name
• Define the file Location. Notice that your .jrxml template must be placed under the src folder of your module. More information on the project folder structure. A common convention is to place your reports in a ad_reports folder, e.g. modules/org.your.java.package/src/org/your/java/package/ad_reports. This is a convention, not a requirement, but you need to remember where you placed your template.

• Define the query to extract the data
• Pick the report datasource previously defined
• The products are stored in the M_Product table

  SELECT m_product_id, value, name FROM m_product

• Click Next

• iReport define the fields based on your query
• Pick all the fields
• Click Next

• Rember to clear the Scriptlet class and modify the Language for expressions
• Window > Report Inspector
• In the report properties in the right, scroll down to More section
  • Clear the Scriptlet class
  • Choose Java as Language
• Save your changes

• Put a static text as report title: Product List
• Place the fields in the Detail band and a title in the Column Header band
• Save your changes

• Click the Preview button to get a report preview

Note: It is recommended to use Dejavu fonts in jasper reports because this fonts support most of the characters in almost all languages. Besides, Dejavu typography is the family of fonts that Openbravo included in jasperreports-fonts library.

Registering the Report in Application Dictionary

Creating the Report

• Using the System Administrator role
• Using the quick-launch, open: Report and Process window
  • You can find it in the menu: Application Dictionary > Report and Process
• Create a new record
• Fill all required fields
  • Module: Pick your module
  • Search Key: SMPLRProductList (Is a best practice to start with your module's DB_Prefix)
  • Name: Product List
  • Data Access Level: Client/Organization
  • UI Pattern: Standard
  • Jasper Report: Check
  • JR Template name:

    @basedesign@/org/openbravo/examples/report/ad_reports/ProductList.jrxml

    • @basedesign@ is a constant that you must use always, after that, starting with a slash you need to write the path to your jrxml template.

Note: Report checkbox must be unchecked

Creating the Menu record

• Using the System Administrator role
• Open the Menu window
• Create a new record
• Fill all required fields:
  • Module: Your module
  • Name: Name of the menu entry (Product List)
  • Description: You must enter a description. Although is not mark as required, it is for this type of reports
  • Action: Pick Process
  • Process: Pick your process (Product List)

Compiling

After you have registered the report and menu entry in the Application Dictionary, you need to compile to generated the the necessary code.

ant smartbuild

Once the compilation has completed, refresh your Eclipse project, and restart your tomcat server.

Testing the Report

If you have completed all the steps, you should be able to open your Product List report form the quick-lauch, or menu entry.

Further Details

Layout

For information on how JasperReports handles the layout check Making HTML, XLS or CSV friendly reports

Creating a Report Using a Process Definition

This feature is available from 3.0PR15Q2

From 3.0PR15Q2 it is possible to create a report using a process definition. For more info see here.

How to create a Report using Process Definition

Introduction

This how to will add a new Report using a Process Definition and create a menu entry to invoke it.

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

• How to create a Standard Process Definition
• How_to_create_a_Report to build the Jasper Report templates.

This feature 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 Product Simple Report and prints in PDF a list of Products that can be filtered by Product Category.

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

Report Definition in Application Dictionary

Process Definition

Create a new record in Process Definition window. Fields to consider:

UI Pattern

Set Report (Using JR Templates). This value will display the Report Definition tab.

Action Handler

If none is set when the UI Pattern is selected the org.openbravo.client.application.report.BaseReportActionHandler is set.

The BaseReportActionHandler is the default action handler to use in reports. In case you desire to make some Java validations or include some extra parameters that are not defined in the Process Definition parameters tab it is possible to use a custom Action Handler that extends the BaseReportActionHandler.

Parameter Definition

In the Parameter tab are added all the parameters that are needed to filter the results of the report. Their values are handled by the BaseReportActionHandler and sent to Jasper Reports as parameters. This parameters need to be defined in the JR template with the same name as the column name.

When the filter parameter is a Selector reference the value is sent as a JSONOBject that includes 2 keys:

• value with the id of the selected BaseOBObject to be used in the SQL query
• identifier with the readable identifier that can be used to print in the report the filter values.

In case of Multiple Selectors the JSONObject includes 4 keys:

• values with a JSONArray including all the selected ids.
• identifiers with a JSONArray including all the identifiers.
• strValues with a comma separated String with all the selected ids that can be used in a SQL IN clause.
• strIdentifiers with a comma separated String with all the selected identifiers.

In the Jasper Template the parameter has to be defined using the class org.codehaus.jettison.json.JSONObject. In the example it is set a Product Category multiple selector. As this parameter is optional, the filter is included in the query using an auxiliary parameter (AUX_Product_category). The auxiliary parameter has a default expression that returns " 1 = 1 " when there is no category selected and the corresponding where clause when some categories are selected:

("".equals($P{M_Product_Category_ID}.getString("strValues"))) ? " 1 = 1 " : " pc.m_product_category_id IN ("+$P{M_Product_Category_ID}.getString("strValues")+")"

This parameter is then included in the query using the "$P!{}" notation to replace it with the parameter value instead of using SQL Parameters.

FROM m_product p
  JOIN m_product_category pc ON p.m_product_category_id = pc.m_product_category_id
WHERE $P!{AUX_Product_category}
  AND p.ad_client_id = $P{Current_Client_ID}

The identifiers can be shown in a Text field with the following Expression:

$P{M_Product_Category_ID}.getString("strIdentifiers")

The BaseReportActionHandler and the ReportingUtils class used to generate the report includes some additional parameters that can be used in the template:

SUBREPORT_DIR

The path where the main template is located. Useful to set the paths of the subreports.

jasper_process

A org.openbravo.client.application.Process object with the Process Definition of the report.

jasper_hbSession

A org.hibernate.Session object with the current hibernate session.

jasper_obContext

A org.openbravo.dal.core.OBContext with the OBContext that has launched the report.

AMOUNTFORMAT

A java.text.DecimalFormat object with the format to be used on Amounts.

QUANTITYFORMAT

A java.text.DecimalFormat object with the format to be used on Quantities.

REPORT_FORMAT_FACTORY

JR Base parameter with the date format.

Current_Client_ID

String with the current AD_Client_ID. Useful to filter the SQL of the report.

Readable_Organizations

Comma separated String with the readable organizations of the User/Role executing the report. Useful to filter the SQL of the report.

Depending on the report output some additional parameters are set:

PDF output:

IS_IGNORE_PAGINATION

with false value to ensure that there report is break in different pages.

XLS output:

IS_IGNORE_PAGINATION

with true value to ensure that there report is not break in different pages and all the results are shown in the same sheet.

In case it is desired to add more parameters that cannot be defined as Parameters of the Process Definition it is possible to use a custom Handler that extends the BaseReportActionHandler and overwrites the addAdditionalParameters method.

It is possible to check all the parameters and values sent to the Jasper Report engine by enabling the DEBUG log level on the org.openbravo.client.application.report.ReportingUtils class by modifying the log4j.lcf file:

DEBUG org.openbravo.client.application.report.ReportingUtils - list of parameters available in the jasper report
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: SUBREPORT_DIR value: /home/gorkaion/src/openbravo/pi-reporting-merge/WebContent/web/org.openbravo.platform.features/jasper/
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: Current_Client_ID value: 23C59575B9CF467C9620760EB255B389
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: REPORT_FORMAT_FACTORY value: org.openbravo.erpCommon.utility.JRFormatFactory@14ffa3fc
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: REPORT_VIRTUALIZER value: net.sf.jasperreports.engine.fill.JRSwapFileVirtualizer@1b670029
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: jasper_process value: OBUIAPP_Process(70889433974B409BAC4F9D7BFB211248) (name: Product Simple Report)
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: M_Product_Category_ID value: {"values":["291B401A38354A2C8247DFF0DFBDF4AE","5525FCABFE9545018EE221E8802AA283"],"identifiers":["Bio","Fruit juice"],"strValues":"'291B401A38354A2C8247DFF0DFBDF4AE', '5525FCABFE9545018EE221E8802AA283'","strIdentifiers":"Bio, Fruit juice"}
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: AMOUNTFORMAT value: java.text.DecimalFormat@674dc
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: QUANTITYFORMAT value: java.text.DecimalFormat@674dc
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: Readable_Organizations value: 'E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43'
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: jasper_obContext value: org.openbravo.dal.core.OBContext@73b91cd
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: jasper_hbSession value: SessionImpl(PersistenceContext[entityKeys=...
DEBUG org.openbravo.client.application.report.ReportingUtils - parameter name: IS_IGNORE_PAGINATION value: false

Report Datasource Definition

This feature is available starting from 3.0PR17Q1.

There exists two ways of defining the data to be displayed by the report:

1. Defining a query in the jrxml template
2. Providing a datasource to the jrxml template

In case of the second approach, we need to use a custom Handler that extends the BaseReportActionHandler and overwrites the getReportData method. This method receives a parameter map as an argument that contains:

• The parameters of the HTTP request
• The parameters available inside the Jasper Report. They are available through another map that can be accessed by using the JASPER_REPORT_PARAMETERS key.

 
  protected JRDataSource getReportData(Map<String, Object> parameters) {
    // Retrieve the report id (HTTP request parameter)
    String reportId = (String) parameters.get("reportId");
    // retrieve the map of JR Parameters
    Map<String, Object> jrParameters = (Map<String, Object>) parameters
        .get(JASPER_REPORT_PARAMETERS);
    // Here goes the logic to generate the JRDataSource
    ...
  }

This allows to generate the report data dynamically, i.e, based on some kind of logic built with the value of any of these parameters.

Sub-Report Runtime Compilation

This feature is available starting from 3.0PR17Q1.

In case our process definition report contains sub-reports, the infrastructure allows to compile the sub-reports at runtime. In that case we will need to:

1. Use a custom Handler that extends the BaseReportActionHandler and overwrites the isCompilingSubreports method. This method will return true.
2. The parameter name for the sub-report in the main report will follow this pattern: SUBREP_name_of_the_sub_report_file.
3. The sub-reports (jrxml files) must be placed in the same folder as the main report.

Report Definition

In the Report Definition tab are defined the JR Templates of the report. Each Process Definition can only have a Report definition. It is also needed to define at least one template: HTML, PDF or Excel.

The HTML output is available since version 3.0PR17Q1.

• If PDF and Excel outputs share the same template, it is possible to set the PDF template and check the Use PDF as Excel Template flag.
• If PDF and HTML outputs share the same template, it is possible to set the PDF template and check the Use PDF as HTML Template flag.
• If HTML, PDF and Excel outputs share the same template, then the PDF template must be set and both flags must be checked.

The templates need to be stored in the web folder.

Result

The result is shown in a new form with all the parameters and the corresponding Export button(s).

Once the report is generated a file download is requested in the browser. The form is kept enabled so it is possible to rerun the report with different parameters.

Additional Information

An example of XLS Report defined with a Process Definition can be found here.

An example of a Process Definition Report using the HTML output (displayed in a tab) can be downloaded from this repository.