The pipeline-to-cash report shows the same customers all the way through: what was forecast in the CRM, what was won, what was invoiced in the accounts, what was collected, and how long it took, by customer, by the marketing source that found them and by the rep who closed them. It's the report that tells you whether the deals you're celebrating are the ones that pay, which sources produce customers who stay, and where in the chain the money slows down. It needs two systems (the CRM and the accounts) joined on one customer identity, and that join is nearly always the whole problem, because the CRM has "Acme Ltd" and the accounts have "ACME LIMITED (A0142)" and nothing links them. Fix the key and the rest is a model; skip the key and the report is a guess.
What does the report show?
Across the top, the chain for the period: pipeline created, won, invoiced, collected, each as a value, with conversion between each step. Then:
- By customer: won value, invoiced value, collected value, outstanding, debtor days, margin (if the accounts hold cost at the right grain, see Gross Margin by Customer and Product). The customers where "won" and "invoiced" diverge are the ones to look at.
- By source: the marketing or lead source recorded in the CRM, followed through to twelve-month revenue and payment behaviour. This is the honest measure of marketing spend, and it's the one the CRM's own attribution report can't give because it stops at "won".
- By rep: won versus invoiced versus collected. The rep who closes big deals that discount heavily and pay late looks different here from how they look on the leaderboard.
- Timing: median days from won to first invoice, from invoice to payment, by customer segment. Where the cash slows down.
- Reconciliation: the model's won total against the CRM's won report; the model's invoiced total against the accounts' sales for the period. Both must tie.
The five joins
| Join | What has to match | Where it usually fails | The fix |
|---|---|---|---|
| Customer | CRM company record to accounts customer account | Names differ; one CRM company maps to several accounts customers (branches, entities) or vice versa | Store the accounts customer ID on the CRM company record (an external ID field); build a mapping table for the one-to-many cases |
| Deal to invoice | A won opportunity to the invoices it produced | Nothing on the invoice says which deal it came from | Put the CRM deal ID on the sales order or invoice reference; or match by customer and date window and label it "matched by rule" |
| Currency | Deal currency vs invoice currency vs reporting currency | Deals in USD, invoices in GBP, or mixed | A rate table and a single reporting currency in the model; show both |
| Dates | Close date vs invoice date vs payment date | Close date is the rep's guess; invoice date is real | Report timing from real dates; treat close date as forecast only |
| Product or service | CRM line items to invoice lines | CRM products don't exist or don't match the item list | Map at category level if line level isn't possible; say so |
Source: the mapping problems I hit on every CRM-to-accounts build; the fixes are the ones that have held.
The first join is the one that matters. If the CRM company record carries the accounts customer ID, the rest is a day's modelling. If it doesn't, populating it is the first job, and it's a people job: someone in sales operations or finance goes through the customer list once, and from then on it's part of creating a customer. The Dynamics 365 Sales to Business Central integration does this for you, which is the one pairing where the join comes for free.
Where does the data come from?
The CRM side: Dynamics 365 Sales through the Dataverse connector and Salesforce through its native connectors (with API access on your edition), both at no extra cost; HubSpot and Pipedrive through a third-party connector or the API. Won opportunities with close date, amount, owner, source and the customer key.
The accounts side: invoices, credit notes and receipts at line level with customer, date and (if you have it) the deal or order reference. Business Central natively; Sage 50 via ODBC; Sage 200 via its connector or SQL; Xero and QuickBooks Online via third-party connectors, an export pipeline or the API. The routes are compared in How to Connect Your Accounting Software to Power BI.
The model: a customer table (the one list both sides map to), a calendar, the CRM opportunity table, the invoice and receipt tables, and measures for each step of the chain. Row-level security so each rep sees their own customers and each manager their team.
What breaks, and how the report stays honest
Unmatched records. Some won deals won't match an accounts customer, and some invoices won't match a deal. Don't drop them. Show them: "won, no invoice found", "invoiced, no deal found", with values. The first list is either deals that haven't been invoiced yet (a cash question) or deals that were never real (a CRM hygiene question). The second is business the CRM never saw. Both are useful and both are hidden if the model quietly inner-joins.
Amount drift. The deal said £50,000; the invoices total £42,000. That's discounting, scope change or a deal entered optimistically, and the report should show the difference by rep and by customer rather than pick one number.
Timing. Won in March, invoiced in May, paid in August. The report's period logic has to be explicit: "won in period" and "invoiced in period" are different populations, and the chain view should say which it's using.
The two reconciliations. The model's won total ties to the CRM's own won report for the period, and the model's invoiced total ties to the accounts' sales for the period. Both on a page, every refresh, differences shown. If either doesn't tie, the model is wrong, not the systems.
What does it cost?
This is a two-source build by definition, so it's a Reporting Sprint in my published pricing (see the Reporting Sprint, £9,500): the shared customer key, both connections, the model, the report, both reconciliations, and the handover. If the CRM and the accounts are Dynamics 365 Sales and Business Central with the integration in place, the key exists already and the job is smaller.
If you want to start with one end of the chain, the pipeline page from the CRM alone (Sales Pipeline Report) or the debtors page from the accounts alone (The Aged Debtors Report) is a single-source First Report at £1,950 (see the First Report), and building the customer key mapping during it is a good use of the ten days.
Power BI Pro is £10.80 per user per month on Microsoft's UK page as of September 2026, paid yearly, excluding VAT, for each viewer.
Common questions
What is a pipeline-to-cash report?
A report following customers from CRM forecast to won deal to invoice to payment, by customer, source and rep, with conversion and timing between each step. It needs the CRM and the accounts joined on one customer key.
How do you link CRM records to accounting customers?
Store the accounts system's customer ID on the CRM company record (an external ID field), populated once for existing customers and as part of the process for new ones. Where one CRM company maps to several accounts customers, a mapping table handles it.
Can HubSpot and Xero be reported on together?
Yes, in a model that reads both (HubSpot via a third-party connector or its API; Xero via a third-party connector or its API, since neither has a native Microsoft connector as of September 2026) and joins them on a customer key stored in both.
Can Salesforce and Business Central be reported on together?
Yes. Salesforce through its native Power BI connectors (API access needed on your edition) and Business Central through its native connector, joined on a customer key. Dynamics 365 Sales with Business Central is easier still because the standard integration creates the shared IDs.
Why don't our CRM won figures match our invoiced revenue?
Because deals are entered at an estimated amount, discounted or re-scoped before invoicing, invoiced in a later period, or never invoiced. The report should show the difference by customer and rep, and list won deals with no invoice, rather than pick one figure.
Which marketing source produces the best customers?
The report answers it by following each source from CRM to twelve-month invoiced revenue and payment behaviour. The CRM's attribution stops at "won"; this continues to "paid", which is a different ranking more often than people expect.
How much does a CRM-plus-accounts report cost?
It's a two-source build, so a Reporting Sprint at £9,500 in my published pricing, including the customer key mapping and reconciliations at both ends. Power BI Pro is £10.80 per viewer per month (Microsoft UK, September 2026).