Report Example
Contents |
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: