Window: Financial Report

Description: Maintain Financial Reports

Help: Financial Reports are the combination of a Report Column Set and Line Set.

Tab: Financial Report

Description:

Help:

**Fields**
Name	Description	Help	Technical Data
Tenant	Tenant for this installation.	A Tenant is a company or a legal entity. You cannot share data between Tenants.	PA_Report.AD_Client_ID numeric(10) Table Direct
Organization	Organizational entity within tenant	An organization is a unit of your tenant or legal entity - examples are store, department. You can share data between organizations.	PA_Report.AD_Org_ID numeric(10) Table Direct
Accounting Schema	Rules for accounting	An Accounting Schema defines the rules used in accounting such as costing method, currency and calendar	PA_Report.C_AcctSchema_ID numeric(10) Table Direct
Calendar	Accounting Calendar Name	The Calendar uniquely identifies an accounting calendar. Multiple calendars can be used. For example you may need a standard calendar that runs from Jan 1 to Dec 31 and a fiscal calendar that runs from July 1 to June 30.	PA_Report.C_Calendar_ID numeric(10) Table Direct
Name	Alphanumeric identifier of the entity	The name of an entity (record) is used as an default search option in addition to the search key. The name is up to 60 characters in length.	PA_Report.Name character varying(60) String
Description	Optional short description of the record	A description is limited to 255 characters.	PA_Report.Description character varying(255) String
Active	The record is active in the system	There are two methods of making records unavailable in the system: One is to delete the record, the other is to de-activate the record. A de-activated record is not available for selection, but available for reports. There are two reasons for de-activating and not deleting records: (1) The system requires the record for audit purposes. (2) The record is referenced by other records. E.g., you cannot delete a Business Partner, if there are invoices for this partner record existing. You de-activate the Business Partner and prevent that this record is used for future entries.	PA_Report.IsActive character(1) Yes-No
Report Line Set			PA_Report.PA_ReportLineSet_ID numeric(10) Table Direct
Report Column Set	Collection of Columns for Report	The Report Column Set identifies the columns used in a Report.	PA_Report.PA_ReportColumnSet_ID numeric(10) Table Direct
Report Cube	Define reporting cube for pre-calculation of summary accounting data.	Summary data will be generated for each period of the selected calendar, grouped by the selected dimensions..	PA_Report.PA_ReportCube_ID numeric(10) Table
Exclude Adjustment Periods			PA_Report.ExcludeAdjustmentPeriods character(1) List
Jasper Process	The Jasper Process used by the print engine if any process defined		PA_Report.JasperProcess_ID numeric(10) Table
List Sources	List Report Line Sources	List the Source Accounts for Summary Accounts selected	PA_Report.ListSources character(1) Yes-No
List Transactions	List the report transactions	List the transactions of the report source lines	PA_Report.ListTrx character(1) Yes-No
Include source accounts with no transaction	Include source accounts with no transaction for list report line sources	List the Source Accounts with or without transactions for Summary Accounts selected	PA_Report.ListSourcesXTrx character(1) Yes-No
Create Report	Create Financial Report	The default period is the current period. You can optionally enter other restrictions. You can select an alternative Reporting Hierarchy.	PA_Report.Processing character(1) Button
Print Format	Data Print Format	The print format determines how data is rendered for print.	PA_Report.AD_PrintFormat_ID numeric(10) Table Direct
Create Report (Jasper)	Create Financial Report (Jasper)	The default period is the current period. You can optionally enter other restrictions. You can select an alternative Reporting Hierarchy.	PA_Report.JasperProcessing character(1) Button

Contributions / Posts

Functional Description

Look here Financial Report Writer

Technical Description

These reports are based on tables:

PA_Report
- PA_ReportColumnSet
  - PA_ReportColumn
- PA_ReportLineSet
  - PA_ReportLine
    - PA_ReportSource
C_AcctSchema
C_Calendar

The report generator looks on table T_REPORT and invokes org.compiere.report.FinReport process to fill temporary table.

T_REPORT has only 31 columns (0 to 30)

org.compiere.report.FinReport

prepare

Fix parameters

Fix where clause based on parameters and hierarchy.

Create a list of accounting periods of tables C_Period and C_Year with fields C_Period_ID, Name, StartDate (first day of month), EndDate (last day of month), YearStartDate (first day of year)

Query:

SELECT   p.c_period_id, p.NAME, p.startdate, p.enddate, MIN (p1.startdate)
    FROM c_period p INNER JOIN c_year y ON (p.c_year_id = y.c_year_id),
         c_period p1
   WHERE y.c_calendar_id = ?
     AND p.periodtype = 'S'
     AND p1.c_year_id = y.c_year_id
     AND p1.periodtype = 'S'
GROUP BY p.c_period_id, p.NAME, p.startdate, p.enddate
ORDER BY p.startdate

If period field is left blank process takes the period corresponding to TODAY

doIt

This method insert in T_Report records for the instance of process AD_PInstance_ID, according to the defined ReportLineSet

Query:

INSERT INTO t_report
            (ad_pinstance_id, pa_reportline_id, record_id, fact_acct_id,
             seqno, levelno, NAME, description)
   SELECT ?, pa_reportline_id, 0, 0, seqno, 0, NAME, description
     FROM pa_reportline
    WHERE isactive = 'Y' AND pa_reportlineset_id = ?

If "Update Balance" is seleted then the process FinBalance.updateBalance is executed, this process update existing Fact_Acct_Balance records from Fact_Acct, and then insert into Fact_Acct_Balance the non existing records in Fact_Acct.

For each line of report type SegmentValue (S)

For each column not calculation gets the select according with the AmountType of line, or of AmountType of column if line has that field blank.

First letter of Amount Type determines the query:

B: Balance - acctBalance(Account_ID,AmtAcctDr,AmtAcctCr)
C: Credit - AmtAcctCr
D: Debit - AmtAcctDr
Q: Qty - Qty

Then process define the work period, if it's relative subtract/add the relative value of current period for report.

Second letter of Amount Type determines the period:

P: Period - BETWEEN StartDate AND EndDate
Y: Year - BETWEEN YearStartDate AND EndDate
T: Total - <= EndDate

Example of a final query:

SELECT SUM (acctbalance (account_id, amtacctdr, amtacctcr))
  FROM fact_acct_balance
 WHERE dateacct BETWEEN DATE '2004-01-01' AND DATE '2004-05-31'

Moreover to this query conditions are added according to hierarchy and PostingType.

Function acctBalance does the following: returns DB-CR, or returns CR-DB if account is of credit nature.
If account is type N (Natural), then is credit with type is not A or E.

With founded value it updates the columns COL_X of table T_Report.

If the report must list sources then it proceeds to insert details on T_Report.
These lines on T_Report with Level = 1 (or -1 if must be reported before)
Transactions are level = 2 (or -2 if must be reported before)

Then process executes calculations over calculated columns.

Generating a Financial Report

Here we show how to generate a sample Balance Sheet based on GardenWorld.

Financial Report (Window ID-216)

Contents