Xero
Invoice Entries Report
This template generates a report on invoice entries from Xero, breaking down the data by year and month. It gives detailed information on each invoice entry including various attributes such as invoice ID, description, quantity, and so on.
1select
2 extract(
3 year
4 from
5 i.date
6 ) as year
7 , extract(
8 month
9 from
10 i.date
11 ) as month
12 , ili.invoice_id
13 , ili.description
14 , ili.quantity
15 , ili.unit_amount
16 , ili.tax_type
17 , ili.tax_amount
18 , ili.line_amount
19 , ili.account_code
20 , ili.item_item_id
21 , ili.item_name
22 , ili.item_code
23 , ili.tracking
24 , ili.line_item_id
25from
26 {{raw.xero.invoice_line_item}} ili
27 -- Join the invoice table to get the date of each invoice
28 join {{raw.xero.invoice}} i on ili.invoice_id = i.invoice_id
29group by
30 1
31 , 2
32 , ili.invoice_id
33 , ili.description
34 , ili.quantity
35 , ili.unit_amount
36 , ili.tax_type
37 , ili.tax_amount
38 , ili.line_amount
39 , ili.account_code
40 , ili.item_item_id
41 , ili.item_name
42 , ili.item_code
43 , ili.tracking
44 , ili.line_item_id
45order by
46 year desc
47 , month desc
48 , ili.invoice_idExample of output from model:
+------+-------+------------+-------------+----------+-------------+----------+------------+-------------+--------------+---------------+------------+-----------+----------+------------+
| year | month | invoice_id | description | quantity | unit_amount | tax_type | tax_amount | line_amount | account_code | item_item_id | item_name | item_code | tracking | line_item_id |
+------+-------+------------+-------------+----------+-------------+----------+------------+-------------+--------------+---------------+------------+-----------+----------+------------+
| 2023 | 8 | 123456 | Item desc | 10 | 100 | TAX001 | 20 | 1000 | ACC001 | ITEM123 | Item name | ITEM001 | Tracking1 | LINE123 |
| 2023 | 7 | 123457 | Item desc2 | 5 | 200 | TAX002 | 25 | 1000 | ACC002 | ITEM124 | Item name2 | ITEM002 | Tracking2 | LINE124 |
+------+-------+------------+-------------+----------+-------------+----------+------------+-------------+--------------+---------------+------------+-----------+----------+------------+Leveraging SQL’s EXTRACT function, this template helps in extracting detailed data on Xero invoice entries by segregating it based on the year and month. The report encompasses vital details of each invoice including invoice ID, description, quantity, unit amount, tax type, and much more. It not only facilitates a meticulous representation of each entry but also empowers users to have a streamlined access to exhaustive data, promoting informed decision-making in financial management and reporting.
