Business Reporting Integration: Connect CRM, ERP, and Existing BI
On this page
When sales, operations, and finance show different totals, the problem may start before the dashboard. One system records an accepted order, another records shipment, and a third records payment. Each can be correct while answering a different question.
Choose the decision before the chart
Start with questions someone can act on: which orders need attention today, which customers are waiting for service, or which invoices need follow-up? Name the person responsible for the action and the records they need to investigate.
For each report, write down its purpose, users, filters, update frequency and the path from a summary to the underlying records. This gives the team a way to verify the figures and investigate differences.
Decide where reporting belongs
Build reports inside the operational system when people need the result while doing their work. A service manager might review overdue requests and assign the next action without leaving the CRM. Access rules and record details can follow the same workflow.
Connect the BI environment you already use when it meets the presentation and analysis needs. The project still needs source access, transformations, refresh handling, permissions, and validation. Keeping the reporting tool does not remove integration work.
Synchronise data into a shared reporting environment when several systems contribute to the same view or operational databases should be isolated from analytical workloads. Define the transformation rules, update schedule, retention needs, and failure ownership before choosing the implementation.
These approaches can coexist. An ERP can show operational exceptions while a BI tool supports cross-company planning from an agreed reporting dataset.
Define each measure in plain language
For a late-order report, define exactly what “late” means:
- Is one row an order, a shipment, or an order line?
- Is lateness measured against the original promise or the latest accepted delivery date?
- Which time zone and daily cutoff apply?
- How are cancelled, partially shipped, and rescheduled orders treated?
- Which system owns the promised date and which owns delivery confirmation?
- Can a user see only their location or all operating units?
Record the definition with the business owner. If finance needs a different measure, label the difference clearly instead of forcing unrelated totals to match. When currencies are combined, define the conversion date and rate source as part of the measure.
Check the level of detail before joining datasets. In a separate illustrative example, one order with two shipment rows and three payment rows produces six rows when both lists are joined only by order ID. Summing that result repeats each shipment three times and each payment twice. Aggregate each measure to the agreed order level first, or use a model that preserves each measure's original detail. A plausible-looking total is not evidence that the join is correct.
Agree what the report should mean
Define one measure with its business owner before estimating the dashboard. The template includes a worked late-order example and space for your own rules.
- Decision, row detail and metric definition
- Source ownership, refresh needs and access
- Expected results, reconciliation and sign-off
Editable text file (.txt)
Fill it in locally using a text editor or your own document. Share a non-confidential summary when you contact us.
Reproduce three different totals from one sample
Take a fictional order snapshot and events strictly before 2 October 2026, 00:00 UTC. All amounts are USD, with no tax, currency conversion, refunds or discounts. Shipment value uses the agreed order prices; it is an operational measure, not a claim about accounting revenue recognition.
The source records are deliberately small enough to check by hand:
- Accepted order A is $1,000; accepted order B is $600. Cancelled order C is $400 and outside the accepted-order population.
- Shipment s1: A, $400, 1 October at 10:00 UTC. Shipment s2: A, $600, 14:00. Shipment s3: B, $200, 16:00.
- Shipment s4: B, $400, exactly at 2 October 00:00 UTC. It belongs to the next reporting period under this exclusive cutoff.
- Payment p1: A, $300, 1 October at 12:00 UTC, delivered twice with identical data. Payment p2: B, $100, 17:00. Payment p3: X, $50, 18:00; order X is absent from this snapshot.
Scroll the table horizontally to compare all columns.
| Order | Accepted value (USD) | Shipped value (USD) | Matched payments (USD) |
|---|---|---|---|
| A | $1,000 | $1,000 | $300 |
| B | $600 | $200 | $100 |
| Included totals | $1,600 | $1,200 | $400 |
Unmatched payment, held for review: $50. Repeated payment ignored: 1. Shipment at the cutoff, excluded: $400.
Accepted orders total $1,600. Included shipments total $1,200. Matched payments total $400. Those figures disagree because the measures describe different stages. The extra $50 remains visible as an unmatched payment; it is neither silently discarded nor added to a known customer's total. Deduplicating the identical p1 delivery prevents a false $700 matched-payment total.
The table calculates totals from these fictional records. In a live feed, deduplication must distinguish an identical repeat from a reused identifier with different data; the latter needs a conflict review.
Acceptance for this example: the reporting owner confirms the population and cutoff; the data owner resolves p3; an authorised reviewer can trace each included total to its records. If the business instead wants all received payments, that is a separate measure: $450 after deduplication, with the unmatched $50 still explained. Do not relabel either payment measure as revenue.
Use the CRM–ERP exchange example to discuss retries and record versions. Reporting reconciliation then checks the business result of those exchanges, not just their delivery status.
Give every shared field an owner
List the source system, stable identifier, transformation, and update rule for each shared record. A CRM customer identifier may need a mapping to an ERP account; similar company names are not enough to establish that two records are the same.
Decide how changes and deletions travel, what happens when records arrive out of order, and how duplicate events are handled. For a report that combines orders and payments, define how an unmatched payment is shown while the corresponding order is unavailable. Silently dropping that record hides the integration problem.
Historical migration and ongoing synchronisation are separate requirements. Our modernisation guide explains how to plan both during a transition.
Make data freshness and failures visible
A report refreshed every night can suit a monthly review and still be unsuitable for allocating stock during the day. Choose freshness from the decision, then estimate the work needed to support it.
Show the last successful update for the relevant sources. If one source is delayed, the report should make that limitation clear. Define who investigates failures, how missed records are recovered, and how users know when the figures are current again.
Specify the maximum acceptable delay for each source and check whether its interface can meet it. A daily export and an event feed with retries support different reporting needs.
Reconcile before people rely on the result
Use an agreed sample period and compare counts, totals, and representative records with the source systems. Include cancelled records, amendments, duplicates, partial transactions, and boundary dates. Check permissions as well as calculations: a correct total can still expose information to the wrong team.
Keep a reconciliation log with the expected result, actual result, explanation of any difference, and owner. Some differences are intentional, such as a new definition of the reporting period. Document those decisions so they are not rediscovered as defects later.
After launch, changes to source fields, status definitions, and integrations can alter a report. Include those changes in testing and support responsibilities.
Bring the reporting scope into the estimate
Combine these requirements into a brief, with the available interfaces and examples of disputed figures. Start with a non-confidential description; agree how to share sample data and internal reports separately.
The estimate should cover discovery, mappings, integration, report development or BI configuration, validation, rollout, and support. Our cost guide explains how these fit into a wider software budget.
Horizon Dynamics can build reporting into custom ERP software, connect CRM reporting, or preserve reports during modernisation. Tell us which reports your teams depend on and where the numbers stop agreeing.
