Converting SSRS Reports to Power BI Paginated Reports

The moment that Microsoft announced that Power BI Paginated reporting was becoming a Pro feature (link), I finally realized that I would be able to move from 2 different reporting platforms (Power BI Portal & SQL Server Reporting Services) back to 1 (Power BI Portal).

Quick Overview

SQL Server Reporting Services Report

Power BI Report Builder

Publish to the Power BI Portal

Configure the Power BI Portal

SQL Server Reporting Services Report

In this blog post, we will be going through the process to convert a Reporting Services report to a Power BI Paginated report and deploy it in the end.

For this blog post, I have created a very basic SSRS report named Product Sales Overview. This report gets his data from an Azure SQL Database which contains the default dummy database (AdventureWorksLT).

This report was created in a Reporting Services Project in Visual Studio. To get started, we need to locate the .rdl file of our existing SSRS Report.

I have copied the .rdl file to a specific location on my local machine which I will use later on to push it to a separate Azure DevOps Repository (not included in this blog post).

Power BI Report Builder

If you don’t have Power BI Report Builder installed on your machine yet, you can download it using the following link: Power BI Report Builder

As soon as you have installed Power BI Report Builder, just open the application and choose open.

As a next step, navigate to the folder where you have saved the .rdl file, select the file and choose open.

As a result, your existing SSRS report should have opened in designer mode in Power BI Report Builder, in my case it looks like this:

To get this report working properly, we now need to specify our Data Source credentials, to get this done, we navigate to the Report Data pane, go to Data Sources and double click on our data source.

To continue, you need to select the right Connection Type first, in my case, I’m connecting to a Microsoft Azure SQL Database.

As a next step we need to Build our Connection string, to do this, click on the Build button on the right of the Data Source Properties window.

Now provide the server name, authentication type, and credentials and choose the database to which you want to connect to. Test your connection by clicking the Test Connection button. Finish by clicking OK.

Now you should be able to Run your report, if you want to run your report before deploying it, choose the Run button at the top left corner of the application. (or hit F5)

As a result, you should be able to see the report with all facts and figures:

Publish your Paginated Report to the Power BI Portal

To get your Paginated Report published to the Power BI portal, we need to go through a couple of steps. As a first step, click the Publish button in Power BI Report Builder. If you are not signed in yet, you will get a popup window to sign in to Power BI.

As a next step, you need to choose the destination of your Paginated report, in my case, I will be deploying it to my “Corporate Reporting” workspace. Then I choose the name for my report, “Product Sales Overview” and I finalize by clicking the Publish button.

If everything went fine, you should get the following window:

If you now open your Power BI Portal, you should see something like:

Configure the Power BI Portal

When you successfully deployed your Paginated report to a new workspace, you will need to configure your data source. Before we get to the configuration part, I would like to show you the error that I received when trying to open my newly published Paginated Report:

To get this error solved, we navigate to the top right corner to the settings icon and then choose “Manage connections and gateways”.

After clicking “Manage connections and gateways”, you will see an overview of your data sources. in my case 1 only. If you would click the refresh button in the status column, you will see that your data source is currently Offline.

As a side note, since I’m connecting to an Azure SQL Database, I don’t require an On-premises data gateway. If you require on-prem database connection, you will need to install a data gateway to be able to access your on-prem data (if you didn’t install it yet)

To solve our connection issues, we click on the 3 dots next to the data source name and choose Settings.

Now, we need to specify the Authentication method, followed by the Username and Password. To continue you will need to specify if you want an encrypted connection and finally, you need to define the Privacy level. if you want to know more about Privacy levels, you can use the following link: Understand Power BI Desktop privacy levels – Power BI | Microsoft Learn

As a final step, we click the save button.

If we now click on the refresh button in the status column we will see that the status switched from Offline to Online. If not, you will need to revise the used authentication credentials.

As the last step, we navigate to the Paginated report and try to open it, now we should be able to see a fully working report in the Power BI Portal.

We now have successfully migrated our first SSRS Report to the Power BI Portal.