ERP 2.50:Developers Guide/How to change an existing report/it

One of the most likely changes that would be required in any ERP implementation is to modify reports to meet customer needs. This "How To" article, walks through the scenario to change a report based on the modularity guidelines of Openbravo.

Contents 1 Obiettivo 2 Impostare il contesto 3 Modularità 4 Trovare il report e fare una copia nel nuovo modulo 5 Modificare e testare il report usando iReport 6 Distribuire il report su Openbravo 7 Controllare l'output finale

Obiettivo

The scenario that is presented in this article is to modify the "Purchase Order" printed from the Purchase Order Menu option. The changes that are implemented are

• change the icon in the report to the logo of the company (default document icon)
• add a column to the sub-report ( unit price ) and remove another column (size)

Along the way you will learn how to

• locate an existing report through the application dictionary
• structure the change to the report in a modular fashion
• modify the report using iReport and test it locally
• deploy it within Openbravo

Impostare il contesto

A few points to be understood before we begin

• Reports in Openbravo are of three types.They can be the output of different export options within the grid view of a standard MVC based window, a report based on the Jasper Report Rendering Engine or based on the internal XMLEngine. In this article, we will go through the steps of modifying a Jasper report.
• This article is based on Openbravo 2.50 ( and tested with build MP20).
• While the steps used in this article can be used with any data, the example used is based on the "Big Bazaar" client that is shipped with the community version of the product
• The iReport report designer tool to modify the report. For details about installing and configuring iReport check this article Creating Jasper Templates using iReport.

Modularità

All new developments must belong to a module that is not the core module. Please follow the How to create a new module section of the Modularity Developer's Manual to create a new module.

In this scenario, it is assumed that a module com.example.bigbazaar.openbravo.module has already been created and the module has been set as under "Development" and made as the default module for any changes.

Trovare il report e fare una copia nel nuovo modulo

Since this (Purchase Order) is an existing report, the new version of the report can be based on the original report that is packaged and shipped along with the core version of the product. You should not change anything that is part of the core as it would prevent upgrading the product without loosing the changes. The standard approach in Openbravo is to make a copy of the report within the development module that has already been defined ( com.example.bigbazaar.openbravo.module ). Then you can make changes to this copy without any risk of losing it when the original module ( core ) is upgraded.

To start with locate the report. To find the location of the Purchase Order Report

1. Login with an id that has access to the client administrator role. For BigBazaar, it is the Big Bazaar Admin role
2. Choose the option Financial Management || Accounting || Setup || Document Type.
3. Move to grid view and find the document Purchase Order and open it in detail view

Look at the report configuration and use it to locate the report source file (jrxml)

1. Switch to Report Templates tab and note down the Template Location and Template Filename. As a default, Template Location is set as @basedesign@/org/openbravo/erpReports and Template Filename is RptC_OrderPO.jrxml .
2. Copy the template file which would be <OB Root Directory>/src/org/openbravo/erpReports/RptC_OrderPO.jrxml to the module report source folder . If your package name is com.example.bigbazaar.openbravo.module then the module report source folder is <OB Root Directory>/modules/com.example.bigbazaar.openbravo.module/src/com/example/bigbazaar/openbravo/module/erpReports). Create the directory structure if it is not already created.
3. Copy any related subreports as well. There would be mentioned as SUBREP_<filename> in the document structure pane within iReport . In the case of Purchase Order, it has one sub-report viz RptC_OrderPO_Lines.jrxml.

Modificare e testare il report usando iReport

• Before doing this change, ensure that a proper image is associated with Default Your Company Document Image' under General Setup|Client|Client in the Information Tab.
• Make a copy of the reports in the local(development) machine.
• Open IReport and ensure that a connection is available with the DB Server against which the report is to be tested.

• First make the modifications to the main report
  • The change to be done in the main report is to change the logo that comes up. Initially the logo that is shown is the default logo (yourCompany).
  • Open the main report in iReport and view the properties of the image at the top right of the report. The image expression (in the Image tab) is shown as $P{BASE_WEB}+"/images/CompanyLogo_big.png" . Change this to new java.net.URL($P{BASE_WEB}+"/../utility/ShowImageLogo?logo=yourcompanydoc").
  • Save the report and set BASE_WEB property in iReport to the web folder within the location were openbravo is running. For instance, if openbravo is running in http://localhost/openbravo, then BASE_WEB should be http://localhost/openbravo/web.
  • The report will prompt for a DOCUMENT_ID. Use a valid order_id here. If the report is being run against a database that has the Big Baazar client then use Document_id 1002010. Verify that the appropriate document image is being shown.

• Then make the modifications to the sub report
  • Open the subreport through iReport. The change required in the sub-report is to show the unit-price instead of the size field.
  • Change the SQL in the sub-report , by changing the report query in the data menu of the sub-report as given below.The changes are related to the addition of a new field C_ORDERLINE.PRICEACTUAL which is returned as UNITPRICE

SELECT M_PRODUCT.VALUE, M_PRODUCT_PO.VENDORPRODUCTNO, M_PRODUCT.NAME, SUM(C_ORDERLINE.QTYORDERED) AS QTYORDERED, 
       C_UOM.UOMSYMBOL AS UOMSYMBOL, C_ORDERLINE.LINENETAMT AS PRICEACTUAL,
       C_ORDERLINE.PRICEACTUAL AS UNITPRICE,
          (CASE WHEN M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESET_ID IS NULL THEN '' 
                WHEN M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESET_ID='0' THEN '' 
                ELSE TO_CHAR(M_ATTRIBUTESETINSTANCE.DESCRIPTION) END) AS ATTRIBUTE,
          (CASE SUM(C_ORDERLINE.QTYORDERED)*M_PRODUCT.WEIGHT WHEN 0 THEN NULL 
                ELSE SUM(C_ORDERLINE.QTYORDERED)*M_PRODUCT.WEIGHT END) AS WEIGHT
FROM M_PRODUCT inner join C_ORDERLINE on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
               inner join C_ORDER on C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
               left join M_PRODUCT_PO on M_PRODUCT.M_PRODUCT_ID = M_PRODUCT_PO.M_PRODUCT_ID 
                                         AND M_PRODUCT_PO.C_BPARTNER_ID = C_ORDER.C_BPARTNER_ID
               left join  M_ATTRIBUTESETINSTANCE on C_ORDERLINE.M_ATTRIBUTESETINSTANCE_ID = M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID, C_UOM 
WHERE C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_ORDER.C_ORDER_ID = '1002010'
GROUP BY M_PRODUCT.NAME, M_PRODUCT_PO.VENDORPRODUCTNO, M_PRODUCT.VALUE, C_ORDERLINE.LINENETAMT, C_ORDERLINE.PRICEACTUAL,
      (CASE WHEN M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESET_ID IS NULL THEN '' 
            WHEN M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESET_ID='0' THEN '' 
            ELSE TO_CHAR(M_ATTRIBUTESETINSTANCE.DESCRIPTION) END), M_PRODUCT.WEIGHT, C_UOM.UOMSYMBOL

• • Since iReport version 3.0.0 messes with the format of the report, if Automatically Retrieve Fields is chosen in the Report Query, uncheck this option before saving the query.
  • Consequently, the new Field "UNITPRICE" will have to be manually added to the report.
  • Change the heading "Size" to "Unit Price" and the corresponding field below, to show UNITPRICE. Also to keep it conformant with the formatting requirements use the expression ($F{UNITPRICE}!=null)?$P{NUMBERFORMAT}.format($F{UNITPRICE}):new String(" ").

Distribuire il report su Openbravo

• Copy the changed jrxml files to the location within the new module
• Choose Purchase Order' document within Financial Management || Accounting || Setup || Document Type. Modify the Template Location within the Report Template tab to point to the location of the new module viz @basedesign@/com/example/bigbaazar/openbravo/erpReports
• Run the build command to compile the new module and deploy to tomcat.

ant smartbuild -Dlocal=yes -Drestart=yes  

• After the build is succcessful test the report to make sure that the changes have been properly deployed. If on BigBazzar, the document no 'PO /06 / 14' would correspond the order number that was used for testing with iReport.

Controllare l'output finale

• The final report should be something like the image on the right - showing the new BigBaazar logo and with the Unit price being displayed in the subreport instead of the size field.

• As a best-practice, it is a good idea to extract the module as an obx file and deploy to another environment, to ensure that all the changes are copied in the module.