Power BI Report Server can host Power BI reports on-premises, and you’ve learned about it in the previous post. There is another integration between SQL Server Reporting Services and Power BI service; this integration brings tiles from SSRS reports pinned to a Power BI dashboard with scheduled updates from SQL Server agent. The integration of SSRS reports into Power BI service, will create a link from Power BI dashboard to SSRS detailed reports.
In this post, you will learn what requirements to get SSRS report’s elements to be pinned to a Power BI dashboard are, and you will learn what the process of that in details is. Integrating SSRS reports into Power BI dashboards will create one single portal to access reporting solution. Users will be able to open Power BI dashboard and navigate from there to the detailed paginated SSRS report. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Set Up Requirements
For SSRS Integration to work with Power BI, you will need;
- SQL Server 2016 or higher
- SQL Server Agent should be up and running
- Only charts, gauges, and maps can be pinned to Power BI Reports
- Power BI Integration should be enabled
- Stored Credentials should be used for the data sources of SSRS reports
Let’s look at these requirements through an example.
Prerequisites for this Example
To run this example, you will need to have SSRS installed with a sample SQL Server database of AdventureWorks. Unfortunately explaining the steps of installing SSRS or SQL Server database is big enough and outside of this topic to be involved in this post.
Enable Power BI Integration
The first action for the integration is to enable Power BI Integration in the Reporting Services Configuration Manager. Open Reporting Service Configuration Manager, and then go to Power BI Service tab at the left-hand side.
The Power BI Integration is only available in SQL Server Reporting Service 2016 or higher versions.
Click on Register with Power BI and log in to your Power BI account. After registering with your account, you will see a screen such as below;
Pin Report Elements into Power BI Dashboard
If you open an SSRS report, you will see the Power BI pin option at the top of the report in the reporting services portal.
You can click on the Power BI icon to start pinning elements into Power BI service. If you don’t have any charts in your report, you cannot pin any item on the dashboard. Only charts can be pinned.
If you try this item for the first time, you will be asked to log in with your Power BI username and password.
After signing in, you will be asked to authorize SSRS to access Power BI account information.
After going through the login process, you will see all items that you can pin to the dashboard, and you can click on them.
When you click on an element to be pinned, you will be asked which workspace, and dashboard you want the element to be pinned, and what is the refresh frequency.
An important part of pinning items to Power BI from SSRS is that your SQL Server Agent Service should be up and running. Agent service is responsible for updating the tile in the Power BI dashboard.
After successfully pinning the item, you will see a message explaining the process was successful.
You can then open the dashboard in Power BI, and you will see the chart element from SSRS pinned there.
The tile will be updated from SQL Server agent. Every time you click on the chart, you will be redirected to the SSRS report. If you click on edit details of tile, you can see the link to the SSRS report.
SQL Server Agent
As you learned earlier in this post, SQL Server agent is responsible for keeping that tile up to date in the Power BI Dashboard. After pinning the SSRS report element into Power BI, you can check SQL Server Agent and see that there is an agent job created for this process.
The SQL Server agent also has a schedule which can be configured differently.
Summary
This post was a quick post about how charts from an SSRS reports can be pinned into a Power BI Dashboard. For using this functionality, some requirements need to be met. You have to use SQL Server 2016 or higher version. Your dataset of the report should be using saved credentials. SSRS report should have charts, gauges, or maps, because only charts, gauges, and maps can be pinned to the dashboard. Power BI Integration in the reporting services configuration manager should be enabled. And finally; SQL Server agent should be up and running because Agent is responsible for keeping that tile up to date.
Using the integration of SSRS and Power BI; you can have tiles in Power BI dashboard which points to SSRS reports for the detailed paginated report. Power BI users will use normal tiles for interactive reports, and they can use SSRS tiles when they want to see the more detailed paginated report in SSRS. The integration of SSRS and Power BI creates a single portal to access all reporting items; which would be from Power BI dashboard.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
This is awesome.
Can we include a .RDL report ( not only a visual) in our PBIX?
My scenario is we have created reports using the SSRS (.RDL) and want to use them with the Power BI reports (parameters and filters).
is it possible?
since RDL reports are accessible using URL, and you can even pass filters using URL query string parameters, you can create those links in Power BI, and by clicking on those it will navigate to RDL reports
Cheers
Reza