SharePoint and the New SSRS/PBIRS Native Mode Report Viewer Web Part

When SQL Server 2016 was released, I wrote a post that covered how to integrate SharePoint 2016 with SQL Server Reporting Services (SSRS) in native mode. I wrote that post because although SSRS was still available in SharePoint Integrated Mode, many of the new features were only in Native mode. It also wasn’t difficult to surmise that Integrated mode had a limited lifespan at that point, a fact that was confirmed in November 2016.

As my integration article pointed out, one of the glaring problems integrating Native mode with SharePoint was that the Native Mode web part had significantly less capability than the one that was available with Integrated mode, not to mention the fact that it was deprecated. Parameter support was first and foremost among those missing features. Organizations that had incorporated SSRS reports into their SharePoint based dashboards couldn’t just move to Native Mode, and those dashboards relied heavily on the ability to pass parameter values to the SSRS reports through the web part. SSRS did make it easier to embed reports into pages without a web part through the rs:Embed URL directive, but there was no real way to make that dynamic.

The introduction of Power BI Report Server (PBIRS), and its ability to render Power BI reports also left the SharePoint dashboard folks without a solution. Although PBIRS is a superset of SSRS, it is only available in Native Mode, in fact, there’s no longer any need to mention modes. SharePoint dashboards that used SSRS report components were stuck at the 2016 version.

Until now.

Today, Microsoft unveiled the new, fully capable SSRS/PBIRS Report Viewer web part for SharePoint.

Integrated Mode Native Mode

“Meet the new boss, same as the old boss” – Pete Townshend

The web part allows paginated reports from either SSRS in Native mode, or Power BI Report Server to be embedded into a SharePoint page. Essentially, it brings all the capabilities of the integrated mode web part to the native mode web part. Organizations that have bet on SSRS running in Integrated mode can now seamlessly move their reports to SSRS Native mode, or to Power BI Report Server. Let’s have a quick look at some of the details.

Parameter and view support

All the options that we’ve come to expect from the integrated mode web part are here in the new web part. Toolbar options can be controlled at a fine level and web parts can be connected to pass in parameter values. In fact, as seen above, the only difference between the two modes now is the report location option.

C2WTS and Kerberos required… unless

With Integrated mode, Kerberos constrained delegation (KCD) was only required if you needed the user’s identity passed back to the data source. This was because Integrated mode and SharePoint itself shared the same access mechanism. Native mode and PBIRS have their own membership providers, and this new (server side) web part needs to connect to the report server itself to authenticate the user. This is an additional “hop” and therefore requires Kerberos constrained delegation. This only gets the user as far as the report server itself. To get the user’s identity further downstream, KCD will also need to be set up between the report server and data source(s).

SharePoint 2016 in most cases uses claims authentication, and SSRS/PBIRS does not. This also means that the Claims to Windows Token Service (C2WTS) must also be running in your SharePoint environment. This allows the user’s NTLM identity to be extracted and sent via KCD to the SSRS/PBIRS server.

There is one case where KCD is not required, and that is when SSRS is running on a machine in the SharePoint farm along with C2WTS. In a small environment, this may be suitable, but if scaling is a factor, you may need to crack open your Kerberos books.

Paginated reports only

Even though SSRS supports the new RSMobile report format, and PBIRS also now supports both Interactive (Power BI) and analytical (Excel) reports, this web part is only built to render paginated reports. Up until 2016, paginated was the only report type supported by SSRS, so this will not be a problem for those looking to move forward. However, for those people that are looking to embed these other formats in SharePoint on premises, there are other options.

Both Power BI and mobile reports can be embedded into a SharePoint page using the script editor web part alongside the rs:Embed=true URL directive. Parameters can be passed to mobile reports using this technique, and text filters can be passed to interactive reports as well. Excel reports can be embedded with the Excel Services web part, the way that they always have which itself allows for passing slicer values. However, for this to work, the Excel files need to be stored in SharePoint, not PBIRS.

On-premises only

The new web part deploys to the SharePoint farm as a WSP solution file, which means that this is an on-premises solution only. Given that SSRS/PBIRS are also on-premises products, this shouldn’t pose much of a problem. It IS possible to ebbed on prem reports into SharePoint Online pages with the script editor web part for classic pages, and the embed web part for modern pages. A complete summary of report/SharePoint edition embed options can be seen below.

Report type

SharePoint Online modern page

SharePoint Online classic page

SharePoint on-premises

PBIX on Power BI Service

Power BI WP

Anonymous*

Anonymous*

PBIX on PBIRS

Modern embed content WP

Script editor WP

Script editor WP

RDL on PBIRS or SSRS Native

Modern embed content WP

Script editor WP

Report Viewer WP NEW!

RDL on SSRS Integrated mode

Modern embed content WP

Script editor WP**

SSRS WP

XLSX (stored in SharePoint)

N/A

Excel Services WP

Excel Services WP

RSMobile on SSRS/PBIRS (Native)

Modern embed content WP

Script editor WP**

Script editor WP

*    NOT recommended

**     Requires adding sever to HTML Field Security options in site collection settings

In summary, the new native mode web part will be very welcome for those organizations that have been feeling stranded by the deprecation of SharePoint Integrated mode. The very fact that it is now available also underscores that SharePoint still plays an important role in the Microsoft Business Intelligence ecosystem. It may no longer be a prerequisite, but it still adds value, and those are both very good things.

5 thoughts on “SharePoint and the New SSRS/PBIRS Native Mode Report Viewer Web Part

  1. Pingback: Apisode 17 – Live from Ignite with Chris Finlan – BIFocal

  2. tom

    This assumes a lot of SharePoint knowledge.
    Does this supersede the older SQL Server 2008 R2 web part(s) mentioned in your earlier article??
    Should those AND this be both installed??
    Greater clarity should be provided, please do not assume all your readers are SharePoint/SQL experts.
    Thank you, Tom

  3. Chris Zelak

    John, regarding KCD requirement…is another case where KCD would not be required installing SSRS on your SQL DB Engine server? I understand the scaling issues here…just checking if this too would be an option.

    Thanks,
    -Chris

    PS. Thanks for the BI Focus podcasts! Great stuff!

  4. John White Post author

    Hey Chris – unfortunately no. If you’re ok using proxy connections, then there’s no need to do KCD to the DB server, but in this case’ it’s required to allow the consuming user to connect through SharePoint to the SSRS Native mode server. It’s for accessing the RDL files themselves. For the moment, there’s no way to do that via a proxy account.

    Thanks for the listens!

  5. martin griffiths

    you mention for sharepoint online you could use a script editor web part to show an SSRS report as the report viewer control isn’t currently available for sharepoint online only on prem. I’ve managed to get it to work using a page viewer (although I get the Show all content appear each time I go in to the page), but do you have an example of where you’ve used the script editor approach please?

Leave a Reply

Your email address will not be published.