The Road Ahead for SQL Server Reporting Services and Power BI Report Server

Power BI has garnered a lot of attention in the past few years as the “place to be” for Microsoft business intelligence. Power BI is of course Microsoft’s cloud platform for dashboarding and reporting. On premises, Microsoft’s reporting platform has traditionally been SQL Server Reporting Services (SSRS) and is now joined by Power BI Reporting Server (PBIRS). While there are differences between the two in the way that they are licensed and distributed, the main technical difference is that PBIRS includes SSRS, plus the ability to render both Power BI reports (pbix) and Excel based reports through Excel Online.

PBIRS is therefore able to render all four report types as defined by Microsoft:

Paginated

RDL (classic SSRS style reports)

Interactive

PBIX (Power BI Desktop)

Mobile

RDLX or PBIX (Datazen and Power BI Desktop)

Analytical

XLSX (Excel)

Given that PBIRS is the on-premises reporting platform, and Power BI is the cloud platform, they should theoretically be able to perform the same task. However, this isn’t the case. Currently, there is no way to render paginated (or as I like to refer to them, operational) reports in the cloud. PBIRS can render all report types, but today, the Power BI Service cannot.

While it has been hinted at before, Riccardo explicitly stated that the ability to render paginated reports in the cloud was not only on the roadmap, but it was actively being worked on. This will complete the picture for BI in both the cloud and on premises. This is important because I the ideal world, the choice of on-prem, cloud, or hybrid should be related to the data in question, not the available features. No more standing up an Azure virtual machine to be able to get printable, paginated structured reports.

Riccardo sees this service rolling out in three distinct phases. Initially for the preview, RDL reports will be able to connect to cloud data sources like Azure SQL Database, Azure Data Warehouse, Azure Analysis Services, and Power BI data models. The next stage will leverage the On Premises Data Gateway in order to connect to on-premises data like SQL Server, Oracle, Teradata and more. The next phase will begin to leverage Power Query to connect to the world of data sources that Power Query Offers.

With paginated reports adopting Power Query, and Excel having moved to Power Query as a default, it is hard to argue with making an investment in learning Power Query. It all seems to be coming together.

One of the top requested features for SSRS on-premises for year has been the ability to authenticate to it with claims-based authentication. This will be the native authentication mechanism for paginated reports in the service. When this is combined with the fact that these reports will ultimately leverage Power Query, and the vast number of data connection options that it brings, it’s not hard to wonder when these features will be coming down to the on-premises product. When asked about this, Riccardo confirmed that this is certainly the vision for the product.

The future looks bright for both cloud based, and on-premises reporting.

Thanks John for sharing your expertise. We have SQL 2016 Enterprise with SA which entitles us to use PBIRS but I still can’t figure out how to license the authors vs viewers of the published reports. A blog post on the pricing would be great.?