How To Pin SSRS Report To Power BI Service Cloud

Introduction

 
SSRS and Power BI are two different Business Intelligence tools which provide pictorial or graphical visualization of data in the form of dashboards and reports. It provides clear insights into business data to analyze and makes decisions accordingly for  higher management and end users. Microsoft has provided the feature thats SSRS reports can be pinned to Power BI Service cloud. This is applicable with SQL Server 2016 Reporting Services and later versions. In this write up, I will explain a combination of SSRS and Power BI Services and integration of SSRS reports to Power BI online.
 
This article describes the necessary steps for how to configure and publish SSRS reports or dashboards containing visualization to the Power BI cloud services.
 
Firstly, we need to configure report Server Configuration Manager to publish SSRS reports to Power BI.
 
I have assumed that we have already configured the SSRS Environment and SSRS reports and dashboards are in use in this environment. If you do not have SSRS Setup in your machine and no idea how to configure, you can go through this article for SSRS Setup in Report Server Configuration Manager: link
 
Prerequisites
  1. SQL Server
  2. SSRS (SQL Server Reporting Services)
  3. SSRS Running Environment
Requirement to Pin
  • Report Server should have configuration for Power BI integration. (setup from Report Server Configuration Manager)
  • Should configure the browser to allow pop-ups from report server site.
  • Pin option is only available from web portal. We cannot pin from Report Builder.
  • Must configure the reports and dashboard for stored credentials. If you have not configured for stored credential, then you will get error like below.
How To Pin SSRS Report To Power BI Service Cloud
 

Setup in Report Server Configuration Manager

 
Type Report server configuration manager in the windows search box and open it. Then follow the given steps below.
 
When the Report Server Configuration Manager opens, following screen with Server Name and Server Instances including your setup environment details will be appeared then click on connect.
 
How To Pin SSRS Report To Power BI Service Cloud
 
As I have considered all the other setup are already done and SSRS reports are running. So, let us Go to Power BI Service (Cloud) section as depicted below.
 
How To Pin SSRS Report To Power BI Service Cloud
 
Click on Register with Power BI as shown below.
 
How To Pin SSRS Report To Power BI Service Cloud
 
Now, you must provide your Microsoft account and password. Ensure that the account you have entered in this step have access to Power BI.
 
How To Pin SSRS Report To Power BI Service Cloud
 
After providing the password you are successful to register Report Server with Power BI.
 
Besides this, you can see changes in the above screen, for example, before setup there was no Unregister option but now after register, we can get Unregistered option as illustrated below. You can get options to Unregister with Power BI and Update Registration Option if you want to update registration information with new account.
 
How To Pin SSRS Report To Power BI Service Cloud
 
Unregister with Power BI
 
If we unregister the user then the user will not be able to pin SSRS visual in Power BI. Ways to unregister user to Pin SSRS visuals to Power BI,
  • It can be done from Reporting Server Configuration Manager as depicted above.
  • Additionally, revoking to app can be done from Power BI Service if you have Power BI admin privilege.
Update Registration
 
It is for updating the power bi user account with new details.
 

Pin Paginated reports from Report Server

 
After setup in Report Server Configuration manager, Open Report Server using Web Portal URL. You can get it from Web Portal URL Section as shown below.
 
How To Pin SSRS Report To Power BI Service Cloud
 
Open and run the reports which you want to pin to Power BI Service Cloud from Report Server in browser. When the report opens you will get option to pin it to the Power BI Service as illustrated below. Click on Power BI Pin option.
 
How To Pin SSRS Report To Power BI Service Cloud
 
Note
To Pin SSRS Visuals in Power BI SQL Server Agent must be running. Make sure your SQL Server is running.
 
Limitation
  • We can only pin SSRS reports containing visualization only. It means that Table and Matrix report cannot be pinned to Power BI. Only Charts, Gauge panels, Maps, Images, Item which are inside the body area can be pinned. Cannot pin items designed in header and page footer.
  • Registering with multiple user account is not possible. We must unregister the current user and then should add new user.
  • Can be applied with SQL Server 2016 Reporting Services and later versions.
  • Cab be pinned from Report Server not available from Report Builder.

Conclusion

 
Hence, this article has described how to pin SSRS Reports and dashboards to Power BI Service cloud with necessary steps. After setup we will be able to see all the SSRS visual reports and dashboards in Power BI cloud service. I hope, this will help you to implement it successfully, fulfill your client’s requirement in case of need as well as get the flavor of two Business Intelligence services in the cloud together.
 
References
  • https://docs.microsoft.com/en-us/sql/reporting-services/pin-reporting-services-items-to-power-bi-dashboards?view=sql-server-ver15
  • https://docs.microsoft.com/en-us/sql/reporting-services/report-data/store-credentials-in-a-reporting-services-data-source?redirectedfrom=MSDN&view=sql-server-ver15