Tracking Details Report
This template generates a report that brings together tracking details from journal and invoice records, outlining essential details including source type, source ID, and financial particulars such as net and gross amounts, tax details, and tracking categories.
- Grain
- See the notes
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from Xero
- invoice_line_itemjournal_line
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.xero.tracking_details_report
2-- Tracking Details Report. This template generates a report that brings together
3-- tracking details from journal and invoice records, outlining essential details
4-- including source type, source ID, and financial particulars such as net and gross
5-- amounts, tax details, and tracking categories.
6--
7-- From the weld.app template library. Not reconciled against Xero;
8-- check the output against the source before relying on it.
9
10WITH JournalTracking AS (
11SELECT
12'Journal' AS source,
13journal_id AS source_id,
14description,
15net_amount,
16gross_amount,
17tax_amount,
18tax_type,
19tax_name,
20tracking_categories AS tracking
21FROM
22{{raw.xero.journal_line}}
23),
24-- Next, we'll collect data from the invoice_line_item table
25InvoiceTracking AS (
26SELECT
27'Invoice' AS source,
28invoice_id AS source_id,
29description,
30NULL AS net_amount,
31line_amount AS gross_amount,
32tax_amount,
33tax_type,
34NULL AS tax_name,
35tracking
36FROM
37{{raw.xero.invoice_line_item}}
38)
39-- Now, we'll union these two CTEs to create the report
40SELECTThis 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().
How it counts. And where it stops.
This SQL template creates a unified view of tracking details stemming from both journal and invoice records in the Xero accounting system. Utilizing Common Table Expressions (CTEs) to segregate data from journal lines and invoice line items separately, it then combines them to present a holistic report. This detailed report not only outlines the financial particulars such as net amount, gross amount, and tax details but also categorizes them based on their source, aiding in a comprehensive and streamlined financial tracking and reporting.
Raw tables it reads
- raw.xero.invoice_line_item
- raw.xero.journal_line
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.