Reporting Automation

The monthly report comes out of exports, formulas and macros in an Excel file that has grown over the years. Someone copies numbers together, the file travels by email through several hands, and in the end there are three versions, none of which is certain to be the last. On top of that, the macros have become so complex that nobody dares change them, and deviations from plan only show up afterwards.

For us, automation covers more than sending: the whole path. Data from the ERP, accounting or CRM is loaded and checked regularly, metrics are defined once, and the finished report goes to the right recipients on a schedule and after approval. How metrics are defined in detail belongs to data modeling. This page is about what happens automatically with the finished numbers.

The examples here mostly refer to Power BI, because it is in use at many companies. Other BI tools offer comparable functions for subscriptions, alerts and print-ready reports, and we automate there as well.

Replacing Excel or connecting it

A report in Excel is not the problem. The problem is the way the numbers get into it. Instead of copying exports, we connect Excel to the data model. Power BI offers “Analyze in Excel” and export with a live connection, and the Excel add-in can insert connected PivotTables and tables. On refresh they show the current state of the model, and row-level permissions continue to apply.

That way business teams work in a familiar interface, add assumptions and check individual figures, while the data itself comes from one source. What is needed permanently, such as the monthly report, moves into a fixed, automated report. We begin with the two or three reports that cost the most time and carry the experience over to the rest. Switching everything at once rarely goes well.

When a report needs to be paginated

Interactive dashboards suit analysis, but not every report is a dashboard. Monthly reports, board papers or attachments for auditors need a fixed layout. That is what paginated reports are for, built in Power BI with Report Builder, for example. They output all rows, keep their page layout and can be exported as PDF, Excel, Word, PowerPoint or CSV. Tables with hundreds of rows, headers and footers, page numbers and fixed breaks are then no longer a special case but part of a template that refills itself on every refresh.

Distribution and its limits

Subscriptions send reports by email at a fixed time or after the data refresh, with the full output as an attachment for paginated reports. With dynamic subscriptions each recipient gets a version of their own, for example per cost center or region. Which parameters apply to whom is then held in a data model of recipients. Saving to OneDrive or SharePoint and further destinations such as Teams can be added with Power Automate.

There are limits, which we clarify beforehand. Attachments are limited in size, subscriptions for external recipients and per recipient require capacity, and the preview image in the email shows the data from the subscription owner’s point of view. That last point is touchier than it sounds: subscriptions run in the name of their owner, and for data with row-level permissions you have to check what is visible in the mailing. Also, if the person who owns a subscription leaves the company or their license expires, the subscription ends. So we settle who owns which mailing.

Alerts, commentary and AI summaries

Not every value needs to be hunted down in a report. Alerts speak up when a metric crosses a threshold. In Power BI they exist for tiles on dashboards, and for reports there is also Fabric Activator, with notifications arriving by email or in Teams. You should only monitor values that someone will actually act on. With the business teams we settle which thresholds make sense, who is notified and what should happen then. Otherwise the alert quickly turns into an email nobody reads.

Numbers alone explain little, so monthly and board reports need commentary. A person still writes it, ideally in a fixed place in the report. AI can supply a draft: Power BI can add a Copilot summary to subscription emails on request. Such texts can misread connections, and for reports that reach the board or banks we use them as help with wording, not as a substitute for review.

Keeping wrong numbers from going out

Automatic does not mean unchecked. Without checks, automation just distributes bad data more reliably. So the data checks for completeness, plausibility and reconciliation with totals in the source system belong in the data pipeline. Important reports go out only after approval, for example via an approval step in Power Automate, and changes to reports are tested before publication, for instance in a deployment pipeline with development, test and production stages. Who approves and maintains a report has to be settled.

For the switch we set a cut-over date, for example the next month-end close. Until then the old and new reports run in parallel, the numbers are reconciled and differences resolved. After the date, the old file is no longer maintained.