Overview

Reporting services are one of the very cool tools of SQL Server 2016. In this new version of the product, you can find several improvements in the tool. The first innovation I see is the visual format. The second is the improvement of the tools to customize reports at server and mobile level and the third in the practicality of integration with other tools. In this article, we will focus on your integration with SharePoint 2016. SQL Server Reporting Services (SSRS) is a tool for creating mobile and server-level reports. It can be installed using the SQL Server image and allows its use independently or integrated with other tools. SharePoint 2016 has this integration SSRS - SQL Server Reporting Services.

Because this is an integration, we will first validate the settings in SQL Server 2016.

Enable Reporting Services Integration in SharePoint

Provided it is enabled on the installation of SQL Server 2016.

Enable Reporting Services Add-In for SharePoint Products.

The next step is to validate at the other end of SharePoint 2016.

Install Reporting Services in SharePoint 2016 

 How to Administer the SharePoint Shell and Run the Following Image Commands.

Script:

Install-SPRSService

Install-SPRSServiceProxy

Get-SPServiceInstance -all | where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance 

Go to Central Administration - Application Management - Manager Service Application and verify that the Reporting Services service is enabled.

Start SQL Server Reporting Services Service

Go to Central Administration - System Settings - Manage services on the server.

In the list of services, SQL Server Reporting Services needs Started status.

PowerShell start SQL Server Reporting Services

Start SharePoint 2016 Management Shell as administrator.

Get-SPServiceInstance - Displays all Farm SharePoint 2016 services.

Copy the service ID key listed in the image.

Initial Status Disable

Start-SPServiceInstance and paste the service ID key listed in the image.

Status Provide - Informs that the service start process is running.

Get-SPServiceInstance - Displays all Farm SharePoint 2016 services.

Status Online - Informs that the service start is okay.

The iisreset command can be used if an existing farm is going to use the reporting services service for the first time.

Create SQL Server Reporting Services Service Application

The next step is to create the Reporting Services service in SharePoint 2016.

Go to Central Administration - Application Management - Manage Service Applications - New - SQL Server Reporting Services.

Define the name of the reporting service, enter the name of the service-related App, domain and service login that has the correct permissions, also confirm the name and instance of the database server, and finally the name of the database.

By default, the authentication type is Windows Authentication.

Press the OK button at the end of the settings

Wait for the service creation process.

You will receive a message stating that the service was successfully created. Press the OK button.

The final result corresponds to the Service of the Reporting Service created and with status Started.

Manage the Reporting Services Application

In this example, the native tool configuration was used. If you need to make any extra configuration, just click on the Reporting Service link to access the options.

Web Application connected to the Reporting Services service

If the Reporting Services service is created before the Web Application by default, it will already be enabled at creation time.

If it has been created later you will need to check for it to go to Central Administration - Application Management - Management Web Applications.

Select the Web Application and go to Service Connections and validate if the service is visible as Default in the Reporting Services services.

Enable Reporting Services reporting in a site library

The first step is to be in the library select the Library - Library Settings.

Documents - Settings - Advanced Settings.

Enable Yes in option Allow management of content types.

Press the OK button.

Press Add from existing site content type.

Add the Report Builder Report content types, which is the reporting and reporting tool.

As well as the Report Data Source option that allows you to create connections to the database.

Press the Add button to add the content and at the end press OK to confirm the operation.

After performing this action check the Content Type displays the Report Builder Report and Report Data Source buttons.

When you return to the library homepage go to Files - New and note that the two tools for creating the report will be displayed.

Create Data Source

Files - New press Report Data Source button.

  • Data Source Type: Microsoft SQL Server.
  • Connection String: Data Source=ServerDatabase\Instance;Initial Catalog=NameDatabase
  • Stored Credentials enable option.
  • Domain\UserService
  • Password UserService
  • Use as Windows credentials
  • Enable this data source
  • Press Ok Button 

Create Report

Files - New press Report Build Report.

Press Allow button. 

Wait for the Report Builder report tool to load.

Press Blank Report button.

Add Data Source

Report Builder

To add the connection to the report you must use the right mouse button and go to Add Data Source.

View Report Data Source

To add the connection to the report you must use the right mouse button and go to Data Source Properties.

Select the Data Source in the list and perform the connection test. The result must be Successful.

Press OK button.

The final result displayed in Data Source indicating that the connection was made.

References

Add Reporting Services Content Types to a SharePoint Library

https://msdn.microsoft.com/en-us/library/bb326289.aspx?f=255&MSPPError=-2147217396

Install The First Report Server in SharePoint Mode

https://msdn.microsoft.com/en-us/library/jj219068.aspx?f=255&MSPPError=-2147217396

SQL Server Reporting Services Team Blog

https://blogs.msdn.microsoft.com/sqlrsteamblog/2015/12/10/integrate-reporting-services-into-sharepoint-2016/

Install Reporting Services SharePoint Mode

https://msdn.microsoft.com/en-us/library/cc281311.aspx

Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016)

https://msdn.microsoft.com/en-us/library/gg492257.aspx

SharePoint 2013/2016 and SQL Server 2016 Supportability with SSRS fun facts!

https://blogs.msdn.microsoft.com/ronalg/2016/06/13/sharepoint-20132016-and-sql-server-2016-supportability-with-ssrs-fun-facts/