How to build a single source of truth for reporting when your data lives in five systems

A single source of truth is one place where the figures your business reports on are collected, matched up and defined once, so everyone reads the same number. For a growing company whose data sits in several systems that don't talk to each other, you build one in four steps: find out what each system holds and how to get the data out, join the sources on fields they share, agree what the reporting has to show, then pick charts that answer those questions. You don't have to replace your existing software to do it.

A system of record is the system that owns a particular piece of data. Xero is the system of record for invoices and payments. Your CRM owns enquiries and contacts. Your job or project system owns jobs, timesheets and budgets. Each one is right about its own data.

A single source of truth is the layer that brings those records together for reporting. It doesn't replace any of them. It copies the parts you need, links them to each other, and calculates each figure one agreed way, so the finance director and the operations director stop turning up to the same meeting with different turnover numbers.

An example from a construction business: an enquiry arrives in the CRM, becomes a quote in the estimating tool, turns into a job in the project system and gets invoiced in Xero. Four systems, four versions of the same piece of work. In a single source of truth those four records are linked by the quote or job number, so one report can show the whole route from enquiry to cash: what you quoted, what you won, what it cost and what you've been paid.

That is the difference that matters to an owner. The system of record answers what does this system say? The single source of truth answers what is actually happening across the business?

Nobody sets out to build data silos. They arrive one sensible decision at a time. Accounts picks Xero. Sales picks a CRM. The site team needs a job management app. A division you acquired brings its own software, and someone builds a spreadsheet to track tenders because nothing else did. Each tool is fine on its own. The trouble is between them.

The signs are familiar:

• Someone spends days at month end exporting from each system and stitching it together in Excel.
• Two reports give two different answers to the same question, because each one calculates it differently.
• Figures get re-keyed from one system into another, and the errors only surface at year end.
• You find out a job lost money after it finished, not while you could still do something about it.

Growth makes it worse, not better. More people, more divisions and more systems means more joins to make by hand, and the person who knows how the spreadsheet works becomes a single point of failure. If that's where you are now, the monthly pack is usually the first thing worth fixing; our guide to automating your monthly reports and management pack covers that end of the problem.

Start with an inventory. List every system that holds a number you report on, and for each one write down what it captures (invoices, quotes, timesheets, stock, enquiries), who keeps it up to date, and how often it changes. It's common to find two systems holding the same thing, or a spreadsheet quietly acting as a system of record.

Then work out how the data can get out of each one. There are three routes, in order of preference.

1. An API. An API (application programming interface) is the software's own front door for other programs. API integration means a small piece of code asks the system for its data through that door on a schedule, for example every night or every hour. It's the most reliable route because the supplier designed and supports it. Most modern cloud software has one: Xero, most CRMs and many construction job and estimating tools. Check the supplier's developer pages, or ask their support team: is there an API, which plan includes it, and what data does it expose? The last question matters, because some APIs leave out exactly the fields you want.

2. A direct database connection. If the software runs on your own server or a hosted server you control, the data sits in a database underneath it. A read-only database user lets a reporting tool query it directly without being able to change anything. It's fast and complete, but you are reading the supplier's internal tables, which can change when the software is upgraded, so this connection needs checking after every upgrade.

3. Automated collection that does what a person would. Some systems have no API and no database you can reach, often older desktop software or a supplier portal. If a person can log in and run a report, a developer can usually write a script that does the same thing: log in, open the report, set the filters, export it or read what's on the screen, and save the result. This is known as browser automation or screen scraping. It works, and for some systems it's the only way, but it's the least reliable route. When the supplier changes a screen, the script has to be updated, so it needs monitoring and an alert when a run fails.

There's a fourth fallback, a person exporting a file and dropping it in a shared folder, which is fine for something that changes once a month. For anything you want to see daily, use one of the three routes above.

Getting the data out is half the job. Joining it up is where most of the value, and most of the work, sits.

Find the shared fields. Two records can only be linked if they share something: an invoice number, a job or project code, a quote reference, a customer account number. Map each source field to the field it matches in the other systems. This is data mapping, and writing it down as a simple table (system, field, what it means, what it joins to) saves arguments later. A common example is linking Xero invoices to jobs. If the job number is on the invoice reference every time, the join is easy. If it's only sometimes there, you'll need a rule for the rest.

Deal with messy keys. Customer names are the classic problem: "ABC Ltd", "ABC Limited" and "A.B.C. Construction" are the same client in three systems. Fix it once, with a lookup table that maps every variation to one master customer, rather than in every report. Where a shared reference is missing altogether, the cheapest long-term fix is usually upstream: make the job number a required field when the invoice is raised.

Decide which system wins. When two systems disagree, agree in advance which one is right for that field. Xero wins on what was invoiced and paid. The job system wins on hours and cost. The CRM wins on the client contact. Without these rules, the reporting layer turns into a fourth version of the truth.

Keep it somewhere that refreshes itself. Combining data from multiple sources once in Excel is a report; doing it automatically every night into a database is a single source of truth. The joined data lives in one place, refreshes on a schedule, and every report reads from it. People call this data integration or data consolidation; the important part is that nobody rebuilds it by hand.

Check it reconciles. Before anyone trusts it, compare the totals with the source systems: turnover against Xero for the same period, hours against the job system. If they don't match, the difference tells you which join or rule is wrong.

This step is where most reporting projects go wrong, usually by skipping it. The question isn't "what data do we have?" but "what decisions do people make, and what do they need to see to make them?"

Start with who reads it. A board wants a handful of numbers against target and the trend. A divisional manager wants their own jobs, margins and debtors. An estimator wants win rates by client and job type. Each of those is a different view of the same data, filtered and segmented differently, not a different report built from scratch.

Pick the KPIs and write down their definitions. Six to eight headline numbers per view is plenty; more than ten and nobody reads them. Then define each one in plain words: is turnover invoiced or certified? Does gross margin include overheads? When does your financial year start? Two directors with two definitions of the same KPI is how silos survive inside a dashboard. Our article on building a business dashboard lists the KPIs most owner-managed businesses start with.

Agree the cuts. List the filters people will need: by division, client, job type, region, month, financial year. Every one of them has to exist as a field in the joined data, which is why this step should happen before the build is finished, not after.

Set the early warnings. A good KPI report doesn't just show numbers; it tells you which ones need attention. Agree the thresholds that turn a figure amber or red (a job over budget, debts over 60 days, a margin below target) and who gets told when it happens.

Show where each figure comes from. For every KPI, record which system it's drawn from, the formula, and when the data was last refreshed. It's the quickest way to build trust. When someone challenges a number, they can check it themselves instead of asking whoever built the report.

Once you know the question, the chart mostly picks itself. There are dozens of types of charts, but business reporting needs only a few.

Three habits make any dashboard easier to read. Put the headline numbers along the top and the detail underneath. Use colour only to mean something (red is a problem, not a decoration). And keep each view to one screen, so nobody has to scroll to find out whether things are going well.

The four steps are the same whichever tool you use. What changes is how much of steps 1 and 2 the tool can do for you.

• Excel with Power Query is fine for one or two sources that export cleanly and a monthly view, and you already own it. It struggles once you need several joined sources, refreshing without someone pressing the button.
• Power BI and similar BI tools work well when your systems have ready-made connectors and someone in-house can maintain the data model. They don't solve the systems with no API, and per-user licences add up as more people need access. Our article on a Power BI alternative for SMEs goes through when each makes sense.
• Custom-built reporting makes sense when some of your data can only be collected by automation, when the joins need business rules no off-the-shelf tool knows about, or when you want the warnings and approvals built around how you actually run jobs. You own it, and it reads from the systems you already have.

For construction businesses specifically, our guide to construction reporting software covers the commercial and margin reporting side in more detail.

If you'd like to see what this looks like in practice, our data and reporting service page shows a sample dashboard built from several sources. If you send us a list of the systems you use, we'll tell you how each one can be connected.