SQL Server Reporting Services (SSRS) has experienced some very significant improvements in the 2016 version. Equally has been the case Since SQL Server 2005 SP1, information technology runs in either Native, or SharePoint Integrated mode. Integrated mode (the subject field of this article) requires SharePoint 2016, and it is required for SharePoint to be able to render Power View reports in a browser.  This article walks through the setup and configuration of SSRS 2016 Integrated way in a SharePoint 2016 farm.

The process for setting up SSRS in Integrated mode is fiddling changed with 2016. The process consists of installing the $.25 on the SharePoint server(s), creating and configuring the service applications, deploying the solution, and configuring document libraries to contain report elements.

Installing SSRS 2016 on Sharepoint Servers

When running in integrated mode, SSRS MUST exist installed on a server that is office of the SharePoint subcontract. This simply makes sense because it is deployed equally a SharePoint Service Awarding. Unfortunately, the fact that is distributed as role of the SQL Server media causes confusion for some.

As of this writing, SSRS must be installed on a SharePoint 2016 that is configured in a Custom Role. MinRoles are new to SharePoint 2016, and SSRS does not back up whatsoever other role than the Custom part. If your server is not running the Custom role, installation volition succeed, but SSRS will be close down by the roles engine during the next maintenance window. In order to check which role your server is using, and to possibly change it, you tin utilise either PowerShell or Central Admin. With Central Admin, the setting is found in "Organisation Settings", under the "Servers" category every bit "Convert server role in this farm".

Selecting this pick opens the role configuration dialog, which is quite simple.

If the role is already fix to Custom, yous are skillful to go. Otherwise, it tin can exist changed with the "New Office" drop down dialog.

Once the correct role is in place, SSRS tin can be installed. The first step is to mount the SQL Server media on a SharePoint server, and run the standard SQL Server installer. SSRS Integrated style is part of the Shared Features collection (ie no SQL instances are installed), and it consists of ii parts.

The offset option, "Reporting Services – SharePoint" is the actual SSRS Service awarding. This should be installed on any SharePoint servers allocated to doing the heavy lifting of rendering reports – the "app" servers. The second option "Reporting Services Add together-in …" is used to connect a SharePoint server to an example of the SSRS Service application. This should exist installed on any SharePoint front-terminate servers at a minimum, but I recommend installing it on all of them every bit a convenience.

Later a few "Next"s and "OK"s, the SSRS $.25 should be installed on a server. The next footstep is to Create and configure the Shared Service Awarding itself.

Creating the SSRS Shared Service Application

Once the bits are installed, an SSRS Service application is created in the same fashion as any other service application. From the Service Applications interface in Central Administration, select "New" from the ribbon, and and then select "SQL Server Reporting Services Service Application".

Yous volition then be presented with a configuration dialog where you lot will need to specify a name for the service and a few other configuration parameters.

I typically use the same application pool as most of the other SharePoint services, and I always alter the name of the database. The default database proper noun contains a GUID, and nobody likes GUIDs in their database names. The SSRS volition really create iii databases, one with the proper name specified, and 2 others that use this proper name as a base. Also, if yous'll be using other Reporting Services databases on the same SQL Server – for Native mode as an example, it'southward a skillful idea to name it and then that it'southward easily distinguishable. In this case "Integrated" is added to the end.

Scrolling down, you'll see options for activating the SSRS features in all of the subcontract's site collections. The features can exist activated from the site collections equally well; this is but a convenience.

Once saved, additional SSRS configuration items tin be configured, and should be. At the very least, the subscription options should be configured, and the encryption central should exist backed up, but these operations are non essential for bones setup, so they volition not exist done here. The next operation will be to enable a document library for SSRS reports.

Creating a Reporting Library

Enabling a certificate library in SharePoint for SSRS reports is unchanged from the by several versions. The first step is to add a new document library by going into "All Content" for a site, and selecting "Add an App". You lot may be tempted to select "Reports" or "Report Document Library" at this point – don't. The "Reports" library template that ships with SharePoint 2016 and prior contains content types for creating Excel documents in prior versions, web pages – that's it. It has zero to exercise with SSRS reports.

Select a Simple certificate library, give it a name (something like SSRS Reports, or SSRS library), and let it be created. Then, become into the library settings, click Advanced settings, and enable the employ of content types. Next, add the SSRS content types to the library past clicking "Add from existing site content types", selecting the "SQL Server Reporting Services Content Types" category, and then selecting "Data Connections" and "SSRS Report". Unless you have a specific need, practice non add the "Report Architect Model" content type. Models are a deprecated antiquity and exist but for backward compatibility.

Once added, click OK, and you will exist returned to library settings. At this point I like to remove the "Documents" content type from the list to restrict it to reports, only that volition depend on your requirements. At this betoken you should exist able to create a new written report or data source by selecting new in the library's ribbon and choosing the advisable item. This library tin can at present be used to store reports.

The concluding step is to enable and confirm support for Power View.

Power View Support

Power View support in SharePoint 2016 is provided through SSRS Integrated way (and ONLY through SSRS Integrated mode). It is manifested in 3 unlike areas:

  1. Creating and viewing a standalone Power View study from a data connection
  2. Creating and viewing a standalone Power View report from an Excel workbook in a PowerPivot gallery
  3. Using a browser to view a Power View report contained in an Excel workbook

1. Creating and viewing a standalone Ability View written report from a data connexion

Standalone Power View reports employ BISM (BI Semantic Model) connections. BISM connections tin can be added to a SharePoint library by adding the "BI Semantic Model Connection" content type to the library – this would normally be done for a connections library. A BISM connection can as well be created through an SSRS information source by selecting "Microsoft BI Semantic Model for Ability View" equally its information source type.

Creating a Power View report from either connection type follows the same process. In the library, click the ellipsis for the connection, and then the second ellipsis. From at that place, select "Create Ability View Report"

Provided that Silverlight is bachelor on the client, Power View should launch, and you should be able to build a report on the underlying data.

2. Creating and viewing a standalone Power View written report from an Excel workbook in a PowerPivot gallery

Creating a Ability View report is significantly simpler. One time SSRS is installed, it adds a small Power View icon to every workbook that is in a Power Pivot gallery.

But click on the icon, Ability View will launch, and you can build a study on the information model that is contained in the workbook. There is yet 1 additional step necessary for this to work. Considering the information model is actually stored in the SSAS PowerPivot fashion server(s), and it is SSRS (recall, Power View is part of SSRS) that is working with the model (not OOS), the service account for SSRS needs to be added to the Administrators listing on the SSAS PP fashion server(southward). In our case, the service business relationship is NAUTILUS\spServices.

3. Using a browser to view a Power View report contained in an Excel workbook

Power View reports that take been embedded in an Excel workbook require no additional configuration, they should "simply piece of work" once SSRS is configured. Still, every bit with the PowerPivot gallery, SSRS needs access to the data models, and therefor its service account needs to be in the administrators list (encounter above).

Wrapping Upward

Once installed and configured, you will have access to the new HTML5 based rendering engine and new visuals available to SSRS 2016. You lot will also exist ale to work with your existing Power View investments. However, you volition non be able to use the new mobile reports, Reporting Dashboards, Parameters customization, and Power BI integration. For that, you'll need a Native fashion SSRS case, and yes, information technology tin exist continued to SharePoint. That will be the topic of an upcoming article.