Projects:Improved Balance Sheet

Contents 1 Improved Balance Sheet and P&L Structure Report 1.1 Introduction 1.2 New module 1.3 Development implementation 1.4 Functional Changes 1.4.1 Optional Reference Year [Must have] 1.4.2 Changed in PDF output [Must have]

Improved Balance Sheet and P&L Structure Report

Introduction

This document describes the new improved Balance Sheet and P&L Structure report based on the standard report available in Openbravo ERP.

New module

This new report will be distributed in a new public module:

• Java Package: org.openbravo.report.improved.balancesheet
• Name: Improved Balance Sheet and P&L Structure Report
• Type: Module
• Description: Improved Balance Sheet and P&L Structure Report
• Module Language: English (USA)
• License Type: Openbravo Public License
• License Text: Licensed under the Openbravo Public License Version 1.1.
  You may obtain a copy of the License at http://www.openbravo.com/legal/license.html or in the legal folder of the Openbravo ERP core distribution.
• Author: Openbravo S.L.U.
• Dependency: Core 3.0PR15Q1.4

Development implementation

The module will be based on the code already available in Core module. That means that the Java/HTML/XML/JRXML related to the standard Balance Sheet report will be copied from the PI repo to the new module. All the improvements will be exclusively done in the module; Core's code won't be modified.

An instance with this module installed, will be able to launch the standard report and the improved one. The report included in this module will be called “Improved Balance Sheet and P&L Structure Report”

Files to be moved to the module:

• src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports_data.xsql
• src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java
• src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.xml
• src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.html
• src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReportsPDF.jrxml

Please note that:

1. The internal references in the files should be adapted according to the new location. For example the java package name of the Java files
2. New references in the Application Dictionary should be created in the module to be able to launch the new report (Menu, Process, Messages, etc.)
3. Since HTML and JRXML code will be included in the module, new entries in AD_TextInterfaces table will be automatically created by the compilation process

Functional Changes

There are 2 separate improvement areas:

Optional Reference Year [Must have]

In the Core's report, you are forced to launch the report comparing always with another year. Sometimes you don't need that comparation, and it makes the report harder to understand.

A new flag, called Compare To, will be available when launching the report to include the comparation or not:

By default the flag will be set to Yes. When unchecked, the fields Reference Year and As of Reference Date will be automatically hidden.

Note that the Reference Year field is set as mandatory in the HTML. This required attribute must be now controlled based on the new Compare To flag: if checked, then the Reference Year will be required, otherwise, this field will be hidden and set as not required. Besides, the validate() JS function should be adapted accordingly.

When this parameter is set to Y, the select queries available in the XSQL file will avoid querying the database for the reference data in this case. The easiest way to implement it is to add an optional where clause “and 1=2” (based on the Compare To value) to the select query in charge of getting the reference data. Affected methods: AccountTreeData.selectAcct() and AccountTreeData.selectFactAcct()

Changed in PDF output [Must have]

The changes in the PDF output are purely esthetic:

1. Total Amounts won't appear at the top of the line anymore, but as a new line just under the summed amounts. The new line will be called Total + <Account Name> and it will have the same indentation level as its header
2. A new separation line will appear just above a total amount. For the highest level total amounts (example Total Assets), it will be a bold line
3. Same font size will be used in the report, regardless the entry level or indentation
4. An indentation of 2 characters will be used per level
5. Negative amounts will be displayed in parenthesis. Example: -5 will be displayed as (5)
6. Zero amounts will be displayed with the symbol -

This is the output of the standard Openbravo report:

And this is the expected output for a simple report (3 indentation levels):

Another example of the new format, in this case for a full report (4 indentation levels):

From a technical point of view, the easiest way to implement it is by modifying the data that is built in the Java just before sending it to the Jasper report render. A way to do it is:

1. Detect the top level lines and only for them remove the qty and qtyRef amounts (or set them to 0). So the total amount won't appear anymore at the beginning of the group.
2. Manually add the summary level line just when a change in the elementLevel has taken place, including the total qty and qtyRef amounts. It could be useful to add a new parameter to the record informing that it's a “total amount line”, so the output format could be controlled in the Jasper template (set the bold fonts, bold upper line, etc.)

Everything should be done at the loop where the data that is going to be passed to the Jasper render is built (GeneralAccountingReports.java):

for (int i = 0; i < trees.length; i++) {
          for (int j = 0; j < trees[i].length; j++) {
            HashMap<String, String> hashMap = new HashMap<String, String>();
            hashMap.put("elementLevel", trees[i][j].elementLevel);
            hashMap.put("name", trees[i][j].name);
            hashMap.put("qty", trees[i][j].qty);
            hashMap.put("qtyRef", trees[i][j].qtyRef);
            hashMap.put("groupname", strGroups[i].name);
            hashMap.put("pagebreak", strGroups[i].pagebreak);
 
            hashMapList.add(hashMap);
          }
}

Category: Projects ERP