Generate reports from the ledger

Now that we have our ledger account tree, sub-ledgers for relations, and our external ledgers synced, we can look at the power of reporting over journal entries. That's the topic for this week's newsletter!

Report types

With the journal entries added to the database, we can generate numbers from the data. These numbers are divided into two methods:

1. You can calculate the balance of a ledger account on a specific date
2. You can calculate the total of a ledger account in a certain period

These methods are used in the different accounting reports but are also helpful for business logic.

Balance-based reports

The most commonly known example of a balance-based report is your bank account balance. This single amount is an accumulation of all your deposits and withdrawals. This accumulation goes back to the very first transaction of your bank account, in my case, when my parents opened a bank account for me as a baby. Calculating a balance can be an expensive operation depending on the number of transactions.

With the ledger stored in an SQL database, we can calculate a balance with this query:

SELECT SUM(debit-credit) FROM journal_entries WHERE ledger_account='bank_account'

A balance is always reported on a date, so the example above reports the balance for the last date entered in the table. Therefore, the balance as of a specific date can be calculated by only including records before and on this date:

SELECT SUM(debit-credit) FROM journal_entries WHERE ledger_account='bank_account' AND date <= '2021-12-31'

Date-based reporting is a powerful feature of double-entry bookkeeping. Examples of valuable reports:

• How has the balance of our financial accounts changed?
• Have the outstanding amounts for accounts receivable increased or decreased?
• How much do we owe to accounts payable and taxes?
• How much does this relation owe us?

Accounting has several reports that use balances; let's walk through some of them.

Balance sheet

A balance sheet contains a row for each ledger account on the balance. A balance sheet is always generated for a specific date. The SQL to do so is:

SELECT ledger_account, SUM(debit-credit) 
  FROM journal_entries 
  WHERE ledger_account <@ 'balance_sheet' AND date <= '2021-12-31'
  GROUP BY ledger_account
  ORDER BY ledger_account;

By using a ltree column type for the materialized ledger account identifier, you can easily do an ancestor query to fetch all balance sheet ledger accounts.

Debtor and creditor report

These reports display the open amounts for debtors and creditors. In SQL:

SELECT relation_id, SUM(debit-credit)
  FROM journal_entries
  WHERE ledger_account = 'balance_sheet.current_assets.accounts_receivable' AND
    date <= '2021-12-31'
  GROUP BY relation_id
  ORDER BY SUM(debit-credit) DESC;
  

Totals-based reports

The alternative view over the journal entries is the total for a specific period. You can compare this with your bank account again: a total tells you the amount you deposited or credited in a year.

A benefit of the total calculation is that it's always bounded to a period. You don't need to consider all previous journal entries to calculate a total. But with a growing number of journal entries, even these calculations can become slow without optimizations.

The most common example for a total based calculation is the revenue:

SELECT SUM(credit-debit)
  FROM journal_entries
  WHERE ledger_account @> 'profit_loss.revenue'
    AND date BETWEEN '2021-01-01' AND '2021-31-21';

You might notice we flipped the SUM arguments. Because profit loss is credit-default, we need to swap the calculation to get the correct number. You can read about credit-default profit-loss in the ledger account tree issue.

Profit loss statement

A profit and loss statement displays all revenue and expenses within a business. It consists of several parts, like revenue, direct costs, and expenses. These parts are easily calculated in SQL:

// revenue
SELECT ledger_account, SUM(credit-debit)
  FROM journal_entries
  WHERE ledger_account @> 'profit_loss.revenue'
    AND date BETWEEN '2021-01-01' AND '2021-31-21'
  GROUP BY ledger_account
  ORDER BY ledger_account;
  
// expenses
SELECT ledger_account, SUM(debit-credit)
  FROM journal_entries
  WHERE ledger_account @> 'profit_loss.expenses'
    AND date BETWEEN '2021-01-01' AND '2021-31-21'
  GROUP BY ledger_account
  ORDER BY ledger_account;

Calculating the profit of a business is possible by including all ledger accounts:

SELECT SUM(credit-debit)
  FROM journal_entries
  WHERE ledger_account @> 'profit_loss'
    AND date BETWEEN '2021-01-01' AND '2021-31-21';

General ledger

The general ledger report is a combination of both balances and totals. It is a crucial accounting report to display the changes on ledger accounts. It contains four columns:

• The start balance at the beginning of the period
• The total debit transactions in the period
• The total credit transactions in the period
• The end balance at the end of the period

By combining both balance and total queries, you can generate this report.

The auditable ledger

One of the benefits of double-entry accounting with journal entries, is the presence of all separate journal entries in the database. Therefore, you can generate an overview of journal entries that caused the amount to change for every balance and total. This approach makes the whole system easy to audit.

Compare this with a system storing only a single balance for a relation, or a single total for a period. Without recording the underlying changes in the numbers, you can never be sure if the numbers are correct.

An interesting side-note: double-entry accounting has inspired the event sourcing design pattern. This pattern stores all events that lead up to the current state of the world. Even if you don't need double-entry accounting, you can use its principles to design your software.

Next issue

I'll add even more power to the journal entries and reporting with custom dimensions in the next issue. These custom dimensions allow for more use cases like tax reporting and cost centers. Subscribe to my newsletter to receive the next issue in your mailbox once it releases.