Without a doubt SQL Server Reporting Services (SSRS) is one of the most powerful reporting tools for several years. There are tons of features that you can use to make a report that suits your customers’ needs. Despite programmability and extensibility are key strengths of Reporting Services platform when it comes to creating dashboards, SSRS has absolutely nothing to offer as SSRS is a report authoring tool. So it never supposed to offer dashboards. In old days we could create web parts in SharePoint or we could install Performance Point and include SSRS reports in Performance Point dashboards. But, setting up and implementing dashboards in SharePoint/Performance Point was always a painful job. Happily with the new version of SQL Server 2016 we are able to pin visuals from existing on-prem SSRS reports to a Power BI dashboard. In this article I explain how SSRS 2016 and Power BI integration works.
First of all you requite to install SQL Server 2016. Check this out to download SQL Server Developer Edition for Free.
A Power BI account (it doesn’t need to be Pro account)
You need to register SSRS with Power BI
SQL Server Agent should be up and running
When you meet the above requirements you can pin visuals from existing SSRS reports to Power BI or you can create brand new reports and pin the visuals to Power BI.
Note: You can only pin report visuals to Power BI that means you won’t be able to pin tables and matrix to Power BI.
Note: If you don’t want to install the developer edition of SQL Server 2016 OR for any reason you cannot use the developer edition, don’t worry, the functionality I’m going to explain is available in other editions of SQL Server 2016. Indeed, the only editions that doesn’t support SSRS integration with Power BI are “Express Edition” and ” Express with Tools” editions. Check this out for more information.
Register SSRS with Power BI
After installing SQL Server you need to configure Reporting Services. As configuring Reporting Services is out of scope I leave it to you.
Note: At the time of writing this article I was using SQL Server 2016 CTP3. The same principles apply to SQL Server 2016.
Open “Reporting Services Configuration Manager”
Enter the “Server Name” and “Reporting Services Instance” then click “Connect”