The most common reason a dashboard fails is not visual design. It is that someone checked a number, found it wrong, and never fully trusted the thing again. Reporting is only as good as the plumbing beneath it, and most reporting projects treat the plumbing as an afterthought.
What We Build
- Operational dashboards — service, project, dispatch and delivery performance as it actually stands today.
- Financial and margin reporting — profitability by job, agreement, customer and division.
- Executive scorecards — the handful of numbers that genuinely run the business.
- Drill-through lineage — every figure traceable to the records behind it, so a challenge can be settled in a minute.
- Automated distribution — reports that arrive, rather than reports someone remembers to compile.
- Data-quality monitoring — know when a feed has failed before you act on a number that is quietly stale.
Trust Is the Deliverable
We build the lineage and the data-quality monitoring first, because a dashboard nobody believes is worse than no dashboard — it costs the same to build and then quietly gets ignored while people go back to spreadsheets.
That principle is why the executive command center we are building tracks its own integration failures as first-class information.
Start With the Decision
The useful question is not “what should the dashboard show?” but “what decision is being made badly today because the information is not there?” That usually produces a much smaller, much more valuable report than a request for everything.
Tell us what you cannot currently see and we will work back to what it takes to show it reliably.
Related services
- CRM and ERP Integration — Reporting is only as good as the integration behind it.
Who Owns the Definition of a Number?
Before the first chart, agree what the words mean. Revenue can mean something different in the CRM, in the ERP and in the spreadsheet finance keeps for the board. Utilization depends on whether you count holiday, training and drive time. Backlog depends on whether unsigned change orders are in or out. If those definitions live only in the head of whoever built the report, the report has a single point of failure.
Write the definitions down, name an owner for each one, and put them somewhere the dashboard links to. When someone challenges a figure later, the conversation becomes a short one about a documented rule.
What People Mean When They Ask for Real Time
People ask for real time and mean current enough to act on. A dispatch board and a monthly margin review have nothing in common in this respect. Refresh cadence drives load on the source system and how much engineering the pipeline needs, so decide it deliberately per report instead of applying one setting to everything.
A stale number that announces itself is still usable. A stale number that looks live is what destroys confidence in the whole set, which is why the refresh time and the source belong on the page itself.
A Number Should Lead Back to Its Rows
The question that follows every surprising figure is where it came from. Drill-through is the answer: a total that opens into the records behind it, and a documented path from the chart back to the table, the query and the source system it started in. Build that lineage in at the start and a disputed number becomes a two-minute check. Retrofit it later and you are reverse engineering your own report under pressure.
Data quality monitoring is the other half of the same job. Row counts that fall off a cliff, nulls appearing in a field that has never been null, a load that quietly ran against yesterday’s file: none of these announce themselves on a dashboard. They need a check that runs with the pipeline and an alert that reaches a named person.
When a Warehouse Earns Its Place
Reporting directly off a source system is fine while the questions stay inside that system and the volume stays modest. A separate reporting store starts to earn its keep when you need to combine systems, retain history the source overwrites, run heavy queries without slowing the people doing the work, or apply consistent definitions across several tools.
If none of those apply, a warehouse is an extra moving part with an owner and a maintenance cost. Say that out loud before the architecture diagram gets drawn.
Permissions Are Part of the Report
Decide early who sees margin, who sees labor cost, and whether a branch manager sees other branches. Row-level security is straightforward to design at the start and awkward to retrofit once a report is in circulation. It also shapes the data model, because the field that drives visibility has to be present and reliable on every row, including the historical ones.
The Spreadsheet Is Not the Enemy
A spreadsheet is a good answer for an analysis run once, for a model whose logic changes every time, and for anything a finance lead needs to bend live during a call. Spreadsheets become a problem when they turn into a monthly ritual: the same person, the same exports, the same joins, done by hand under deadline.
That ritual is the thing worth automating, and it makes a good first project because everyone already agrees on what the output should look like. Underneath all of it sits integration. If your systems do not agree on a customer or a job number, no reporting layer rescues the comparison, which is why Business Reporting and Analytics work often begins on the CRM and ERP Integration side. The Executive Operations Command Center concept carries the same lineage and monitoring thinking further, and it is still in development.
How the Data Gets Out of the Source System
Every system in scope needs a defined way of handing over its data, and the design of that handover matters more than the choice of chart tool. Two properties decide whether it survives contact with production. It has to be re-runnable, because a load that appends instead of merging on a key produces duplicate rows the first time someone retries a failed run. And it has to be observable: rows read and rows written, per table, per run, written to a log. Without that log a failed load is an investigation. With it, it is a lookup.
Extraction also needs its own identity. A dedicated service account with read-only scope on each system keeps the pipeline running when a person changes roles, and keeps pipeline activity separable from human activity in the source system's audit log. Personal credentials embedded in a scheduled job break at the next password rotation and leave no clean way to review what the job touched.
- Vendor REST API: supported and version-stable, but paginated and rate limited, so the initial history backfill runs long and the daily job has to be incremental.
- Direct SQL against a read replica: fast and flexible, but it reads the vendor's physical schema, so a product upgrade can rename or split a column and break the extract without an error anyone sees.
- Scheduled flat-file export to SFTP: often the only route a hosted vendor allows, but a file carries no schema contract and no signal for deleted records, so all of the validation has to live in your pipeline.
- Webhook or change feed: gives you minutes instead of hours, but events that fail delivery are not always resent, so it still needs a scheduled reconciliation pass behind it.
- Vendor-supplied OData or ODBC connector: quickest to stand up, and usually the slowest part of the pipeline once history and joins are involved.
Incremental Loads and Deleted Records
After the first full load, most pipelines pull only what changed, usually by filtering on a last-modified timestamp. That filter is only as honest as the column behind it. Bulk updates, data fixes applied directly against the database, and writes that arrive through the vendor's own integration layer do not always touch the modified date, so rows change in the source and never reappear in the extract.
Deletes are the harder half. A delta pull by modified date can only tell you about rows that exist. A record hard deleted in the source simply stops being returned and stays in the reporting store indefinitely unless something is looking for its absence. Systems that soft delete are easier, provided the extract carries the deleted flag through rather than filtering it out at the source, because a row that was voided and a row that was never there should not have the same effect on last month's total.
The check that catches both is a periodic full key reconciliation: pull primary keys and a row count from the source, compare them against the reporting store, and report the difference rather than correcting it silently. Cadence follows table size. It is a small job next to a monthly total that has been drifting for a quarter without anyone noticing.
History the Source System Overwrites
Operational systems store current state. A work order has one status field, an opportunity has one stage, a customer has one credit limit, and each write overwrites what was there before. Every question of the form how did this look at the end of last month, or how long do jobs sit in awaiting parts, is a question about a value the source no longer holds.
The mechanism that answers it is a snapshot: capture the rows you care about on a schedule and keep each capture with the date it was taken, or keep effective-dated versions so every change writes a new row with a start and an end date. Both are decisions made before you need them, because a live table cannot be made to reconstruct a status it already overwrote. Pick the grain deliberately, since a daily snapshot of open work orders is small and a daily snapshot of every transaction line is not.
Snapshots also settle an argument that surfaces later: whether a report restates history or freezes it. A report built on the live table shows last quarter as it stands now, and it changes every time a back-dated correction lands. A report built on snapshots shows the quarter as it stood when it closed. Neither is wrong, but finance and operations often want different ones, and the two answers should not share a title.
Where the Report Actually Lands
Delivery surface decides use. Figures placed in the screen someone already works in get read as part of the job; the same figures behind a second sign-in get read when a meeting forces it. For the people who live in the CRM, the ERP or the field service application, that argues for the report appearing inside that system rather than beside it.
Embedding has its own requirements. The host application has to support either an iframe or a native report object, and the viewer's identity has to pass through to the report. If it does not, every viewer inherits whatever the embedded credential can see, which quietly defeats row-level security no matter how carefully the model was built. Confirm both before the report is promised as an in-system feature.
Licensing and access mode should be confirmed against the vendor's current terms before access is promised to everyone in the field. In the Microsoft stack in particular, what a recipient needs depends on the capacity tier the content sits on and on how it is delivered, and those rules have changed more than once. Establish the mode and the head count you are actually provisioning for, then design distribution around it.
External recipients are a separate design, not a permission setting. Showing a customer their own tickets or shipments means an identity that is not in your directory, a filter bound to that identity, and a surface that cannot expose the internal report's other rows. That is portal work. Treating it as an internal report link with a filter applied is how the wrong customer ends up looking at someone else's data.
A Report Cannot Show What Was Never Captured
The ceiling on any report is what the source recorded at the time, not what the pipeline can move. The usual gap is a field that is optional in the source system, or a step in the process that happens outside every system.
The pattern shows up most often in field service. If technicians close calls with a free-text note and no resolution code, no reporting layer can produce first-time fix rate or failure cause by equipment type, because the data to compute it was never written down. The fix is upstream: make the field required, give it a short and honest list of values, and let it accumulate. That report starts when the data starts.
A populated field is not automatically a usable one. Profile the column before designing anything on it: fill rate, distinct values, and the share held by the top value. A picklist where most rows sit in Other is a field that was built for a workflow rather than for a report, and it will produce a chart that looks fine and means nothing.
Frequently Asked Questions
Is this the same as buying a reporting tool?
No. A license gives you a tool and a blank canvas; the license is not the work. It is also not data science. This is reporting on what happened and what is happening now, drawn from the systems that already hold the records, rather than forecasting or predictive modeling. If you want something that predicts churn or demand, that is different work with different inputs.
What does it connect to, and where does it run?
The usual sources are the CRM, the ERP or accounting system, the field service or dispatch application, project and job costing, timekeeping, and document management, plus Microsoft 365 content such as SharePoint lists and Exchange calendars. The binding constraint is what each source permits, and that differs between a vendor-hosted instance and a self-hosted one of the same product, so it gets settled per system rather than once for the project. Where the reporting layer itself runs follows from that and from where your identity and security already live.
What has to already exist before this is worth starting?
A system of record for every number in scope, and a route into it. The systems also need a shared key, or a crosswalk that maps a customer and a job across them, with a named owner who maintains it as new records appear. Nothing has to be clean first; the gaps a data profile turns up are usually cheaper to fix in the source than to work around in the pipeline.
How does the work actually proceed?
The sequence is: inventory the source systems and confirm the access route into each, profile real records to verify the fields the report needs are actually populated, build the extract and load with its run log and checks, implement the agreed definitions once in a model rather than repeating them in each chart, then build one report end to end with drill-through before building the rest. The first report is deliberately narrow, because it is what proves the access path and the refresh under real conditions. A disagreement is also cheaper to resolve against something working than against a mockup.
What do we have to provide?
A named service account with read scope on each source system, the report you are replacing as its actual output rather than a description of it, someone with the authority to declare a number wrong and have that stand, and a sandbox or test instance where one exists. Access provisioning is often the slowest item, because on hosted systems it is a vendor ticket rather than a decision on your side, so it is worth starting before anything else.
Can we get history for the periods before this is built?
Sometimes, and only if the source kept it. Some systems maintain audit or change-log tables that record field-level edits with a timestamp and a prior value, and where those exist and cover the fields in question, past states can be reconstructed from them. Many retain them only for a limited window, and some record who changed a record without recording what it was before, which is enough for compliance and useless for a trend. Check what is there before an as-of comparison is promised.
Why do two reports of the same number disagree slightly?
Almost always timing or a boundary rule, not arithmetic. Common causes are a time zone gap between a stored UTC timestamp and a local day boundary, one report filtering on transaction date while the other uses posting date, one counting records created in the period while the other counts records modified in it, and one excluding voided or canceled rows that the other keeps. Compare row counts before totals: matching counts with different totals point at a field or a rate, while different counts point at a filter or a date boundary.