SQL Server Reporting Services Integration with Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *