The Future of Report Integration with SharePoint

Yesterday, Microsoft made official what many, including myself had been suspecting ever since the release of SQL Server 2016 – that SQL Server Reporting Services Integrated mode would not exist in the future. With the announcement, we now know the timeline of when that will happen. SSRS Integrated Mode will not be included with the next version of SQL Server. Instead, SSRS Native mode will be more tightly integrated with SharePoint for those organizations that use both products. As someone that has always approached Business Intelligence from the SharePoint point of view, I see this is a good thing.

This change is another step in the process of de-cluttering and uncomplicating SharePoint. This process started arguably with the move away from fully trusted code running on SharePoint, to the newer app, add-in and now SPFx development models that run with SharePoint. When Excel Services was removed from SharePoint in SharePoint 2016, with its capabilities moved to Office Online Server this process became obvious. A decreased dependency on SharePoint allows for simpler, more streamlined architectures, better options for upgrade management, and better, more targeted performance management.

SSRS SharePoint Integrated mode has been with us in various forms since it was first introduced in SQL Server 2005 SP2. The original goal was to simplify the integration of the two products, and to take advantage of SharePoint’s storage and authorization capabilities. The integration has always worked well, but the very fact that these two products were delivered by two different product teams on different media, often on different release schedules has typically led to a great deal of confusion. The SharePoint prerequisite for Integrated Mode leads to far too many SQL servers having SharePoint installed on them.

Managing SSRS in SharePoint Integrated mode requires a combined skill set to some degree as well, with knowledge of both SSRS and SharePoint. SharePoint administrators tend to be intimidated by SSRS, and SharePoint simply mystifies SQL DBAs.

The fact that the two different modes did not always maintain feature parity is another problem. PowerView and several other features are only available through SharePoint integrated mode. This results in entire SharePoint farms being created for the sole purpose of providing reporting features. Since those performing these installations are typically not familiar with SharePoint best practices, these farms tend to be unreliable. The latest release of SSRS 2016 contains a massive number of new features, but many of them in Native mode only, leaving the SharePoint integrated folks with a decision whether to favour features or integration.

A strategy that reduces the dependency of one platform on the other is therefore to everyone’s advantage.

The two operating modes also represent two different code streams for Microsoft to maintain. Given the finite set of resources that is any development team, resources must be spent on maintaining both of these streams that could otherwise be applied to features. A single code stream is simply more efficient.

Preventing the wholesale move to Native mode are several SharePoint integration features that have been employed over the years that are only available in SharePoint Integrated mode.

There has been a SharePoint Report viewer web part for SSRS Native mode since SharePoint 2003. The trouble is that while it does work, it is deprecated, and hasn’t been updated since SQL Server 2008 R2. It also doesn’t allow for parameter binding, or interface control. For all intents and purposes, it is an iFrame embed of a report. The web part that is available through integrated mode allows for parameter interactivity, and significant control of the look and feel. It has been widely deployed. Integrated mode also allows for the reporting of SharePoint list data, and the ability to publish reports to a SharePoint library on a schedule. These features are well utilized in the market today.

Power View reports (RDLX) built on top of SSAS tabular models, or Excel Power Pivot models also require Integrated mode. Compounding this is the fact that Power View requires Silverlight, which does not work in either the Chrome nor the Microsoft Edge browsers.

These integration features will need to be added to Native mode before it will be possible to fully abandon Integrated mode. The good news is that the announcement commits to doing just that. Report embed, Report viewer web part, and SharePoint library destination capabilities will all be added. For Power View reports, a conversion tool will be provided to convert from RDLX into Power BI Desktop (PBIX). A technical preview is already availably that demonstrated PBIX rendering in SSRS.

This announcement signals the end of SSRS SharePoint Integrated mode, but it does not spell the end of SSRS SharePoint integration. The single mode architecture should be more approachable, simpler, and more efficient. It’s a win all the way around.