Session 5.2 Parameters

How to change an existing Report

Objective

One of the firsts customizations required when deploying Openbravo is to modify the templates of the most common documents: Orders, Shipments and Invoices. These documents are common to customers and vendors of the client company and must follow its requirements and corporate image. This document explains how to modify these templates in a modular way taking as example the report template for Sales Orders.

Acknowledgments

Before starting make sure you read the prerequisite knowledge and modularity concepts articles.

You should read also the how to create a report article that explains some concepts and common configuration you must follow in order to use iReport.

Important Note: This article doesn't explain how to work with iReport

Defining the Module

You should define first a new module that contains your report modifications. For more information about creating a module read the documentation on How_To_Create_and_Package_a_Module.

As System Administrator
- Create a new record in the Module window
- Fill all required fields
- Fill all required data in child tabs: Dependency, DB Prefix, etc

Export your changes to create the module structure
```
ant export.database
```

Copying the Base Template

After exporting your changes, you'll get a new folder under the modules folder.

Create a src folder
Copy the base templates from src/org/openbravo/erpReports/C_Order*.jrxml to your module folder

Note: You should follow the Java package structure

You should end up with some structure like this one:

Changing the Template

After making a copy of the base template to your module, you're ready to make all the changes you like. For sake of simplicity, we'll just add a simple label to the report header.

Start iReport
Open the C_OrderJR.jrxml file of your module
Add a simple label: "My customization"

You can customize your template with any change you like, more fields, changing position, etc

Rebuild your changes to deploy the module structure
```
ant smartbuild
```
Reload Openbravo from tomcat Manager

Defining the Report Template at Document Level

Report templates are defined at document level. You need to change it in order to work.

As Client Admin Role
Go to: Financial Management > Accounting > Setup > Document Type

Search for Name Standard Order and open it
Open the Report Template tab and modify the Template Location field
- Use the location where your copy is located
```
@basedesign@/org/openbravo/howto/mysalesorder
```
Save the changes

Testing the changes

All the definitions required to use your customized Sales Order template has been done. There is only needed to verify that the new document template created renders the document as expected with an existing Sales Order document.

Open any Sales Order using Standard Order as document type
Print it

Reports Guide

Introduction

This example explains the 'Purchase Order Report' already present in Openbravo. Before going into details, you can test the report in the following path: Procurement Management > Analysis Tools > Purchase Order Report

Objective

The main objective of this document is to give, in a pragmatic way, a detailed explanation on how to integrate a JasperReports template into Openbravo ERP.

Implementation

Application Dictionary

Following these steps you'll be able to find the report in the Application Dictionary:

As System Administrator go to:

Application Dictionary > Report and Process
Use the filter and search for: Purchase Order Report
Select the found record

You should be able to see the report definition:

Search Key: JR_ReportPurchaseOrder
Name: Purchase Order Report
Description: Purchase Order Report
Help/Comment: Purchase Order Report JR
Active: Yes
Data Access Level: Client/Organization
- Data Access Level defines who has access to this report.
UI Pattern: Standard
- Standard or Manual. A Standard process is used to generate automatically the pop-up which will ask for the report parameters. A Manual pop-up should be created by the developer. No pop-up will be automaticaly generated the report parameters.
Report: No
Jasper Report: Yes

Notice that when you register a JasperReport based report, you don't select the Report check-box, but the Jasper Report one. These reports are handled as processes

JR Template name: @basedesign@/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml
- Defines where is located the JRXML template. @basedesign@ is a constant evaluated at run-time, and translated to something like: your_tomcat_context/src-loc/design

Parameters

Business Partner

Now we go to the Parameters tab. Report and Processes > Parameters. Select the Business Partner parameter

Name: Business Partner
Description: Anyone who takes part in daily business operations by acting as a customer, employee, etc.
Help/Comment: A Business Partner is anyone with whom you transact. This can include a customer, vendor, employee or any combination of these.
Sequence Number: 10
- Defines the parameters order. More info at Sequence Number
DB Column Name: C_BPartner_ID
- Defines the name of the parameter in the JRXML template.

The DB Column Name must match exactly the parameter name defined in the template. It is case sensitive.

Application Element: C_BPartner_ID - Business Partner
- Defines the Element used by when rendering this parameter
Reference: Search
- Defines which type of Reference you want to use when prompting for parameter values. In this case we choose Search, so it will be rendered as a 'selector'.

Reference Search Key: Business Partner
- Since we have choose Search reference, we choose the Business Partner selector.
Length: 40
- Defines the field length.

Currency, Starting Date, Ending Date, Warehouse

This parameters follow the same type of definition used on the Business Partner. The only change is the DB Column Name, and the Reference used by each one of them.

Output Type

This is a special parameter that should be present in all the Standard UI reports. Is used to specify which output you want to use: PDF, HTML, XLS

Name: Output type
Help/Comment: Defines the output format type from the available list.
Sequence Number: 70
DB Column Name: outputType
- The name of the parameter must be outputType in order to use it as
Application Element: outputType - Output type
- Defines the Element associated with this parameter
Reference: List
- Defines that we want to use a List of defined values
Reference Search Key: Output format
- This is the list of possible values on the combo: Excel, Html, PDF

Length: 20
- Defines the length of the parameter
Mandatory: Yes
- Defines that is a compulsory value, cannot be blank

Standard UI Pattern

The above parameters definition will be rendered as a pop-up window like this one:

Is the Help/Comment Report definition
The Element defined for each parameter
The Reference defined for each parameter

JasperReport Template

Openbravo 3 is shipped with JasperReports 4.0.1 version. You must use the same iReport version.

You can read the iReport official Tutorials and Help documents in the iReport Documentation page.

Open the JRXML template with iReport: Should be located under src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jxrml

Parameters

The following parameter list, matches the DB Column Name parameter's definition in the Application Dictionary

C_BPartner_ID: java.lang.String
DateFrom: java.util.Date
DateTo: java.util.Date
M_Warehouse_ID: java.lang.String
C_Project_ID: java.lang.String
C_Currency_ID: java.lang.String

Auxiliary Parameters

This auxiliary parameters are used in the report SQL definition to set an extra restriction in the WHERE clause. The logic is simple, if the parameter is null or an empty String, we should not filter, otherwise we add an extra condition.

aux_partner:

$P{C_BPartner_ID}.equals("") ? " " : " AND C_ORDER.C_BPARTNER_ID = '" + $P{C_BPartner_ID} +"'"

aux_warehouse:

$P{M_Warehouse_ID}.equals("") ? " " : " AND C_ORDER.M_WAREHOUSE_ID = '" + $P{M_Warehouse_ID} + "'"

aux_project:

$P{C_Project_ID}.equals("") ? " " : " AND C_ORDER.C_PROJECT_ID = '" + $P{C_Project_ID} + "'"

aux_DateFrom:

($P{DateFrom} == null || $P{DateFrom}.equals("")) ? "" : "AND C_ORDER.DATEORDERED >='" + new java.sql.Date($P{DateFrom}.getTime()).toString() + "'"

aux_DateTo:

($P{DateTo} == null || $P{DateTo}.equals("")) ? "" : "AND C_ORDER.DATEORDERED <='" + new java.sql.Date($P{DateTo}.getTime()) + "'"

Extra Parameters

There is some extra parameters

USER_CLIENT: java.lang.String
- Parameter to be used in the SQL query and filter by by Client
USER_ORG: java.lang.String
- Parameter to be used in the SQL query and filter by Organization
LANGUAGE: java.lang.String
- Parameter that holds the current language, e.g. en_US
NUMBERFORMAT: java.text.DecimalFormat
- Parameter used in number text-fields formatting. e.g.

 
($F{PRICELIST}!=null)?$P{NUMBERFORMAT}.format($F{PRICELIST}):new String(" ")

SQL Query

The report SQL query can be edited in the property Query Text of the report. This is the whole SQL query used to get the data.

 
SELECT DOCUMENTNO, DATEORDERED, CLIENT_NAME, SUM(QUANTITYORDER) AS QUANTITYORDER, PRICEACTUAL, CONVPRICEACTUAL,
SUM(PRICELIST) AS PRICELIST, SUM(CONVPRICELIST) AS CONVPRICELIST, PRODUCT_NAME, UOMNAME,
C_CURRENCY_SYMBOL(TRANSCURRENCYID, '0', 'Y') AS TRANSSYM,
C_CURRENCY_SYMBOL($P{C_Currency_ID}, '0', 'Y') AS CONVSYM,
C_CURRENCY_ISOSYM($P{C_Currency_ID}) AS CONVISOSYM
FROM (
SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.NAME AS CLIENT_NAME, 
SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL,
C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID},
        TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL,
SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST, 
C_CURRENCY_CONVERT(SUM(C_ORDERLINE.LINENETAMT), COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID},
        TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICELIST,
M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME,
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,
C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 
C_ORDERLINE.AD_ORG_ID AS TRANSORGID
FROM C_ORDER, C_ORDERLINE, C_BPARTNER, M_PRODUCT, C_UOM
WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
  AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
  AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
  AND 1=1  
  $P!{aux_partner}  
  $P!{aux_warehouse}
  $P!{aux_project} 
  $P!{aux_DateFrom} 
  $P!{aux_DateTo}
  AND C_ORDER.ISSOTRX = 'N'
AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT})
AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})
GROUP BY C_BPARTNER.NAME, C_ORDERLINE.PRICEACTUAL, M_PRODUCT.NAME, C_UOM.NAME, C_ORDER.DOCUMENTNO, 
C_ORDER.DATEORDERED, C_ORDERLINE.DATEORDERED,
C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID,
C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
) ZZ
GROUP BY CLIENT_NAME, PRICEACTUAL, CONVPRICEACTUAL, PRODUCT_NAME, UOMNAME, DOCUMENTNO, DATEORDERED,
TRANSCURRENCYID, TRANSDATE
ORDER BY CLIENT_NAME, DATEORDERED

Use of parameter in a Function call

 
C_CURRENCY_SYMBOL($P{C_Currency_ID}, '0', 'Y') AS CONVSYM,
C_CURRENCY_ISOSYM($P{C_Currency_ID}) AS CONVISOSYM

We use the parameter C_Currency_ID to get the currency symbol

Use of Auxiliary Parameters

 
       WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
  AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
  AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
  AND 1=1  
  $P!{aux_partner}  
  $P!{aux_warehouse}
  $P!{aux_project} 
  $P!{aux_DateFrom} 
  $P!{aux_DateTo}
  AND C_ORDER.ISSOTRX = 'N'
AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT})
AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})

As you may see there aux_* parameters are used in the WHERE clause. e.g. Let's imagine that the user the users selects a Business Partner with ID 1000000, that value will be passed to the template and the line:

$P!{aux_partner}

Will be changed to:

" AND C_ORDER.C_BPARTNER_ID = '" + $P{C_BPartner_ID} +"'"

And after evaluating the value of the parameter will be:

" AND C_ORDER.C_BPARTNER_ID = '1000000'"

So the report will be filtered by that Business Partner

Client and Organization

The report is also filtered by Client and Organization

 
AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT})
AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})

P{} vs P!{}

$P!{} this syntax is used to replace the parameter placeholder with its value as raw chunk of query
$P{} this syntax is used to treat the parameter as SQL parameter, JasperReports will prepare a statement

 
  AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT}) -- $P!{USER_CLIENT} will be replaced by a string like '10000'
  AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})

 
  C_CURRENCY_SYMBOL(TRANSCURRENCYID, '0', 'Y') AS TRANSSYM,
  C_CURRENCY_SYMBOL($P{C_Currency_ID}, '0', 'Y') AS CONVSYM, -- $P{C_Currency_ID} will be a SQL parameter C_CURRENCY_SYMBOL(?, '0', 'Y') AS CONVSYM
  C_CURRENCY_ISOSYM($P{C_Currency_ID}) AS CONVISOSYM

Final Result

Choosing PDF as Output type you should be able to get a report like this:

RAWN Technologies Ltd.

How can we help you today?

Session 5.2 Parameters Print

How to change an existing Report

Objective

Acknowledgments

Defining the Module

Copying the Base Template

Changing the Template

Defining the Report Template at Document Level

Testing the changes

Introduction

Objective

Implementation

Application Dictionary

Parameters

Business Partner

Currency, Starting Date, Ending Date, Warehouse

Output Type

Standard UI Pattern

JasperReport Template

Parameters

Auxiliary Parameters

Extra Parameters

SQL Query

Use of parameter in a Function call

Use of Auxiliary Parameters

Client and Organization

P{} vs P!{}

Final Result

How can we help you today?

Session 5.2 Parameters Print

How to change an existing Report

Objective

Acknowledgments

Defining the Module

Copying the Base Template

Changing the Template

Defining the Report Template at Document Level

Testing the changes

Introduction

Objective

Implementation

Application Dictionary

Parameters

Business Partner

Currency, Starting Date, Ending Date, Warehouse

Output Type

Standard UI Pattern

JasperReport Template

Parameters

Auxiliary Parameters

Extra Parameters

SQL Query

Use of parameter in a Function call

Use of Auxiliary Parameters

Client and Organization

P{} vs P!{}

Final Result

Related Articles