Very Slow Reports
Improve time to generate Financial Report by removing null account elements
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.
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 from pa_reportsource where pa_reportline_id in (select pa_reportline_id from PA_ReportLine where PA_ReportLineSet_ID=1000031) AND c_elementvalue_id is null;
-- SusyOngko - 21 Jan 2007