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 database 2016 version as the basis for our SSRS report development. 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.</p
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). </p
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.
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. </p
If you are not signed in, then you can use the sign in button to sign into your Power BI account. </p
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.</p
After clicking the Pin to Power BI button, you must first select the object to be pinned.</p
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.
Finally, the object will then be pinned to the bottom of the selected dashboard.
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.
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.</p
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
- Check out these related resources:
