Log in / create account
Openbravo.com
Issues
Forge
University
View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Add page
Show collection (0 pages)
Collections help

Search

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

Ob3-report-example1.png

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:

You should be able to see the report definition:

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

Parameters

Business Partner

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

Bulbgraph.png   The DB Column Name must match exactly the parameter name defined in the template. It is case sensitive.
Ob3-example-report2.png

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

Ob3-example-report3.png

Standard UI Pattern

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

Ob3-example-report4.png
  1. Is the Help/Comment Report definition
  2. The Element defined for each parameter
  3. 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.

Ob3-example-report5.png

Parameters

Ob3-example-report6.png

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

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. 

$P{C_BPartner_ID}.equals("") ? " " : " AND C_ORDER.C_BPARTNER_ID = '" + $P{C_BPartner_ID} +"'"
$P{M_Warehouse_ID}.equals("") ? " " : " AND C_ORDER.M_WAREHOUSE_ID = '" + $P{M_Warehouse_ID} + "'"
$P{C_Project_ID}.equals("") ? " " : " AND C_ORDER.C_PROJECT_ID = '" + $P{C_Project_ID} + "'"
($P{DateFrom} == null || $P{DateFrom}.equals("")) ? "" : "AND C_ORDER.DATEORDERED >='" + new java.sql.Date($P{DateFrom}.getTime()).toString() + "'"
($P{DateTo} == null || $P{DateTo}.equals("")) ? "" : "AND C_ORDER.DATEORDERED <='" + new java.sql.Date($P{DateTo}.getTime()) + "'"
Extra Parameters

There is some extra parameters 

 
($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!{} 

 
  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

Example-report7.png

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

This page has been accessed 19,938 times. This page was last modified on 11 August 2011, at 12:52. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.