Weld logo
xero logo
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_id
Example 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.

Ready to start modeling your own xero data?

Get started building your data warehouse with xero and connect all your apps and databases.

xero logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.