Before you rebuild SSRS and Excel reports in Power BI: what to decide first

Power BI programmes often start with a simple brief: move the SSRS reports and the key Excel workbooks across. It sounds like a like-for-like job. Rebuild each report, check the numbers, switch off the old one.

That approach carries the old problems into the new platform: duplicate reports, conflicting definitions and undocumented manual steps. Only now they sit in the tool that was bought to give everyone one set of numbers. Here is what to ask and decide before the rebuild starts.

Why like-for-like is a trap

A like-for-like rebuild treats today's reporting as the specification. But today's reporting was not designed. It accumulated. Every report reflects a request, a workaround or a copy, and few were ever reviewed together.

• Duplicates get rebuilt twice, by different developers, with slightly different logic.
• Conflicting KPI definitions get built into the new platform, sometimes more than once.
• Reports nobody uses get the same effort as the ones the board relies on.
• Manual steps that held the old process together are lost, because nobody wrote them down.

Power BI also works differently. In SSRS and Excel, each report usually carries its own logic. Power BI works best when a few shared, governed models serve many reports. A one-for-one rebuild misses the chance to consolidate, and the savings that come with it.

What to ask about SSRS

Report servers tend to hold more than anyone expects, including reports for teams that no longer exist.

• How many reports are on the server, and which are actually run?
• Which reports are sent out on a schedule, to whom and how often?
• Which reports leave the business, to a regulator, a lender or a client?
• Which reports are tied to others, so that moving one means moving several?

Scheduled deliveries deserve special attention. A report nobody opens on the server may still go to a regulator or a client every week.

What to ask about Excel

Excel is harder because there is no server. Workbooks sit in shared drives, SharePoint and personal folders, and the important ones are not always the obvious ones.

• Which workbooks are reports, and which quietly act as data sources for other reports?
• Where does business logic live in a spreadsheet, especially hard-coded rates and adjustments?
• Which workbooks rely on someone typing or pasting numbers each period?
• Which workbooks would stop working if a folder or file moved?

Pay close attention to workbooks that many others draw on. They are often the real data layer, even if nobody calls them that.

Expect gaps. Some of what a workbook relies on will be out of reach or impossible to confirm. Ask for each gap to be recorded with a named person to confirm it, rather than assumed not to matter.

Decisions to make before the rebuild

With that picture in hand, make these calls before a developer starts building.

• Scope: which reports move, which retire and which merge. Every report you retire is one you never rebuild or test.
• Shape: how many shared models the new platform needs, and which reports each one serves. Group by subject and audience, not by where reports came from.
• Definitions: one agreed definition for each KPI, signed off by its owner.
• Sources: whether each report should draw on governed company data or, for now, a file. Workbooks acting as data sources are candidates for proper engineering.
• Fixed layouts: which SSRS outputs genuinely need a printable layout, such as statements and regulatory returns. Power BI can produce these, but many reports do not need one.
• Manual steps: which are removed, and which need an owner and a written process.
• Sign-off: who confirms each report is right after the move, and what it is compared against.

Sequence the work by what relies on what

Move sources before the reports that draw on them. If four reports depend on a consolidation workbook, decide its future first. Rebuilding reports on a source you later replace means paying for them twice.

Start with a small set of valuable, well-understood reports to prove the approach and the sign-off process. Leave the tangled, single-owner reports until the team has mapped what they touch.

Run old and new side by side, briefly

Parallel running builds confidence, but agree an end date up front. If users can keep their old workbook indefinitely, they will, and you pay to support two estates.

Compare outputs for an agreed set of periods. Where numbers differ, check first whether the difference comes from a definition change you agreed. Those are expected. The ones you did not agree are the ones to chase.

The short version

Do not move reports. Move the decisions about reports. Know what you have, decide what each report deserves, agree the definitions, then build the smallest Power BI estate that does the job.