Difference between revisions of "Very Slow Reports"

Latest revision as of 03:35, 28 March 2007

Improve time to generate Financial Report by removing null account elements

Summary:

The system's time to calculate and display a financial report (e.g., Balance Sheet, Income Statement) can be improved by deleting empty "Record Source" entries that contain no "Account Element" value.

Overview:

In this context, a financial report refers to one that is generated from Financial Report window. A financial report is composed of 2 elements:

1. Report Line Set which contains row data (i.e., account elements)

2. Report Column Set which contains column definition (e.g., total amount, debit, credit, etc.)

Row data in a report line set can be created by importing account elements via Import Report Line Set. When this method is used, entries are created in “Record Line” and “Record Source” tabs in Report Line Set window. However, in instances where a row data is defined to be a heading/label (i.e., it does not map to an account element), the import process creates an entry in the “Record Source” tab with an empty value in “Account Element” field. Removing these empty references has been shown to significantly improve the time it takes for the system to generate the financial report.

SQL to check if there are Report Source lines that contain no Account Element value:

 SELECT * 
 FROM PA_ReportSource 
 WHERE PA_ReportLine_ID IN(Select PA_ReportLine_ID from PA_ReportLine) 
 AND C_ElementValue_ID IS NULL;

SQL to delete Report Source lines that contain no Account Element value:

 DELETE PA_ReportSource 
 WHERE PA_ReportLine_ID IN(Select PA_ReportLine_ID from PA_ReportLine) 
 AND C_ElementValue_ID IS NULL;

-- SusyOngko - 21 Jan 2007

Remedy

From Version 3.1.5 onwards, this issue was fixed, so now when you Import the Report Lines empty values in Account Element field are not added.