I note a similar request from 4 years ago:

https://support.fivetran.com/hc/en-us/community/posts/1500000746602-Connector-Improvement-Xero-Pull-reports-for-Balance-Sheet-and-Profit-And-Loss

I am recreating P&L from raw data, but Xero's own API is inadequate for providing links between financial transactions and tracking categories. Specifically, in the existing Xero connector, the following tables are landed in our warehouse:

• bank_transaction_line_item_has_tracking_category
• credit_note_line_item_has_tracking_category
• invoice_line_item_has_tracking_category
• journal_line_has_tracking_category
• manual_journal_line_has_tracking_category
• overpayment_line_item_has_tracking_category
• purchase_order_line_item_has_tracking_category

The first issue is the fivetran dbt model for P&L mentioned in the earlier request include only three of the above financial transaction records - meaning the model is incomplete for computing P&L accurately ... but that's not even the biggest issue.

The second issue is that in all those tables, the link to tracking category option is a field named `option`, which is of type STRING. Basically, this is the display value to the end user for the given tracking category option. The reason for this is because the Xero API itself does not provide the option's ID value in its output. That shortcoming was flagged with Xero 2.5 years ago in this feature request:

https://xero.uservoice.com/forums/5528-accounting-api/suggestions/47113189-show-trackingoptionid-in-lineitem-in-invoices-api

I have this week had Xero confirm/agree this is an issue and they've added it to their roadmap but without any priority or timeframe for a fix. Below are two examples of the issues this shortcoming causes:

1) Imagine you create an option named "Project A" and then generate financial records. The financial records themselves have "Project A" recorded against them and this is exposed through the API. Now you realise the option should have been named "Project A1", so you update the option text in Xero - which the UI allows. Now those financial transactions cannot be linked to this tracking category option because the `option` (text) value no longer matches. Despite this, if you run the P&L within Xero, it correctly links those transactions to that option - implying that internally, Xero is storing the option ID against the transaction also.

2) Now imagine at some later date someone creates a new tracking category option with the text "Project A". Those earlier transactions now will match the new option - but they do not properly relate to that option. They should relate to Project A1 and now the transactions will be reported against the wrong option.

These incorrect P&L results will happen regardless of whether you code your own P&L logic using the existing Xero connector results, or whether you use the Fivetran dbt model for P&L (which, as mentioned, lacks many of the financial transaction tables anyway).

Hence, the only source of truth for P&L data (linked to tracking categories) remains Xero's UI - meaning all the connector data is useless if your business case is to link to tracking category options.

All that said, reading the Xero API docs on the "Profit And Loss" report available through their reports endpoint:

https://developer.xero.com/documentation/api/accounting/reports#profit-and-loss

... shows that you can supply either one or two tracking category IDs and, for each, you can further provide zero or one option ID. If you do not provide an option ID then the results will include a new column for every option within that tracking category. This would seem the logical way to go - and then ideally would require the result being transposed so that we get one row per option, rather than one column.

There are also input parameters for from date and to date which, if not supplied, default to "for the current month". In our business case we run a monthly report and look at three sets of values: the totals for the prior month (ie. we might run on the 5th of the month to review the prior month's data); the totals from the start of the financial year through to the end of the selected month (eg. "last month"); the totals from the start of time to the end of the selected month.

Given also that it is possible within Xero to modify records that were created on earlier dates, it means that subsequent runs of the reports could produce different results than earlier ones - even for prior months. Likewise, in Xero you can create financial transactions and back-date them, also meaning that re-running a report can generate different results for the same month.

Given all that complexity, I do not know how best Fivetran could model the results of calls to this API to pull the P&L summary data - in a way that should suit all Fivetran clients - who will be using the tracking categories for a variety of purposes, and who may need a variety of date ranges. I have a clear direction for what would suit our purposes, obviously.

In truth, the real issue is that Xero failed to include the option ID in all their endpoints that link financial transactions to tracking options .. and though this was flagged with them 2.5 years ago, they seem in no hurry to fix that bug. The fix they could code in a day would solve this issue for anyone expecting reliable results from all their financial transaction endpoints.