Without a doubt SQL Server Reporting Services (SSRS) is one of the most powerful reporting tools for several years. There are tons of features that you can use to make a report that suits your customers’ needs. Despite programmability and extensibility are key strengths of Reporting Services platform when it comes to creating dashboards, SSRS has absolutely nothing to offer as SSRS is a report authoring tool. So it never supposed to offer dashboards. In old days we could create web parts in SharePoint or we could install Performance Point and include SSRS reports in Performance Point dashboards. But, setting up and implementing dashboards in SharePoint/Performance Point was always a painful job. Happily with the new version of SQL Server 2016 we are able to pin visuals from existing on-prem SSRS reports to a Power BI dashboard. In this article I explain how SSRS 2016 and Power BI integration works.

Requirements

• First of all you requite to install SQL Server 2016. Check this out to download SQL Server Developer Edition for Free.
• A Power BI account (it doesn’t need to be Pro account)
• You need to register SSRS with Power BI
• SQL Server Agent should be up and running

When you meet the above requirements you can pin visuals from existing SSRS reports to Power BI or you can create brand new reports and pin the visuals to Power BI.

Note: You can only pin report visuals to Power BI that means you won’t be able to pin tables and matrix to Power BI.

Note: If you don’t want to install the developer edition of SQL Server 2016 OR for any reason you cannot use the developer edition, don’t worry, the functionality I’m going to explain is available in other editions of SQL Server 2016. Indeed, the only editions that doesn’t support SSRS integration with Power BI are “Express Edition” and ” Express with Tools” editions. Check this out for more information.

Register SSRS with Power BI

After installing SQL Server you need to configure Reporting Services. As configuring Reporting Services is out of scope I leave it to you.

Note: At the time of writing this article I was using SQL Server 2016 CTP3. The same principles apply to SQL Server 2016.

• Open “Reporting Services Configuration Manager”
• Enter the “Server Name” and “Reporting Services Instance” then click “Connect”

• Click “Register to Power BI” from “Power BI Integration” tab

• Enter your Power BI account (email address)
• Click “Work or School account”

Note: If you click “Personal account” you won’t be able to register  Power BI. You’ll get the following error message:

“Registering Power BI ClientApp Failed to register Power BI client application. You may not have permissions to register an app with Azure Active Directory.”

• Enter your password
• You successfully registered your Reporting Services with Power BI

• You can easily unregister the by clicking on “Unregister with Power BI” button
• Click “Exit” to close Reporting Services Configuration Manager

Pin SSRS Visuals to Power BI

The aim of this post is not explaining how to create a SSRS report so I leave this to you. But, for those who just started working with SSRS, you can download Adventure Works databases from here. Then all you need is to change the data sources to point to your SQL Server instance then deploy the reports to your Report Server. In this post I used “Sales by region” report.

• Open Report Manager in Internet Explorer
• Click on “Sales by region” report to open it
• You should see a report like this
  
• Click “Pin to Power BI Dashboard” button
• Click “Sign in”
• Enter Power BI credentials if needed

Note: Remember to click “Wok or school account” as your account type otherwise you’ll get redirected to the “Sign in” page.

• Authorise Power BI by clicking on “Accept” button. If you want to see what permission you’re giving to your report server just hover over the question marks in front of each permission
• Click “Pin to Power BI Dashboard” button again
• Click a report item to pin to Power BI
• Select a dashboard from the list
• Select frequency of updates then click “Pin”

So far you successfully pinned a SSRS visual to your Power BI dashboard. Now login to your Power BI account and check the dashboard for the SSRS visual you pinned.

• You should see a new like this tile added to the dashboard:

• Clicking on the tile should open the SSRS report in a new tab
• Go back to the Power BI dashboard. To change the tile details click on the ellipsis button on top right of the tile

• You can either click on the URL to open the SSRS report, delete or edit the tile or pin it to another dashboard by clicking on the desired button

• Click Edit tile details
• Modify the desired details and click Apply

• All done!

Note: Remember to run SQL Server Agent if it is not up and running otherwise you get the following error message and won’t be able to pin the visuals to Power BI dashboard.

“We can’t pin to Power BI right now because the service that schedules dashboard tile refreshes (SQL Server Agent) isn’t running on the report server. (rsSchedulerNotResponding)”

Revoke App Permissions

As  I mentioned before you can unregister with Power BI by clicking “Unregister with Power BI” from “Reporting Services Configuration Manager”.

An alternative is to revoke app permission from Power BI Admin settings which is available if have admin permissions.

• Login to your Power BI Service
• Click Power BI menu then click “Admin”

• If you have Office 365 account you’ll see something like this in the menu

• Click “View all my apps”
• Click “Settings” menu from top right of the page then click “Office 365 settings”

• Click “App Permissions”
• You can now “Revoke” the “Microsoft Power BI” app permission

Note: Keep in mind  when  you revoke Power BI app permissions then the Power BI user(s) will not be able to pin SSRS visuals to Power BI any more and they’ll receive the following error message:

“The report execution gwr25245qqb4pzqsplxvuv55 has expired or cannot be found. (rsExecutionNotFound)”

To resolve this just open “Reporting Services Configuration Manager”  then click “Power BI Integration” and click “Update Registration” button.

Registering with a New Power BI Account

Registering the same Reporting Services with more than one account is not possible so if you want to register with a new Power BI account then you need to “Unregister” the current registration then register with the new account. To achieve this:

• Click “Unregister with Power BI” button

• Stop the service

• Exit the Configuration Manager then reopen it
• Start the service

• Go to “Power BI Integration” and register with the new Power BI account

Note: The reason for stopping and starting the service is that if you click “Unregister with Power BI” and click “Register with Power BI” without restarting the service, closing and reopening the Configuration Manager, then it SSRS registers with the old Power BI account without asking for new account.

• Open Report Manager from your browser
• Click “My Settings” from top right of the page

• Click “Sign out” button

• Click “Sign in” and enter the new Power BI account credentials

• All done! Now you can pin the SSRS visuals to your new Power BI account