SQL Server Reporting Services

Data, either relational or multidimensional, does not have its own visible face--data is not visual. To have a complete end-to-end data management platform, Microsoft offers a service called Reporting Services (SSRS) as a part of the SQL Server technology set. This service is designated to access data from variety of sources and visualize the data to users. Reporting Services is a favorite service for centralized and managed reporting.

From an architectural point of view, the SSRS is a Windows (or newly Linux) service that offers HTTP/HTTPS endpoints for human-readable as well as web service content consuming. The human-readable endpoint is called Report Portal. It is just a web application for report consumption and management. (Formerly, the report portal was called report manager.)

Reporting Services can be installed in two modes:

• Native mode
• SharePoint mode

Reporting Services have almost the same features in both modes, including report deployment, report previews, subscriptions, or report exports to formats such as MS Excel or PDF. The only feature not present in the native mode of installation is data alerts. A data alert is a user's option to be informed when something is changed in the report's data.

SQL Server 2017 Reporting Services installation is no longer a part of SQL Server installer. From now, SSRS is installed and versioned separately. Linking to the installer is accessible from the SQL Server installation center or from the setup wizard step with Feature Selection.

When SQL Server 2016 was up to date, it had two installation modes for Reporting Services. This approach has completely changed since SQL Server 2017. Reporting Services can still be installed in both modes, but by separate installers.

When installing SSRS in native mode, web installer allows only installation of the service itself without creating the ReportServer and ReportServerTempdb databases for services metadata:

In production environments, it is a better option to install an instance of SQL Server DE on its own computer and then install SSRS on its own computer as well. Metadata databases are created later by a visual configuration tool called reporting services configuration manager.

Compared to the native mode installation, the SharePoint mode requires that SharePoint farm already exists and the downloaded installer runs on it. The installer just installs the SharePoint service application and SharePoint features containing certain SSRS web parts and features. After installation, SSRS in SharePoint mode is not working because SharePoint does not know that new components were added. The SharePoint administrator has to run two simple PowerShell commandlets to assign SSRS to SharePoint.

PowerShell commands to install SSRS in SharePoint mode are as follows:

Install-SPRSService
Install-SPRSServiceProxy

As a last step of making SSRS work on SharePoint, the content manager of certain SharePoint sites should create new custom libraries called report library. Data sources, shared datasets, report parts, and reports are then delivered to the report libraries.