Migrating from SSRS to Power BI Paginated Reports: A Practical Guide

Is it just me or do many despise working with Paginated Reports? I love it... maybe it's the nostalgia of working with SSRS many years back and producing a cool suite of reports. I even had a joke between me and an ex-colleague who was stuck with doing the development work on a large Tableau to Power BI migration that had... yes, lots of Paginated Report development.

Here's the thing, if you're heavy in SQL Server Reporting Services (SSRS) and eyeing a shift to Power BI, you're not alone. In fact, this is why I'm writing this. In the last 6 months I have had 3 conversations with potential clients on this exact topic. Many organisations are looking to migrate their SSRS reports to Power BI Paginated Reports for better integration. But where do you even start? In this blog, I'll break down the key differences, why it might be worth the effort and the core part of this blog are the options for migrating. We'll also touch on our approach at Metis BI to make it smooth - without giving away all the secrets, of course 😉

Overview of Paginated Reports (RDL) vs Power BI Standard (PBIX)

First things first, what's the real difference between a Power BI Paginated Report and a standard Power BI report?

Note: You will find many blogs online saying migrate from "SSRS to Power BI", but this can be misleading, as there is a huge difference between just Power BI (.pbix) and Power BI Paginated Reports (.rdl). So, when they say to Power BI - think PBIX or RDL.

Now, paginated reports (.rdl files) are all about pixel-perfect, printable outputs, so think invoices, detailed statements or other traditional style statements, that span multiple pages. It's those reports we need all in the same location with margins and headers/footers consistent across pages, ensuring everything aligns precisely for professional printing or sharing. To build these reports, we use the tool Power BI Paginated Report Builder, which works with structured data from a variety of sources, such as SQL Server databases, Azure databases, Power BI Semantic Models and more. Also, you can publish paginated reports to a Power BI workspace within the Power BI Service as you can with a standard Power BI report.

On the flip side, standard Power BI reports (.pbix files) are interactive dashboards created in Power BI Desktop. They're great for visual exploration, with features like drill-throughs and slicers, but not ideal for high-volume printing or fixed layouts. Think of paginated reports (.rdl) as your go-to for tabular/operational style reporting, while standard Power BI reports (.pbix) shine in visual, interactive reporting and analytics. Also, Power BI Desktop is a user-friendly tool designed for the average business user to create interactive visuals with ease, while Power BI Paginated Report Builder is more technical, geared toward developers handling structured, precise reporting. It's imortant to keep that in mind, that not everyone can develop paginated reports with ease.

Now, here's an interesting question I think everyone should be asking... Just because you've got reports built in SSRS, does that mean they all need to be rebuilt in Power BI Paginated Reports? Not really! Maybe some aren't even needed anymore or they could use a serious revamp. The point is, when migrating SSRS reports to Power BI, most often go with Paginated for that familiar .rdl vibe. But hey, any report migration is a great opportunity to clean up your reporting architecture and make sure everything we do actually advances your reporting, so maybe that paginated report should now be a standard Power BI report.

Overview of SSRS vs Paginated Reports

SQL Server Reporting Services (SSRS) has been a go-to for on-premises report servers, handling everything from connecting to various data sources, to setting up subscriptions for automated report delivery through email. It uses .rdl files (same as Paginated Reports) to define report data, layouts and parameters, deployed via the Web Portal (formerly known as Report Manager). But Power BI Paginated Reports build on that same .rdl foundation and bring it into the cloud. They use the same underlying technology as SSRS but are published directly to the Power BI Service, with deeper integration across the Microsoft ecosystem, including easier hybrid access through data gateways, connectivity to Azure services and collaboration through shared workspaces.

Why Migrate to Paginated Reports from SSRS If the Underlying Tech Is the Same?

Good question! Well, if both use .rdl files and its the same underlying tech, why bother migrating SSRS reports to Power BI Paginated? Well, we briefly mentioned some aspects of this above, but the tech overlap is there and the benefits go beyond that. For starters, Power BI's cloud service means easier collaboration in workspaces and no need to maintain a separate report server - unless of course you are storing the paginated reports in the PBI Report Server.

Plus, if your organisation is already on Power BI, consolidating reports reduces silos, it's a unified reporting platform that handles both operational (paginated) and analytical (interactive) reports in one space, cutting down on some effort. In the early days, tons of key features we relied on in SSRS were straight-up missing from Power BI Paginated Reports, but I've got to say, that gap has closed significantly over time. Migrating to Power BI Paginated Reports can also potentially yield cost savings by reducing on-premises infrastructure needs, SQL Server licensing expenses (if SSRS was a primary driver) and ongoing maintenance labour, though it introduces Power BI costs.

It's also about future-proofing as SSRS is solid but it's clear to see that Paginated Reports is the new way to go.

Note: You no longer need Premium Per User (PPU) or a Fabric Capacity (Premium) to host paginated reports in Power BI. We can use Power BI Pro licensing.

Options for Migrating to Paginated Reports (from SSRS)

Alright, by now we should have a solid understanding of standard Power BI reports (.pbix), Power BI Paginated Reports (.rdl) and SSRS (.rdl). So, when it comes to migrating those SSRS reports to Power BI Paginated (not the standard .pbix ones), you've got a few paths to choose from, each with its own pros and cons. Based on our experience at Metis BI (plus Microsoft's guidance through various resources), here's a quick rundown:

Manual (One-by-One):

This is your hands-on approach, ideal when each report needs individual review, like if it uses unsupported data sources or features, requires complex changes or your SSRS version doesn't play nice with the automation. Download the .rdl files from the SSRS portal, open them in Power BI Report Builder, update data sources or features manually, then click the Publish button to a Power BI workspace. Great for complex customisation but time-intensive if you've got 100+ reports.

Automated (Publish Button):

If you're using SQL Server Reporting Services 2017 (with build update 14.0.601.20 or later), 2019 (with build update 15.0.1102.1047 or later) or 2022 or the Power BI Report Server, whether on-premises or hosted in VMs, the Publish Button feature allows you to upload paginated reports directly to the Power BI Service.

If your environment supports this, its an easy and fast option. You can choose to publish all reports or selected reports. Whilst publishing, shared data sources and datasets become embedded, provided they use supported connectors. So, after signing into Power BI and selecting a workspace (requires right licensing - minimum Pro), the tool scans for unsupported elements and returns a success/failure summary, without disrupting your existing SSRS setup. For more information on this option, check out this helpful MS link.

Also, I want to call out, when initially reading documentation I thought it was purely for SQL Server 2022 Reporting Services, but have a look at this link here and it it makes it clear they expanded support for other versions too.

Automated (RDL Migration Tool):

The last option is Microsoft's RDL Migration Tool. It's a tool developed by Microsoft to help migrate .rdl reports from your SSRS servers to Power BI. This is especially the case if you're on earlier versions like SSRS 2016 or older or if the Publish Button highlighted above simply isn't available in your setup.

Use the RDL migration tool to parse .rdl files from your report server, as it checks for unsupported data sources and report features, automatically converts any shared resources (such as data sources and datasets) to embedded ones and then deploys the reports that pass those checks as paginated reports to a specified Power BI workspace.

It's ideal for bulk migrations, following a 3-step approach of assess, convert and publish workflow, connecting to on-premises or Azure data without causing downtime or altering your original SSRS server. It even outputs a summary of what's successful or unsuccessful to keep you in the loop.

For more information on this option, check out this helpful MS link.

Metis BI Approach

At Metis BI, we don't just wing migrations or dive in without a solid plan. The key to a successful outcome is a strong, structured approach from the start. Without the right planning and support, I've seen way too many cases where teams migrate reports only to end up with endless performance headaches, dragging over reports that simply weren't needed anymore and spiking resource usage way higher than necessary, which just drives up costs. Also, one of the worst scenarios, the users refuse to use the new reports/solution.

We use a structured 4-phase process to ensure efficiency and minimal disruption. It starts with Identification, where we collect key metadata on reports like usage, data sources, and visuals. Then Classification to assign the relevant classification to ensure we cleanse the reporting architecture and not just migrate everything. Prioritisation is the next phase, where we decide what moves first, based on business criticality across departments. Finally, Migration initiates the shift, generating phases for a backlog and structured transition to Power BI.

We also create a tiered system when running report migration projects, especially through the discovery stage. This helps gauge effort and offer realistic costs and estimates. Our discovery phase is key, involving high-level reviews of data architecture, validating the Power BI Service setup, listing all .rdl reports, RLS and much more. We test select reports manually to confirm they work as expected, then revise plans with sign-off.

Data governance and change management run throughout, ensuring secure, compliant shifts. It's not just about moving reports, it's about enhancing your overall reporting. If you are on the report migration journey or about to start it, please please think governance, change management, training and ways to help the organisation use the solutions. This is of upmost important to make a report migration project a success.

Wrapping up, migrating from SSRS to Power BI Paginated Reports can transform your setup, but it needs the right strategy. If you're planning to migrate SSRS reports to Power BI or just curious about the steps, why not chat with us? We're experts in making this painless.

‍