ERP
Automating Financial Reporting in a Custom ERP: From Trial Balance to Management Reports
May 202512 min read
ERP
The ERP software market reached $70 billion in 2025, driven largely by the demand for automated financial reporting. Finance teams using ERP report automation see a 70% reduction in report generation time, and Gartner projects that advanced ERPs with AI could cut financial close times by 30% by 2028. In Indonesian SMEs, I've found that financial reporting is typically the highest-value module — the one the CFO and owner look at every week. At Commsult, I built a financial reporting engine that generates P&L, Balance Sheet, Cash Flow, and management-specific reports entirely from the general ledger, with no manual spreadsheet work. This post covers the architecture.
Every financial report is ultimately a view over the chart of accounts (CoA) and the journal entries posted against it. Before writing any reporting code, get the CoA structure right. We use a four-level hierarchy: Category (Assets, Liabilities, Equity, Revenue, Expense) → Group → Sub-group → Account. The hierarchy determines which accounts roll up into which P&L and balance sheet lines. Changing the CoA after data is posted is painful — invest time in designing it correctly with the client's accountant before go-live.
Our journal engine enforces double-entry accounting at the database level: every journal entry must have total debits equal total credits, or the INSERT is rejected by a PostgreSQL CHECK constraint. Each journal_entry row has a header (date, reference, description, type) and multiple journal_lines (account_id, debit, credit). The financial reports are aggregations over these journal_lines, filtered by date range and grouped by account hierarchy. This means any transaction correctly posted to the journal — AP invoice, AR payment, payroll — automatically appears in all financial reports with no additional mapping.
Financial Reporting Engine — NestJS + PostgreSQL
Chart of Accounts (4-level hierarchy)
├── ASSETS
│ ├── Current Assets
│ │ ├── Cash & Bank [11000–11999]
│ │ └── Accounts Receiv. [12000–12999]
│ └── Fixed Assets [15000–15999]
├── LIABILITIES
│ └── Accounts Payable [21000–21999]
├── EQUITY [30000–39999]
├── REVENUE [40000–49999]
└── EXPENSES [50000–59999]
journal_lines (the single source of truth)
┌─────────────────────────────────────────────────┐
│ id │ journal_id │ account_id │ debit │ credit │
│ ...│ ... │ 11001 │ 1000 │ 0 │ <- Cash receipt
│ ...│ ... │ 40001 │ 0 │ 1000 │ <- Revenue
└─────────────────────────────────────────────────┘
↓ Aggregated by NestJS ReportService
P&L Report Balance Sheet Cash Flow
Revenue: 500M Assets: 2.1B Operating: +400M
COGS: (300M) Liab: 800M Investing: (50M)
Gross: 200M Equity: 1.3B Financing: (20M)
Net: 150M Net: 330MFrom my experience building ERP systems at Commsult: always implement a 'period lock' feature. Indonesian companies need to close their books monthly for PPN (VAT) reporting and annually for corporate tax. Once a period is locked, no new journal entries can be posted to it — this prevents retroactive changes that would invalidate already-submitted tax reports. The lock is enforced at the database level with a trigger, not just in the application layer.
Our three core financial reports are generated on demand via NestJS endpoints. The P&L endpoint accepts a date range and returns revenue accounts summed as income, expense accounts summed as costs, with gross profit and net profit computed. The Balance Sheet endpoint computes asset, liability, and equity totals as of a specific date (cumulative from inception, not period-filtered). The Cash Flow endpoint categorizes movements into operating, investing, and financing activities based on account tags. All three return structured JSON consumed by a React report renderer.
Beyond the statutory reports, management wants cost center P&L (how much did each department spend?), project profitability (revenue vs. cost by project), and product margin analysis (which products make money?). We support this through a cost_center_id and project_id on each journal line. Segmented reports filter by these dimensions. The CFO at our client gets a weekly email (automatically generated every Monday at 6 AM WIB) with P&L by cost center attached as an Excel file generated via exceljs in NestJS.
-- PostgreSQL: P&L report query
SELECT
coa.category,
coa.group_name,
coa.account_name,
coa.account_code,
SUM(jl.debit - jl.credit) AS net_amount
FROM journal_lines jl
JOIN journal_entries je ON jl.journal_id = je.id
JOIN chart_of_accounts coa ON jl.account_id = coa.id
WHERE
je.posted_at BETWEEN :start_date AND :end_date
AND je.status = 'posted'
AND coa.category IN ('REVENUE', 'EXPENSE')
AND (je.locked_period IS NULL OR je.locked_period >= :start_date)
GROUP BY coa.category, coa.group_name, coa.account_name, coa.account_code
ORDER BY coa.account_code;
-- Period lock enforcement (trigger)
CREATE OR REPLACE FUNCTION check_period_lock()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM period_locks
WHERE period_year = EXTRACT(YEAR FROM NEW.posting_date)
AND period_month = EXTRACT(MONTH FROM NEW.posting_date)
AND locked = TRUE
) THEN
RAISE EXCEPTION 'Period % is locked — no new entries allowed',
TO_CHAR(NEW.posting_date, 'YYYY-MM');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_period_lock
BEFORE INSERT ON journal_entries
FOR EACH ROW EXECUTE FUNCTION check_period_lock();Our reporting engine is a NestJS module with four services: JournalService (posting and validation), ReportService (P&L, Balance Sheet, Cash Flow queries), SegmentService (cost center and project filtering), and ExportService (PDF via Puppeteer, Excel via exceljs). All reports are cached in Redis with a 5-minute TTL — re-generating a full P&L from journal lines for a year with thousands of entries can take 2-3 seconds, which is fine for on-demand but not for a dashboard that refreshes every 30 seconds.
If your client has multiple legal entities or transacts in foreign currencies, consolidated financial reporting requires currency translation — converting subsidiary accounts from their functional currency to the presentation currency at the appropriate exchange rates. This is a common source of errors when handled manually. We store exchange rates daily and apply transaction-date rates for income statement items and period-end rates for balance sheet items, with FASB ASC 830 or PSAK 10 as the reference standard depending on the client's reporting requirements.
Indonesian businesses must file monthly PPN (VAT) returns via e-Faktur and annual PPh (income tax) returns. Our ERP links each journal line to an optional tax code (PPN 11%, PPh 23 2%, etc.). Tax reports are generated by filtering journal lines by tax code and date range. The PPN report groups by tax period, shows input tax (purchases) and output tax (sales), and computes the net tax payable or credit. We export this in the format accepted by the Direktorat Jenderal Pajak's e-Faktur application, which saves the finance team several hours of monthly data entry.
For Indonesian companies subject to external audit (either statutory audit for PKP companies or client audit for large vendors), every journal entry needs supporting documentation. Our system links journal entries to source documents — AP invoices, AR invoices, bank statements, payment vouchers. The auditor can click on any journal line and see the original document. This 'drill-down to source' capability is what separates a real ERP from a sophisticated spreadsheet. When BPK (Badan Pemeriksa Keuangan) or an external auditor requests support for a transaction, we can pull it up in 30 seconds.