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
- SQL Server
- SSRS (SQL Server Reporting Services)
- 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.
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.
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.
Click on Register with Power BI as shown below.
Now, you must provide your Microsoft account and password. Ensure that the account you have entered in this step have access to Power BI.
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.
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.
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.
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