Skip to content

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).

SQL Server Reporting Services Report Example

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.

Power BI Report Builder open .rdl file

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

Select .rld file

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:

Power BI Report Builder Example Report

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.

Power BI Report Builder 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.

Power BI Report Builder Build Connection String

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.

Power BI Report Builder Building The Connection String

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)

Power BI Report Builder Run Report

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

Power BI Report Builder Showing Report

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.

Power BI Sign-in Window in Power BI report Builder

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.

Power BI Report Builder Report Deployment

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

Power BI Report Builder Deploy Success Message

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

Power BI Portal Report Overview

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:

Power BI Portal Error Message Data Source Connection

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

Power BI Navigation to 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)

Validating Data Source Connection Status in Power BI Portal

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

Power BI Portal Configuration Data Source

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

Power BI Portal Data Source Connection  Authentication and Encryption options

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.

Power BI Portal Data Source Status Validation

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.

Power BI Portal showing execution result of a Power BI Paginated Report

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

Please follow and like us:
Published inPower BI

2 Comments

  1. Jack Strudley Jack Strudley

    Good read. I assume this would only work if the report consumers have a Power BI Pro license?

    We’ve got a number of users of paginated reports currently who don’t have a Pro license.

    • Hi,

      Really good question, since you’re using a pro workspace, every consumer should have a Pro license to be able to execute reports.

Leave a Reply

Your email address will not be published. Required fields are marked *