SSRS 2016 and Power BI

SSRS 2016 and Power BI

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

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”

SSRS 2016

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

SSRS 2016 and Power BI 01

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

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.

SSRS 2016 and Power BI 02

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

SSRS 2016 and Power BI 03

  • 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 newbies who are just started working with SSRS, you can download Adventure Works 2016 CTP3 database from here and Adventure Works 2012 SSRS samples 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 itSSRS 2016 Report Manager
  • You should see a report like this
    SSRS 2016 and Power BI 04
  • Click “Pin to Power BI Dashboard” buttonSSRS 2016 and Power BI 05
  • Click “Sign in”SSRS 2016 and Power BI 06
  • 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.

Power BI Account Type 01

  • 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 permissionAuthorize Power BI App
  • Click “Pin to Power BI Dashboard” button again
  • Click a report item to pin to Power BISSRS 2016 and Power BI 07
  • Select a dashboard from the list
  • Select frequency of updates then click “Pin”

SSRS 2016 and Power BI 08

SSRS 2016 and Power BI 09

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:

SSRS 2016 and Power BI 10

  • 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

SSRS 2016 and Power BI 11

  • 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

Edit Power BI Tile

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

Edit Power BI Tile 01

  • All done!

SSRS 2016 and Power BI 12

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

Error 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”.

SSRS 2016 and Power BI 13

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”

Power BI Admin Settings

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

Power BI Admin Settings 01

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

Power BI Admin Settings 02

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

Power BI App Permissions

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

Error rsExecutionNotFound

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

SSRS 2016 and Power BI 14

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

SSRS 2016 and Power BI 15

  • Stop the service

SSRS 2016 Configuration Manager

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

SSRS 2016 Configuration Manager 01

  • 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

SSRS 2016 and Power BI 16

  • Click “Sign out” button

SSRS 2016 and Power BI 17

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

SSRS 2016 and Power BI 18

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

Leave a Reply