Financial Reporting and Analysis

Overview

• What you’ll learn:
  • How to build custom financial reports using iDempiere’s Report Builder (PA_Report, PA_ReportLine, PA_ReportColumn) with account ranges, calculations, and multiple column types
  • How the period-end and year-end closing processes work, including soft close, hard close, closing journals, and retained earnings posting
  • How to configure budgets, perform budget vs. actual analysis, create custom Balance Sheet and P&L reports step by step, and schedule report distribution
• Prerequisites: Lesson 38 — Multi-Currency and Multi-Org Accounting (understanding of accounting schemas, organizations, and general ledger structure)
• Estimated reading time: 25 minutes

Introduction

Financial reports are the output of an ERP system — the artifacts that board members read, auditors examine, tax authorities review, and managers use to make decisions. Everything else in the system — every invoice, payment, journal entry, and inventory movement — ultimately feeds into these reports.

iDempiere includes a Financial Report Builder that allows you to create highly customized financial reports without writing code. You can define which accounts appear on each row, which time periods or comparison columns to display, and how calculated rows (subtotals, ratios, variances) derive their values. This lesson covers the report builder in detail, the period-end processes that prepare data for reporting, budget management, and step-by-step instructions for creating Balance Sheet and Profit and Loss reports.

Financial Report Builder Architecture

The report builder is composed of three core tables that work together: PA_Report defines the overall report, PA_ReportLine defines the rows, and PA_ReportColumn defines the columns.

PA_Report (Report Definition)

The PA_Report table is the master record for a financial report. Key fields include:

A single report definition brings together a line set (rows) and a column set (columns). You can reuse the same line set with different column sets — for example, the same Balance Sheet row definitions displayed with “Current Period” columns or “Year-to-Date” columns.

PA_ReportLine (Row Definitions)

Report lines define what appears in each row of the report. Lines are grouped into a Report Line Set (PA_ReportLineSet), and each line set contains multiple lines ordered by their sequence number.

Line Types

Each report line can be one of the following types based on its configuration:

• Account range line: Pulls actual accounting data from the general ledger. You define the account range by specifying starting and ending account values using the PA_ReportSource table. For example, a line labeled “Cash and Bank” might include accounts 1000-1099.
• Calculation line: Computes a value from other lines. For example, “Total Current Assets” sums the Cash, Receivables, and Inventory lines. Calculations reference other lines by their identifier and support addition, subtraction, and percentage operations.
• Segment line: Filters by accounting dimensions other than account — such as organization, business partner, product, project, or activity. This enables reports segmented by dimension.

Key PA_ReportLine Fields

PA_ReportSource (Account Ranges)

For segment-value lines, the PA_ReportSource table defines which accounts feed into the line. Each source record specifies:

• ElementValue (Account): A specific account or an account range (from-to).
• Other dimensions: Optional filters by organization, business partner, product, project, campaign, or activity.
• ListSources: Whether to break down the line by individual accounts in the report output.

A single report line can have multiple report source records, and the line’s value will be the sum of all matching accounts across all its sources.

PA_ReportColumn (Column Definitions)

Report columns define the time-based or comparative values displayed for each row. Columns are grouped into a Report Column Set (PA_ReportColumnSet).

Column Types

Key PA_ReportColumn Fields

T_Report: The Temporary Reporting Table

When a financial report is executed, iDempiere does not query the general ledger directly for each cell. Instead, it populates a temporary table called T_Report with the calculated values, and the report viewer reads from this table.

How T_Report Is Populated

1. Initialization: The report engine creates T_Report records for each line-column intersection (each cell in the report matrix).
2. Segment value lines: For each segment value line, the engine queries Fact_Acct (the general ledger detail table) using the account ranges, dimension filters, period boundaries, and posting type defined by the line and column. The result (sum of debits minus credits, or as configured by AmountType) is stored in T_Report.
3. Calculation lines: After all segment value lines are populated, calculation lines are evaluated. A calculation line references other lines (Oper_1_ID, Oper_2_ID) and applies the calculation type (add, subtract, percentage) to derive its value.
4. Calculation columns: Similarly, calculation columns reference other columns and compute derived values (e.g., variance = budget column minus actual column).
5. Display: The report viewer reads T_Report and renders it as a formatted report with headers, data rows, subtotals, and formatting based on the line and column properties (IsSummary, IsPrinted, etc.).

The T_Report table is session-specific and temporary — it is cleaned up after the report is displayed. This architecture allows reports to be generated without permanent storage overhead.

Period-End Closing

Before financial reports can be finalized for a period, the period must go through a closing process that ensures all transactions are recorded and no further changes can be made.

Soft Close

A soft close (also called “closing” the period) prevents new documents from being posted to the period. The period status is changed to “Closed” in the C_Period and C_PeriodControl tables. Key characteristics:

• No new documents can be posted with an accounting date in the closed period.
• The period can be re-opened if adjustments are needed (by an administrator with the appropriate permissions).
• This is a reversible action — it is a control mechanism, not a permanent lock.

Hard Close

A hard close (or “permanently closing” the period) is a more definitive action. Once a period is hard-closed:

• It cannot be re-opened through the standard interface.
• All adjustments for the period must be made in a subsequent adjustment period.
• This is typically done after the audit is complete and the period’s financials are finalized.

Period Close Checklist

Before closing a period, ensure the following tasks are complete:

1. Post all documents: Run the posting process to ensure no unposted documents remain for the period.
2. Currency revaluation: Perform the unrealized gain/loss calculation for foreign-currency open items (covered in Lesson 38).
3. Accruals and deferrals: Enter any accrual or deferral journal entries (prepaid expenses, deferred revenue, accrued liabilities).
4. Depreciation: Run asset depreciation for the period.
5. Reconciliation: Reconcile bank statements, inter-company balances, and sub-ledger to GL balances.
6. Review trial balance: Verify that the trial balance is in balance and investigate any unusual amounts.
7. Close the period: Change the period status to Closed.

Year-End Processing

At the end of the fiscal year, additional steps are required beyond the normal period close.

Closing Journal (Income Summary)

The year-end closing journal transfers the net income (or net loss) from the income statement accounts to the Retained Earnings account on the balance sheet. The process:

1. Zero out revenue accounts: Debit each revenue account for its year-end balance, Credit Income Summary.
2. Zero out expense accounts: Credit each expense account for its year-end balance, Debit Income Summary.
3. Transfer net income: The Income Summary balance (revenue minus expenses = net income) is transferred to Retained Earnings. Debit Income Summary, Credit Retained Earnings (for a profitable year).

In iDempiere, this is typically accomplished through a year-end closing process or a manually created GL Journal. The key configuration is the Retained Earnings account on the Accounting Schema defaults — this is where the system posts the net income transfer.

Opening Balances in the New Year

After the closing journal is posted:

• Balance sheet accounts carry their ending balances forward as opening balances in the new fiscal year. Assets, liabilities, and equity continue seamlessly.
• Income statement accounts start the new year with zero balances. All revenue and expense accumulation begins fresh, as the prior year’s amounts have been moved to Retained Earnings.

iDempiere handles this automatically: when a financial report for the new year queries balance sheet accounts, it includes all prior-year postings. When it queries income statement accounts, it automatically excludes postings from prior fiscal years (based on the account type and the calendar’s year boundaries).

Budget Management

Budgets establish the financial plan against which actual performance is measured. iDempiere’s budgeting framework allows you to define detailed budgets and compare them to actuals through the financial report builder.

GL_Budget

The GL_Budget table defines a budget as a named plan. A client can have multiple budgets — an operating budget, a capital expenditure budget, a cash budget, etc. Each budget is identified by name and has an active flag that controls whether it appears in reports and lookups.

Budget Entry

Budget amounts are entered through GL Journals with the posting type set to Budget (rather than the default “Actual”). Each journal line specifies:

• Account: The account being budgeted.
• Period: The accounting period for this budget amount.
• Amount: The budgeted debit or credit.
• Organization: The organization this budget applies to (allowing per-org budgets).
• Other dimensions: Product, project, business partner, activity — enabling detailed dimensional budgeting.

Budget journals are posted to the Fact_Acct table with PostingType = ‘B’ (Budget), keeping them separate from actual transactions (PostingType = ‘A’). This separation allows the financial report builder to query actuals and budgets independently and create comparison columns.

GL_BudgetControl

The GL_BudgetControl table enables budget enforcement — preventing transactions from being completed when they would cause the actual spending to exceed the budget. Configuration options include:

• Budget: Which budget to enforce against.
• Control scope: Which accounts are subject to budget control.
• Control action: Whether exceeding the budget generates a warning (allowing the user to proceed) or an error (blocking the transaction).
• Commitment type: Whether to include committed amounts (open POs) in the budget utilization calculation, providing a more forward-looking view of budget consumption.

Budget vs. Actual Analysis

Using the financial report builder, you can create budget comparison reports with columns such as:

• Column 1 — Actual YTD: PostingType = Actual, ColumnType = Year-to-Date.
• Column 2 — Budget YTD: PostingType = Budget, ColumnType = Year-to-Date.
• Column 3 — Variance: Calculation column = Budget – Actual.
• Column 4 — % Utilized: Calculation column = Actual / Budget x 100.

This four-column layout gives managers a clear view of how actual spending tracks against the plan, where variances exist, and what percentage of the budget has been consumed.

Creating a Custom Balance Sheet: Step by Step

Let us walk through the creation of a Balance Sheet report from scratch.

Step 1: Create the Report Line Set

Navigate to the Report Line Set window and create a new record named “Balance Sheet Lines.” Then create the following lines (simplified example):

For each segment line, create PA_ReportSource records specifying the account ranges. Set the AmountType to “Balance” (Debit minus Credit) for asset lines and consider sign reversal for liability and equity lines (since they naturally have credit balances, you may want to display them as positive numbers).

Step 2: Create the Report Column Set

Create a new Report Column Set named “Balance Sheet Columns” with the following columns:

This column set displays the balance sheet at the current date, the prior month, the same month last year, and the month-over-month change.

Step 3: Create the Report

Navigate to the Financial Report (PA_Report) window and create a new record:

• Name: Balance Sheet
• Report Line Set: Balance Sheet Lines (created in Step 1)
• Report Column Set: Balance Sheet Columns (created in Step 2)
• Accounting Schema: Your primary accounting schema
• Calendar: Your fiscal calendar

Run the report by clicking the Process button. The report will populate T_Report and display the formatted Balance Sheet with all rows and columns populated from the general ledger.

Creating a Custom Profit and Loss Report

The P&L (Income Statement) report follows the same pattern but uses income and expense accounts.

Report Line Set (Simplified)

Report Column Set for P&L

A typical P&L column set might include:

Note that for P&L reports, you typically use Period Amount rather than Balance for the AmountType. Period Amount shows only the activity within the period (debits and credits posted during that month), whereas Balance shows the cumulative balance since the account’s inception.

Financial Ratios and Analysis

The report builder’s calculation capabilities extend beyond simple subtotals. You can create financial ratio lines directly in the report:

• Current Ratio: Total Current Assets / Total Current Liabilities (use percentage calculation between lines 50 and 120 from the Balance Sheet example).
• Gross Margin %: Gross Profit / Net Revenue x 100 (percentage calculation between P&L lines 50 and 30).
• Operating Margin %: Operating Income / Net Revenue x 100.
• Debt-to-Equity: Total Liabilities / Total Equity.

These ratio lines use the Calculation line type with CalculationType set to “Percentage.” The result is displayed as a percentage, providing immediate analytical insight within the standard financial report.

Report Scheduling and Distribution

Financial reports can be scheduled for automatic generation and distribution using iDempiere’s process scheduling framework.

Scheduling Reports

Using the Scheduler window, you can configure a financial report to run on a recurring schedule:

• Frequency: Daily, weekly, monthly, or on specific dates (e.g., the 5th business day after month end).
• Parameters: The report parameters (period, organization, etc.) can be preset or derived dynamically (e.g., “current period” always runs for the most recent closed period).
• Output format: PDF, Excel, or HTML.

Distribution

Scheduled reports can be distributed automatically:

• Email: The generated report is emailed as an attachment to a distribution list. Configure recipients on the scheduler or use a notification rule.
• Dashboard: Report results can be configured to appear on the user’s dashboard, providing at-a-glance financial status.
• Archive: Reports can be automatically archived in iDempiere’s document archive for historical reference and audit trail.

Advanced Reporting Techniques

Multi-Organization Reports

When running a report for the * (all) organization, the report sums data across all organizations. To create a comparative report showing each organization side by side:

• Create separate columns filtered by organization, or
• Use the ListSources option with organization as a dimension to break down totals by org.

Trailing Twelve Months (TTM)

Create 12 relative-period columns (RelativePeriod = 0 through -11) and a calculation column that sums them. This provides a rolling twelve-month view that smooths seasonality and is useful for trend analysis.

Comparative Year-over-Year

Use relative period columns with offsets of 0 and -12 to show the current month and the same month in the prior year. Add a calculation column for the year-over-year change and a percentage column for the growth rate.

Departmental P&L

If your chart of accounts includes an Activity or Cost Center dimension, create report lines with PA_ReportSource records filtered by activity. This produces a departmental P&L without needing separate reports — each line shows the income and expenses for a specific department.

Key Takeaways

• iDempiere’s Financial Report Builder uses three core structures: PA_Report (the report definition linking rows to columns), PA_ReportLine (row definitions with account ranges or calculations), and PA_ReportColumn (column definitions with period types, posting types, and calculations).
• Report lines can reference specific accounts or account ranges through PA_ReportSource records, and calculation lines can sum, subtract, or compute percentages from other lines — enabling complex report structures like subtotaled Balance Sheets and P&L statements with gross margin calculations.
• The T_Report temporary table is populated at report execution time by querying Fact_Acct (the general ledger), ensuring reports always reflect the latest posted data without permanent storage overhead.
• Period-end closing follows a checklist (post all documents, revalue currencies, enter accruals, reconcile, close the period), with soft close being reversible and hard close being permanent.
• Year-end processing transfers net income from revenue and expense accounts to Retained Earnings via a closing journal, resetting income statement accounts to zero for the new fiscal year while carrying balance sheet balances forward.
• Budgets are entered as GL Journals with PostingType = Budget, stored in Fact_Acct alongside actuals, and compared through the report builder using columns with different posting types and calculation columns for variance and percentage analysis.
• The report builder supports advanced analysis including financial ratios (using percentage calculation lines), multi-organization comparisons, trailing twelve-month views, and year-over-year analysis — all without custom code.

What’s Next

This lesson concludes the Advanced series on Manufacturing and Financial Management. You now have the knowledge to manage complex production operations, multi-location distribution, product costing, multi-currency and multi-organization accounting, and sophisticated financial reporting. The next series moves into Expert-level topics, covering system customization, plugin development, and advanced integrations that extend iDempiere beyond its standard capabilities.