Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Pinning a SQL Server Reporting Services Report to Power BI


By:   |   Read Comments (4)   |   Related Tips: More > Power BI


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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:


signup button

next tip button



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 


SQL tips:

*Enter Code refresh code     



Friday, March 17, 2017 - 7:01:43 AM - Scott Murray Back To Top

You are correct with the pinning of the objects.

 

Sorry I am not familiar with pinning from Excel.


Friday, March 17, 2017 - 1:29:23 AM - Charles Back To Top

Hi Scott,

Thanks for your quick response.

Per my understanding for the paginated reports it also works to pin a tile to dashboard even the objects from the different page of the report, is that correct?

Also can you please explain weather it can pin from Excel range or a PBI desktop file? how it works?

I am a newer of the PBI, thanks for your patience and great support!

 

-Thanks,

Charles 


Thursday, March 16, 2017 - 7:04:30 AM - Scott Murray Back To Top

 Charles,

The pinning process only pins the selected object, not the whole report.

I have not tested a mobile report.

Scott


Thursday, March 16, 2017 - 3:40:54 AM - Charles Lee Back To Top

This tips quite useful for the newer to pin a SSRS report to online dashboard of power bi.

but there is not mentioned that if:

- it supported to pin from an SSRS 2016 mobile report

- or if it is a paginated report can be pined to a dashboard tile at one time or not.

 

 

-Thanks and Cheers,

Charles


Learn more about SQL Server tools