If you look after more than one Xero organisation, you already know the drill. Log into the first org, run the report, export it. Log out. Log into the second org, run the same report, export it again. Paste six exports into a spreadsheet and hope nobody asks you to refresh it next week.
This is not a workflow problem you are failing to solve. Xero is built around one organisation at a time, and consolidated reporting across organisations is a long-standing request that Xero has not committed to building. Every accountant, bookkeeper and agency running several client files hits the same wall.
There is a way out that does not involve a spreadsheet: put every organisation's data into one PostgreSQL database, tagged by organisation, and query the lot with SQL. This post covers why Xero makes it awkward, the one schema decision that makes it work, and what to watch for when you consolidate across clients.
Why Xero Makes This Awkward
Xero treats each organisation as a separate world, and its API is built the same way.
When you authorise an app against Xero, you are not authorising an account, you are authorising a set of organisations, each identified by a tenant ID. Every single API call then has to name which organisation it is for, by passing that tenant ID in a header. Get it wrong and you do not get an empty result, you get an error.
That design is sensible. An accountant with forty client files should not be able to leak one client's ledger into another's report by forgetting a filter. But it means anything that reads Xero data has to be built for multiple organisations from the start, and most things are not. The mechanics of the OAuth flow and the tenant header are covered in our Xero API integration guide if you want the developer-level detail.
The result is a market full of tools that connect to one organisation nicely and fall apart at five.
The One Schema Decision That Makes This Work
Here is where most home-grown attempts go wrong. Faced with several organisations, the instinct is to give each one its own table, or worse, its own database. acme_invoices, globex_invoices, initech_invoices, on and on.
It works for two clients. At ten it is a maintenance problem, because every new client means new tables, every query means a UNION across a list you have to keep updating, and every schema change has to be applied everywhere.
The alternative is one table per data type for all organisations, with the tenant ID carried as a column and folded into the primary key. Codeless Sync builds its Xero tables this way: the primary key is the composite (id, tenant_id), and tenant_id is indexed. Two organisations can hold a contact or an invoice with the same Xero ID without colliding, and every table is one query away from being filtered or grouped by organisation.
That single choice is what turns "six exports and a spreadsheet" into GROUP BY.
The second piece is the xero_organisations table. Xero exposes organisation metadata as a data type of its own, so you can sync it alongside the rest and get a row per organisation carrying the trading name, legal name, base currency, country code, financial year end month and timezone, keyed by the same tenant_id. Without it your consolidated queries are full of GUIDs. With it they read like a client list.
Setting It Up
The shape of the job, assuming you are not writing the sync yourself:
- Connect each organisation. Authorise Xero once per organisation you want to sync. Where you have access to several, you pick which one this connection is for.
- Choose the data types. Contacts, invoices, payments, accounts, bank transactions, credit notes, items, purchase orders, journals and organisations are all available. Most consolidated reporting needs fewer than you think: invoices, contacts and organisations covers a surprising amount.
-
Create the destination tables once. Not once per client, once. Every organisation writes into the same tables, separated by
tenant_id. Tables can be auto-created, or you can run the SQL templates yourself if you would rather see the DDL first. - Put it on a schedule. Daily is the sensible default for accounting data. Our post on how often to sync billing data covers picking a cadence properly.
If you have never done the single-organisation version, how to sync Xero to PostgreSQL walks the whole flow start to finish. Multi-organisation is that, repeated per client, into the same tables.
The Queries That Make It Worth Doing
This is the part that pays for the setup. All three of these are impossible in Xero's own reporting because they cross organisations.
Outstanding receivables, every client, one screen.
SELECT
o.name AS organisation,
i.currency_code,
count(*) AS open_invoices,
sum(i.amount_due) AS outstanding
FROM xero_invoices i
JOIN xero_organisations o ON o.tenant_id = i.tenant_id
WHERE i.type = 'ACCREC'
AND i.status = 'AUTHORISED'
GROUP BY o.name, i.currency_code
ORDER BY outstanding DESC;
Who is overdue, across the whole book.
SELECT
o.name AS organisation,
i.invoice_number,
i.contact_name,
i.due_date,
current_date - i.due_date AS days_overdue,
i.amount_due,
i.currency_code
FROM xero_invoices i
JOIN xero_organisations o ON o.tenant_id = i.tenant_id
WHERE i.type = 'ACCREC'
AND i.status = 'AUTHORISED'
AND i.due_date < current_date
ORDER BY days_overdue DESC
LIMIT 50;
Which client files have gone stale. Worth running before anyone trusts a number:
SELECT
o.name AS organisation,
max(i.synced_at) AS last_synced,
max(i.updated_date_utc) AS latest_change_in_xero
FROM xero_invoices i
JOIN xero_organisations o ON o.tenant_id = i.tenant_id
GROUP BY o.name
ORDER BY last_synced;
None of these needs a reporting product, a BI licence or a spreadsheet. They are ordinary SQL against tables you own, which also means they can feed a dashboard, a scheduled email, or your own client portal.
What to Watch For
Consolidating across organisations introduces problems that single-org syncing never shows you. These are the ones that bite.
Do not sum across currencies. Invoices carry their own currency_code, and organisations carry a base_currency. A group with a UK and an Australian entity will happily let you add pounds to Australian dollars and produce a number that means nothing. Group by currency, as the queries above do, or convert deliberately with a rate table you control.
Financial year ends differ. financial_year_end_month exists on the organisation row for exactly this reason. Two clients on different year ends cannot share a naive "this year" filter.
Demo companies pollute totals. Xero's demo organisation is real data as far as an API is concerned. The organisation row carries is_demo_company, so exclude it explicitly rather than noticing later.
Access is per organisation, and clients can revoke it. Each organisation is authorised separately, which is the correct security posture, and it also means a client offboarding removes one connection and leaves the rest untouched. Build the staleness query above and you will notice a revoked connection in a day rather than a quarter.
Rate limits apply per organisation as well as per app. Xero meters each organisation separately, with a daily cap that depends on the connecting app's Xero subscription tier, so ten organisations do not all contend for one shared bucket. It is one of the few things about Xero's API that gets easier at scale rather than harder. The specifics are in our Xero API guide.
The number that binds is rows, not clients. This is the one people get wrong when they price it up. Plans are metered on rows processed per month, and a full sync reprocesses the whole table every run. Ten organisations synced daily on full mode is a lot of repeated work. Use a fixed lookback window for scheduled runs instead, keep the window wider than the gap between runs, and reserve full syncs for the first load and the occasional reconciliation.
What It Costs
Worth doing the arithmetic before you commit, because the shape is not obvious.
Each organisation and data type pairing is one sync configuration. Ten client organisations syncing invoices, contacts and organisation details is thirty configurations, all writing into one database.
On Codeless Sync pricing, that lands on Pro at $29 a month, which allows 30 configurations across 3 database projects with a 100,000 row monthly allowance and 90 days of sync history. Starter at $19 covers 10 configurations, which suits three or four clients on a couple of data types. Business at $99 goes to 100 configurations and 10 projects, which is agency scale. The free tier allows 2 configurations and covers Xero contacts and invoices only, so it is a way to try one client, not to run a book of them, and the organisation joins above need a paid plan.
Scheduled syncing is a paid feature at every tier: daily, weekly or monthly on Starter and Pro, with a 12-hourly option on Business. For accounting data, daily is almost always the right answer.
When You Want a Reporting Tool Instead
It would be dishonest to pretend this replaces everything, so here is the line.
Products like Joiin, Fathom and Spotlight Reporting exist to produce consolidated financial statements, and they do things this approach does not: intercompany eliminations, group structures with ownership percentages, formatted board packs, budget versus actual. If what you need is a monthly group P&L that an investor will read, buy one of those.
What syncing gives you instead is the raw ledger in a database you own, queryable in SQL, joinable against everything else you hold, and available to any tool that speaks Postgres. If your reporting is bespoke, if you are building a client dashboard, or if the interesting questions cross Xero and your own systems, a reporting product cannot get you there and a database can.
The two are not really competitors. One produces statements, the other produces a data layer. Plenty of firms end up with both.
If you are still deciding whether to build the sync yourself, Xero API vs database sync works through the build-versus-buy maths, and how to export Xero data to a database covers the one-off export options.
Frequently Asked Questions
Can Xero consolidate multiple organisations natively?
No. Xero is designed around one organisation at a time, and cross-organisation consolidated reporting is a long-standing feature request Xero has not committed to. Consolidation happens outside Xero, either in a reporting product or in a database you control.
How do I tell which organisation a row came from?
By its tenant ID. Xero identifies each organisation with a tenant ID, and a well-designed destination table stores it as a column and includes it in the primary key. Sync the organisations data type as well and you can join on tenant_id to get trading names, currencies and countries instead of bare identifiers.
Do I need a separate table for each Xero organization?
No, and you should avoid it. Separate tables per organization means new tables for every client, UNION queries that need editing whenever the client list changes, and schema updates applied many times over. A composite primary key of record ID plus tenant ID lets every organisation share one table safely.
Will invoice IDs from different organisations clash?
Not if the table is keyed on both the record ID and the tenant ID. That composite key is what allows two organisations to hold records with the same identifier in the same table without overwriting each other.
How many Xero organisations can I sync?
In practice the limit is how many sync configurations your plan allows, since each organisation and data type pairing counts as one. Thirty configurations covers ten organisations across three data types. Xero also caps how many organisations can connect to any given app, and that cap sits with the app rather than with you, so if you are planning to connect a large book of clients it is worth asking us first.
Can I do the same thing with QuickBooks or Stripe?
Yes for QuickBooks. Its tables use the same composite key pattern, keyed on the record ID plus the QuickBooks company ID, so several companies share one table safely. Stripe works differently. A Stripe account is a single entity, so the tables are keyed on the record ID alone with no account column, and two Stripe accounts writing to one table would leave you unable to tell the rows apart. Give each Stripe account its own database project instead.
Wrapping Up
Multi-organisation Xero reporting looks like a Xero problem, and it is really a storage problem. Xero will keep handing you one organisation at a time, correctly, because that is what it is for. The fix is to stop asking it for a consolidated view and to keep one yourself, in a database, with a tenant ID on every row.
Once the data is there, the questions that used to take an afternoon of exports take a GROUP BY.
Try it with one client organisation and see the first table land in your own database in about five minutes.
Related:
Top comments (0)