What to prepare before automating a business report
A useful first project starts with one report and a clear decision. Write down what the reader needs to know, where the information comes from and how you will check it. This makes the scope easier to agree before a dashboard or automated workflow is built.
01 / The question
Choose a decision the report should support.
Replace “we need a dashboard” with a question your team asks regularly. A trades business might want to know which completed jobs have not been invoiced. A restaurant operator might compare sales and reported costs by location. A distributor might review stock movement alongside demand.
Identify the reader, the action they can take and how often they need the information. Keep the first release focused on that question; additional views can follow once the underlying data is understood.
02 / The inputs
Map the source before choosing the tool.
List each workbook, system export or database the report uses. Record who owns it, which fields are available, how often it changes and how access can be granted. An existing export can be a sensible first input; a live connection depends on the source, permissions and licences.
- Which source is authoritative when two totals differ?
- Are identifiers, dates and units consistent across the files?
- Does one row represent an order, an order line, a payment or a snapshot?
- Who can explain missing, duplicate or corrected records?
For Surrey and Metro Vancouver teams working across several locations, agree the reporting period and location definitions together. Describe the sources in the brief; access can be arranged separately.
03 / The meaning
Agree what each number means.
A shared label does not guarantee a shared definition. “Sales” could refer to orders, invoices or payments. Specify the date used, included statuses, treatment of credits and units. Record the calculation in words your team can review.
Fictional worked example
Three lines are not three orders.
| Order | Line | Amount |
|---|---|---|
| A100 | 1 | $120 |
| A100 | 2 | $80 |
| A101 | 1 | $150 |
The expected result is 3 lines, 2 orders and $350. If a merge with status history matches every line twice, the expanded data contains 6 lines and sums to $700. Check the join and record grain before changing the visual.
These figures are invented to explain a data check. They are not a client result.
04 / The evidence
Define acceptance before automating delivery.
Choose a known reporting period and compare the result with its source. Check totals and record counts, then trace a few individual records through the calculation. Agree how differences will be resolved and who signs off.
Include an exception case: a missing file, a blank identifier or a late correction. A successful refresh can still load incomplete data. Decide what should be flagged for review and when a report should be withheld rather than distributed.
Explore the fictional dashboard demonstrations05 / Day to day
Make refresh and recovery part of the scope.
Document the update schedule, source availability and the person responsible for a failed run. A report should make its reporting period and refresh status understandable to its readers.
- Who owns the connection and required permissions?
- Who receives a failure notification and checks the missing input?
- Can the process be rerun safely after a correction?
- What walkthrough and documentation does the team need?
Choose a schedule the source data and existing licences can support. Include any gateway or operational dependency in the agreed design.
06 / A useful first release
Choose the smallest scope that answers the question.
Use the challenge to choose the starting point. The review establishes what the available data can support and which dependencies need attention.
Bring the decision, source list and acceptance check to the first conversation. We can then discuss a defined deliverable, timing, price and handover. You do not need to solve every reporting problem at once.
Work through the four-check example and download a report worksheet
Further technical reading
Microsoft’s documentation explains the data modelling, refresh and error handling concepts behind these planning steps.
