Tag: Native Mode

The Future of Report Integration with SharePoint

Published November 18, 2016

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.

3 Comments

SSRS 2016 – Integrated or Native Mode – Which one should you use?

Published March 28, 2016

The answer to the SQL Server Reporting Services Integrated vs. Native mode question used to be very simple. Once upon a time, if you had a SharePoint environment, you would want to deploy SharePoint Integrated mode, and if you didn’t, you would pick Native. Integrated mode would leverage your pre-existing security model in SharePoint, it would allow reports to look like documents in SharePoint making them more user friendly, and you would be able to use the advanced features of the SSRS web parts in SharePoint. Non-SharePoint users were able to do what they needed around security and report storage with Native mode. Everyone was happy.

SQL Server 2012 changed that a little bit. Power View reports were first introduced in SQL Server 2012 as a part of SSRS. These reports leveraged the tabular (PowerPivot) data models available in SSAS 2012 and provided some very user friendly tools for self service analytical reporting. However, one of the catches introduced was that Power View reports were only available in SharePoint Integrated mode. Suddenly, the choice of mode became feature based. This suited those with SharePoint environments just fine, but those without SharePoint would now need to stand up a SharePoint farm just to gain access to Power View. This is a daunting prospect, especially for those unfamiliar with SharePoint. This requirement, coupled with the minimal investment into new features for core SSRS in SQL Server 2012 had the effect of making the Native mode users feel abandoned. After all, we know what typically happens when Microsoft stops investing in a product. The balance was heavily tilted in the favour of Integrated mode.

The new normal

This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.

Feature	Integrated Mode	Native Mode
HTML 5 based rendering engine	X	X
New chart types	X	X
PDF based printing (no ActiveX)	X	X
PowerPoint rendering and export	X	X
New UI for Report Builder	X	X
Customizable parameters pane		X
New web portal		X
Mobile reports		X
KPIs		X
Pint to Power BI		X
Render Power BI reports*		X

* Coming soon

You can see above that the balance has shifted very heavily in favour of Native mode. The folks using Native mode are very happy about this move – they are no longer having SharePoint forced on them in order to access new features. However, now it’s the SharePoint folks turn to feel abandoned, but they really don’t need to. SSRS Integrated mode is still getting a significant enhancement in 2016, it’s just not as significant as the improvement to Native Mode. Integrated mode is also still required for rendering Power View reports. Last fall’s Reporting Roadmap reconfirmed Microsoft’s commitment to SharePoint as a platform -“We will continue to support embedding of BI content into SharePoint”. SharePoint has a bright future as an report destination. The only question is how that will be brought about.

It may well be that the features had to go into Native mode first in order to meet the shipping schedules, and that they’ll be brought along eventually. I suspect however that this is not the case. I think that this is either the last, or penultimate version of SSRS to contain Integrated mode. If the same level of embedding into SharePoint could be provided by Native mode, and the user experience improved (as it has been in the new report portal) then there is very little real need for Integrated mode at all.

Building shared service applications in SharePoint is a non-trivial task, and those resources could likely be better spent on features for SSRS. A new embedding model could support both SharePoint on-premises (as it currently does) and SharePoint Online (as it currently doesn’t). The same mechanism could be used to embed Power BI reports. We’ve already seen glimpses of this hybrid interoperability in the SSRS and Excel pin visual to Power BI capability. I suspect that over time we’ll see SSRS Native mode and its reporting portal also assume the role currently played by PerformancePoint Services as well. For all of these reasons, I think that SSRS Native mode is the only future for SSRS.

But that’s the future. What about the present?

When I first learned of these developments, I suspected that I would be recommending Native mode for anyone moving forward. However, as I discuss in an earlier article, the SSRS web parts for Native mode are deprecated, and missing key pieces of functionality, parameters being first among them. They are really little more than iframes, and they certainly can’t replace the Integrated mode web parts. If you’re going to use reporting in SharePoint in any meaningful way, or you are looking to upgrade an existing SharePoint farm with SSRS integration to 2016, you’re going to need Integrated mode. That means no mobile reports, report manager, or Power BI integration.

So why choose?

There is nothing stopping you (apart from possibly licensing) from running both modes. Using Integrated mode, you can take advantage of the new rendering engine, etc, and a separate Native mode server can be used for Report Manager, mobile reports, and Power BI integration. Over time, more reports can be brought over to Native mode and the embedding story improves. Once they are all brought over in “the future”, the Integrated mode service can be simply removed. This provides for a smooth, gradual migration. In fact, you can set up an SSRS 2016 Native mode server along side an existing SharePoint 2013 farm with SSRS 2014 or earlier Integrated mode to get started. Your SharePoint reports won’t have any of the new features, but your Native mode certainly will.

We are clearly in a transitional stage when it comes to on-premises reporting technologies from Microsoft. There are significant, bold steps forward, but there is also a legacy of technology to support. The current lineup of technologies allows for both approaches for organizations to embrace at their own pace.

14 Comments

Integrating SharePoint 2016 with SSRS Native Mode

Published March 11, 2016

Why on earth would I want to write an article on this topic? Surely, if I am using SharePoint and SQL Server Reporting Services, I should be running in in SharePoint Integrated mode, right? That’s certainly the message that I have been delivering for quite some time. However, the game has changed significantly with SSRS 2016. Last October, Microsoft outlined their reporting roadmap, and that roadmap included a significant investment in SSRS. The roadmap was very clear as to Microsoft’s intentions.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services is the solution, not SharePoint with Reporting Services, or PerformancePoint with Reporting Services, just Reporting Services. For several years now, the path to any new features in SSRS led through SharePoint Integrated mode. Features like Power View reports were only made available in Integrated mode as an example. While that was great for those invested in SharePoint, it presented an adoption issue for those that were not. This issue has prompted Microsoft to remove the SharePoint dependency, while still providing solid integration. In short, the goal for SSRS is to run with SharePoint, not on it.

In my opinion, this is all to the good. By making SharePoint integration pluggable, the product team can focus on one codebase instead of two, and spent more energy on features. This does however have some negative impact on administrators that will need to again manage two security models, but in the ideal world, it should be transparent to end users.

The immediate impact of this refocusing is that Native mode now receives new feature priority. If we look at the current state of SSRS 2016 (RC0 at the time of this writing), only a few of the major new features will be available in SharePoint integrated mode.

SSRS New Features

Native

SharePoint Integrated

HTML 5 Based Rendering Engine

Customizable Parameters Pane

New UI for Report Builder

New Web Portal

Mobile Reports

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

KPIs

Pin to Power BI Dashboard

Render Power BI Desktop files

HTML 5 Based Rendering Engine

New UI for Report Builder

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

This new disparity is likely to leave some in the SharePoint world feeling left behind. The reality is that although this may seem like the case in the short term, Microsoft stated that “We will continue to support embedding of BI content into SharePoint”. For the record, that statement is open ended enough to include both SSRS and Power BI. The improvements to integrated mode in SharePoint 2016 are a testament to this support. It would have been just as easy to leave Integration mode in its previous state (like PerformancePoint). My view is that ultimately Native mode reports will work with SharePoint in much the same manner that current Integrated mode ones do. In fact, it’s possible to do some of this today – to embed Native mode SSRS Reports into SharePoint. That’s what the remainder of this article describes.

The ability to embed Native mode SSRS reports has actually been available since SharePoint 2003. It fell by the wayside after Integrated mode was introduced in SQL Server 2008 R2, but it has continued to be there. What is needed is a Native mode SSRS Server, and the Native mode SharePoint web parts.

Installing and Configuring SSRS Native Mode

Native mode SSRS is installed from the SQL Server media. It should be installed on a NON SharePoint server. Run the SQL Server installer, and eventually you will be taken to the feature selection screen.

Native mode SSRS installs as a SQL Server instance, and it is the only option necessary to install. The SharePoint add-in is only used for Integrated mode.

Once installed, it is necessary to run the Reporting Services configuration tool. The first step in configuration is to set up the web service URL.

Once the desired options are set, click Apply and the SSRS web service will be set up. Next, click on the Database node to configure the SSRS database. If the SQL Server database is installed on the same machine, you can use it, but you can use any SQL Server at your disposal. The only restriction is that the database engine must be at least the same edition level as SSRS (ie Standard vs Enterprise).

To create a new SSRS database, click the “Change Database” button and provide the database parameters.

Two databases will actually be created, one of them a Temp database. I recommend using the word “Native” or some other identifier in the name, particularly when both Native and SharePoint Integrated mode servers may be used. Complete the database creation process, and move to the Report Manager URL node.

Click Apply to create the SSRS Report Manager. The initial URL will always be based on the machine name, but once complete, you can click on the Advanced tab to add additional URLs. This is how you can add a Fully Qualified Domain Name (FQDN) to your SSRS server, which is strongly recommended if you will be integrating SSRS with Power BI. Power BI users will need to connect directly to the SSRS server to view SSRS reports, and this requires an FQDN.

There are other steps to be performed at this point, including Power BI integration and exporting the Encryption keys, but this is all that is necessary for basic configuration. You should now be able to navigate to your Report Manager URL and create reports. The next step is therefore to integrate them with Sharepoint.

Integrating with SharePoint

Native mode ships with a pair of web parts that allow SSRS web parts to be embedded into a SharePoint page. The web parts are embedded in an installable .cab file that can be found in the folder “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Reporting Services\SharePoint” where C: is the installation drive, and “130” is the major installation version for SQL Server (130, or 13.0 corresponds to SQL Server 2016). The name of the file is RSWebParts.cab. Copy this file to a SharePoint server in the farm, and from there, it can be installed from either PowerShell, or the gool ol’ STSADM command. With PowerShell, the command is:

Install-SPWebPartPack -LiteralPath “D:\Software\RSWebParts.cab” -GlobalInstall

Where “D:\Software” is the folder that the file was copied to. The corresponding STSADM command is:

STSADM.EXE -o addwppack -filename “D:\Software\RSWebParts.cab” -globalinstall

Unfortunately, I and many others have found that the version of the .CAB file distributed with SQL Server 2012 and above are incompatible with SharePoint 2013 and 2016 – the web parts fail to deploy. The good news (and the bad) is that the web parts are unchanged from SQL Server 2008 R2, and that version of the .CAB file will work with modern SharePoint. Of course, not everyone has a SQL Server 2008 R2 server lying around, so if you happen to need the file, I include it here:

RSWebParts.cab from SQL Server 2008 R2

Using the Web Parts

Once deployed, the two web parts, Report Viewer and Report Browser will appear under the miscellaneous section when a web part is inserted into a page. Report browser allows the browsing of reports on a server, and Report Viewer renders them. By connecting the two, it is possible to provide a highly interactive navigation of the report server right in a SharePoint server. However, editing the Report Viewer web part reveals that it is lacking some very fundamental capabilities.

Native Mode Web Part	Integrated Mode Web Part

The Native mode web part is missing all of the view control features that are available to the Integrated mode part, which means that when it comes to Native mode reports, you get what you get. However, more concerning is the fact that it is also missing parameters. There is no way to configure parameters for, or pass parameter values to Native mode reports embedded on a page.

Add to this limitation the fact that these web parts are approximately 10 years old – they were designed for SharePoint 2007. They are able to render the new chart types, but not through the new HTML renderer. These limitations make it very difficult to recommend their use, except in a few very specific scenarios.

Recommendations

So what is a Report driven SharePoint administrator to do? All of the cool new features are showing up in Native mode, but except in certain circumstances, there no really good way to embed those reports in Sharepoint pages. It seems a difficult question, but the reality is that these choices are not necessarily mutually exclusive. SSRS Integrated mode is getting many of the modernization improvements and continues to be a totally viable platform moving forward. If you want or need to take advantage of the new SSRS features like mobile reports, parameter pane customization, or Power BI integration, you can stand up a separate SSRS Native mode server, and even integrate it with SharePoint using the older web parts.

Taking this dual approach means that you’ll be well positioned to gradually move assets from Integrated mode to Native mode as the embedding story and capabilities improve.

17 Comments

Migrate Reporting Services from Native Mode To SharePoint Integrated Mode

Published April 26, 2012

I have previously written about upgrading and moving Reporting Services to SSRS/2008 R2/SP2010, and also on upgrading to the new Service application in 2012. Both of these deal with moving prior versions of Reporting Services running in SharePoint mode to more recent versions, also running in integrated mode. What has been lacking from Microsoft until now was a mechanism to help move an organization from Reporting Services in Native mode to Reporting Services in integrated mode.

The solution to date has been to go back to the source projects in BIDS and redeploy them to the Integrated Mode server. This of course assumes that BIDS was used for report design (not Report Builder), and that the projects are available. You also lose all server side configurations (like subscriptions) with this approach.

On Friday, April 20 2012 Microsoft released Version 1.0 of the Reporting Services Migration Tool, which allows you to do just that. It’s a high level tool that brings all of the artifacts out of the Native mode instance, and at a later point in time, import them into the Integrated Mode instance. Ultimately, the stated aim of the tool is to allow a file system level backup of your Reporting Services Instances, be they Native or Integrated mode.

It can be run either by command line, or through a GUI. A snapshot of the GUI screen can be seen below.

The tool is definitely version 1, and has several limitations which I’ll outline below, but it does work. It does so by connecting to either the WMI provider, or the Reporting Services web services, then extracting all of the available content, and then building a PowerShell script which can be run to place the backed up content in a SharePoint document library that has been properly configured to support the Reporting Services content types.

Operation of the tool is relatively straightforward, and is adequately documented on the download page, so I won’t go through a step by step, but I do want to share a few observations.

Firstly, migration is from Native Mode to Integrated Mode only. The stated objective of this tool is to support both modes on either end of the migration path, but for now it’s a one way trip. For the moment, it does limit its ability to perform as a backup tool. However, if you examine your output folder, you’ll find all of your report files, connection files (etc), so if you’ve built your reports with BIDS, and lost the original source project, it’s a great way to get them out of the Reporting Services database.

I have also been unable to get the WMI provider to work at all. I’ve tested with both SSRS 2008R2 and SSRS 2012 Native mode sources, but the tool can’t seem to find the WMI instance. The tool still works in this configuration, but it will not back up passwords or history snapshots. I’ll update this post if/when a solution to this can be found.

UPDATE – Thanks to Tristan in this MSDN forum thread – The WMI provider is working. I have added the paragraph and image below.

The Instance Name field is mislabelled. It should be SERVERInstance for non default instances, or just SERVER for default instances. Unfortunately the nowhere in the help is the requirement for SERVERNAME mentioned. Essentially, you should treat this field the same as you would the Server field when connecting via Management Studio. The image above has been updated to show the correct value for Instance Name (In this case, although not necessary, I have included the name of the default instance).

As outlined on the download page, the tool does not back up Reporting Services security information, or role information – which makes sense when moving to a new security model. Also, linked reports aren’t supported in Integrated mode, so they’re not backed up at all.

For a complete list of constraints and instructions, visit the download page.

For it’s limitations, this tool is a very welcome addition to the toolkit. Migrating from Native Mode to SharePoint Integrated mode Reporting Services no longer needs to be painful.

2 Comments