Monthly Invoice Summary Report
This template generates a monthly invoice summary report from Xero, organizing the data by year and month. It details various invoice metrics such as the total number and amount of invoices, total tax, total amount due and paid, amount credited, and the number of unique contacts involved.
- Grain
- See the notes
- Built from
- 1Raw tables
- 1Core
- 1Analytics
- Reads from Xero
- invoice
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.xero.monthly_invoice_summary_report
2-- Monthly Invoice Summary Report. This template generates a monthly invoice summary
3-- report from Xero, organizing the data by year and month. It details various
4-- invoice metrics such as the total number and amount of invoices, total tax, total
5-- amount due and paid, amount credited, and the number of unique contacts involved.
6--
7-- From the weld.app template library. Not reconciled against Xero;
8-- check the output against the source before relying on it.
9
10select
11extract(
12year
13from
14date
15) as year
16, extract(
17month
18from
19date
20) as month
21, count(invoice_id) as total_invoices
22, sum(sub_total) as total_sub_amount
23, sum(total_tax) as total_tax_amount
24, sum(total) as total_invoice_amount
25, sum(amount_due) as total_amount_due
26, sum(amount_paid) as total_amount_paid
27, sum(amount_credited) as total_amount_credited
28, count(distinct contact_contact_id) as number_of_unique_contacts
29from
30{{raw.xero.invoice}}
31group by
321
33, 2
34order by
35year desc
36, month descThis 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.
Utilizing Xero invoice data, this SQL template aggregates key invoice metrics on a monthly basis, aiding in the detailed and comprehensive reporting of financial information. It showcases a summary of invoicing activities per month including the total and distinct count of invoices, accumulation of sub-total, tax, and total amounts, along with tracking the amount due, paid, and credited. Moreover, it gives insight into the client reach by reporting the number of unique contacts involved each month. By leveraging SQL’s aggregation and date extraction functions, it presents a structured report facilitating better financial monitoring and decision-making.
Raw tables it reads
- raw.xero.invoice
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.