Xero’s Reporting API is an extremely powerful endpoint which provides us with the data required to assemble their most common reports, such as Balance Sheets or Profit & Loss. Unfortunately, with this power comes a level of abstraction that makes it quite tough to wrangle the data into a meaningful chart or report.

In this post, I will demonstrate what the structure of this endpoint means, why it is actually excellent (for us nerds), and the SQL required to coerce it into something that humans (not nerds) can understand.

The structure

Please note: In this tutorial I’ll be using the Profit & Loss report, but the same concepts apply to all of Xero’s reporting endpoints

When you think about your Profit & Loss report, you probably imagine it visually - a list of items down the left hand side, with corresponding values for the month in a separate column. Something, like this (taken from Xero’s own dashboards):

But that’s not how a data-driven person sees it. We see a huge range of considerations when trying to convert this into a consistent format suitable for an API. No, we see something more like this:

• Reports are divided into sections, with section totals at the bottom of each. This also indicates that there might be sub-sections (or sub-sub-sections) in some reports

• The column that contains the totals on the right, also contains a heading, which is of type string. This means the numbers may be type string as well - making things like sum or avg difficult

• Different variations show a different number of columns, such as a YTD roll-up

• …you get the idea…

Abstracting the data

The variance in formats makes it prohibitive to generate a different endpoint for each permutation, so what the Xero team did was abstract the data into its common features - rows & cells. Let’s have a look at what the API returns (I’ve truncated to only the Gross Profit section):

Wow, that is quite a lot to take in. You’ll probably recognize a few things like your product/service names and the totals. But it’s not immediately obvious how to coerce this into a tabular format.

Step 1/2 - Storing the data in a relational database

The format of the API response is called JSON - and it’s a common format for APIs to return data in. It’s possible to query and manipulate JSON data, but your reporting tool probably doesn’t have this facility and besides, you have to be pretty nerdy.

So, our first step is to convert this JSON into a relational-database format. Reporting tools love relational data. At SyncHub, below is the structure we came up with.

If you have a SyncHub subscription connected to Xero, these tables will automatically appear in your database, and the data will be kept up to date automatically too.

As you can see, we retained the abstract nature of the data (rows & cells), and augmented with a few other quality-of-life fields such as typed columns and foreign keys back to the Account records.

At this point you’re probably thinking that we’re just replacing one confusing solution for another. And from a human’s point of view, you’re right. But from your reporting tool’s point of view there is one major new benefit - SQL queries.

Step 2/2 - Querying your Xero reports using SQL queries

Now, this data does not lend itself to your run-of-the-mill SELECT queries. No, for this you need something more powerful - the PIVOT query. I won’t go into details about exactly how the PIVOT query works - you can search Google or ask ChatGPT about that. But in short, in our case it lets us use the imported column values (i.e. month names) as columns headings. I’ll cut straight to the code, pasted below.

Note that I’m using MS SQL, which is the default database provided free as part of your SyncHub subscription. If you’re using an alternative such as Postgres, you’ll have to tweak accordingly.

If you execute this against your SyncHub data store, you’ll get something along the lines of the results below:

So, how can you use this?

I won’t lie to you - that is a gnarly query. But, that is the nature of BI sometimes, and think how impressed everybody in the office will be! So let’s move on to the final step - how exactly can you use this query in your reports?

Using your own reporting tool

Unfortunately, we can’t provide guidance for every reporting tool, but (assuming your tool supports SQL queries, which it almost certainly does), what you can essentially do is copy/paste this into your editor and tweak accordingly.

Note: you must replace the schema placeholder [CONNECTIONS.xero] with whatever the schema name of your SyncHub data store is.

There is also the chance that your reporting tool won’t let you execute dynamically-assembled strings. In this case, you’ll have to either:

• hard-code the column names (changing every month, as required)

• use our Insights option below, then query the resulting table from your reporting tool

Using SyncHub Insights

For a much easier solution, you can use this query in our Insights platform. Insights will periodically execute the query, and store the results in a new database table. From there, you can either:

• query your new database table from your reporting tool, using a simple select * from… query; or

• use Insights itself to render and share your reports

To conclude

Xero is great. Their Reporting API is great. SyncHub is great.

Happy reporting everybody!