Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Pinning a SQL Server Reporting Services Report to Power BI


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

How do I pin a SQL Server Reporting Services (SSRS) report to Power BI Dashboards?

Solution

The improvements to SQL Server Reporting Services (SSRS) and Power BI just keep coming. In the latest SQL Server 2016 CTP 3.0 a new feature added to SSRS allows the direct pinning of specific objects to your online Power BI dashboards.

We will use the Adventure Works databases as the basis for our SSRS report development. The 2016 version of the regular databases are available on Codeplex: https://www.microsoft.com/en-us/download/details.aspx?id=49502. Once you download and install the SQL Server databases, we will subsequently use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Pinning to Power BI

The process of pinning an object requires some initial setup before being able to pin an object for the first time. The first step centers around establishing a connection between SSRS and Power BI. To start the process, you must first connect to the Reporting Services Configuration Manager as shown below.

SSRS Config

Next you need to move to the Power BI integration tab. On this tab you need to register the connection to your previously created Power BI site. Additionally you must be an Azure AD tenant, a Reporting Services system administrator and a system administrator for the ReportServer catalog database in order to register the connection.

To complete the integration you will need to use the email address you used initially to register with on the Power BI site. Note: the registration process is currently restricted to company and organizations email addresses, so Gmail, Yahoo, and similar accounts are not able to register. The registration, as shown next, will validate your identity and then assure that you have proper access via your email account (second screen print below).

reg account

sign in

Once the registration is complete, you will see confirmation of success at the bottom of the screen. Furthermore, the registration ID and related account details will display in the middle of screen, similar to the below illustration. This window also allows you to unregister the connection or re-register the link.

reg complete

Now that the connection between your SQL Server 2016 Reporting Services server and your Power BI account is defined, we can now move on to your main report server screen. As displayed next, from the home page we first must click My Settings in the upper right corner of the screen. On the My Settings screen we can verify that we are logged in and connected to the Power BI site.

my setting

success sign in

If you are not signed in, then you can use the sign in button to sign into your Power BI account.

sign ing

Once the connection is validated on the My Settings screen, we can then navigate to the report which contains the object(s) we want to pin to the Power BI dashboard. Remember we can only pin gauges, charts, pictures, and maps. On the report viewer screen, a new Pin to Power BI button should be present and available to click on.

power bi button

After clicking the Pin to Power BI button, you must first select the object to be pinned.

select object

Now you are connected to your Power BI site and requested to select the dashboard to pin the object to and how often to refresh the dataset for the object.

select refresh and location

Finally, the object will then be pinned to the bottom of the selected dashboard.

Pinned Object

Limitations for SSRS Pinning to Power BI

A few caveats need to be mentioned about this newly pinned chart, graph or map. First, credentials must be stored in the report in order for the object to refresh. The pinning process actually creates a SSRS subscription which requires stored credentials in order to execute. Furthermore if you change the item name, the object will stop updating based on the subscription. Also if the pinned object is deleted, the related subscription is not deleted and must be removed manually. Lastly, if you try to pin an object and the SQL Server Agent is not running, the following error results. The SQL Server Agent where the SSRS database resides must be running in order to pin objects.


sql agent not running

Finally, as you are viewing the pinned object, you can actually click on the object tile which will link you back to the original report.

link to original report

Conclusion

We covered in this tip the methods of taking an object on a SSRS report and pinning it onto a Power BI Dashboard online. The process first requires your SSRS server to be connected or linked with your online Power BI account. Once connected, charts, graphs, maps, and images can then be pinned using the Pin Power BI button found on the report viewer screen. The pinned object gets added to the bottom of the selected dashboard and also provides a link back to the original report.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools