Guide · Automate weekly reports

How to automate a weekly report without replacing your software.

Most weekly reports are rebuilt by hand from the same few systems. Automating one rarely needs new software. It needs every number mapped to its source, the rules written down, and a parallel run before anyone relies on it.

By Sunny BinjolaOctober 2, 2026From our own work

Every example labelled observed comes from our flagship engagement, a five-center behavioral health network, where leadership boards now rebuild each morning. No time saving is claimed.

See the six steps See the dashboards
The same report, two waysIllustrative
Fig. 1 Same sources, same report. Red is done by hand; green runs on a schedule; navy is a person.
01Six steps

Six steps, in order.

The order matters. Building before the rules are written automates whatever disagreement already exists.

  1. Name the decision it serves

    A report exists to answer a question someone acts on. Write that question at the top. If nobody can name it, automate nothing yet.

    Observed at the networkWe framed the daily census board around one question: which programs need attention today?

  2. Map every number to its source

    For each figure: which system it comes from, who owns that system, and how it can be read. Gaps show up here, before any build.

    Observed at the networkThe census lived in four separate EMR instances, site spreadsheets and the billing workbook.

  3. Choose how to read each source

    An API where one exists and is allowed, a scheduled export where it does not, or a shared spreadsheet. Nothing has to be replaced.

    Observed at the networkEvery center's census is read from the EMR's API each morning, read-only. Where an account has no API, exports still work.

  4. Write the rules down

    Definitions decide the numbers: what counts as enrolled, which day a week ends, what a target means. Agree them in writing.

    Observed at the networkBreak-even comparisons appear only where finance confirmed the assumptions.

  5. Run it beside the manual report

    Before anyone relies on it, produce both versions for a few cycles and investigate every difference.

    Observed at the networkThe census engine ran beside the manual census for a week, and the operations lead signed off with three notes.

  6. Publish where people already look

    Send it to the inbox, shared drive or tool leadership already uses, with the time each source was read. Keep the manual route as a fallback.

    Observed at the networkDaily census and clinical boards rebuild each morning and go to the shared location leadership already uses. The manual export still works on any day the API does not.
02Freshness

Show how old every number is.

An automated report that fails quietly is worse than a late manual one. Put the time each source was read on the report itself, and mark anything stale instead of hiding it.

Fig. 2 When a source fails, the report says so, keeps the last good figures, and the owner is told. How support works.
03Mistakes

Mistakes that make an automated report worse.

  • Reading only the first page. Many systems return data in pages. A report that reads one page looks complete and is wrong.
  • Hiding stale data. A number that silently stopped updating is the most dangerous one on the page.
  • Changing a definition quietly. When a rule changes, the report should say when and why.
  • Automating a report nobody reads. Name the decision first; some reports should be retired, not automated.
  • One person holds the script. Document the rules and the sources, and agree who maintains it.
  • Skipping the parallel run. Trust is earned by matching the report people already believe.

Deciding which report to automate first is part of an audit. The automation audit checklist · Operations audit and automation

04Questions

What owners ask about automated reports.

Do we need Power BI or another new tool?

Not necessarily. The report can stay in the spreadsheet or tool your team already reads. What changes is how the numbers get there: on a schedule, by rules, instead of by hand.

Can the report stay in Excel?

Yes. Many automated reports still land as a spreadsheet. The work is in reading the sources, applying the rules and checking the result, not in the file format.

What if one of our systems has no API?

Then a scheduled export is read instead, or a shared spreadsheet the team already keeps. The API is a convenience, not a requirement.

Who maintains it when something changes?

Agree it before launch: who is told when a source fails, who approves a change to a definition, and what counts as new work. Document the rules so they do not live in one person's head.

How do we know the automated numbers are right?

Run it beside the manual report first and investigate every difference. After that, show when each source was last read, so a stale number is visible rather than silent.

Which report takes half a day to build?

Bring it to a free 20-minute call. We will map where its numbers come from and tell you what we would automate first, if anything.

Book a free 20-minute call Read the case study