Weld
Start free
Xero model

Journal Entries Report

This template generates a report on journal entries from Xero, breaking down the data by year and month. It gives you a count of all journal entries and a count of individual journal lines for each journal entry within the specified periods.

See the SQLAll Xero models
Grain
See the notes
Built from
  1. 2
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Xero
journaljournal_line
Ships as
Weld and dbt SQL, open source on GitHub
core.xero.journal_entries_report
1-- core.xero.journal_entries_report
2-- Journal Entries Report. This template generates a report on journal entries from
3-- Xero, breaking down the data by year and month. It gives you a count of all
4-- journal entries and a count of individual journal lines for each journal entry
5-- within the specified periods.
6--
7-- Entries are counted once each; lines are the journal lines joined to them,
8-- so a journal with no lines counts as an entry with zero lines.
9--
10-- From the weld.app template library, corrected. Not reconciled against Xero;
11-- check the output against the source before relying on it.
12
13SELECT
14EXTRACT(YEAR FROM journal_date) AS year,
15EXTRACT(MONTH FROM journal_date) AS month,
16COUNT(DISTINCT j.journal_id) AS number_of_journal_entries,
17COUNT(jl.journal_line_id) AS number_of_journal_lines
18FROM
19{{raw.xero.journal}} j
20-- Join with journal_line to count the number of journal lines for each journal entry
21LEFT JOIN
22{{raw.xero.journal_line}} jl
23ON
24j.journal_id = jl.journal_id
25GROUP BY
261,2
27ORDER BY
28year DESC,
29month DESC

This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

By extracting and aggregating Xero journal data based on year and month, this SQL template aids in the streamlined reporting of accounting information. The report brings forth a clear picture of the number of journal entries and journal lines created each month, helping to track the accounting activities over time. It leverages SQL's EXTRACT function and conditional aggregation to yield a structured breakdown of the journal entries, providing a comprehensive view that can support better financial management and reporting.

Raw tables it reads

  • raw.xero.journal
  • raw.xero.journal_line

More Xero models

Put Xero in front
of your agents.

Connect Xero, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us