Introduction to Reporting Services in SQL Server

What a report server does

A report server stores report definitions, runs their queries when someone asks for the report, lays out the result and renders it in the requested format. It is suited to:

• Pixel-precise, multi-page documents meant for printing or PDF.
• Parameterized operational reports, such as "open orders for warehouse X between two dates".
• Scheduled delivery by email or to a file share.
• Reports embedded in another application through a URL or the REST API.

It is not an interactive data exploration tool. For dashboards and self-service analysis, use Power BI reports.

Architecture

Report server service. A Windows service that hosts the web portal, the web service and REST API, and the background processor that runs schedules and subscriptions. SSRS has not depended on IIS for a long time. The service listens on its own reserved URLs.

Report server databases. Two SQL Server databases, named ReportServer and ReportServerTempDB by default. The first holds reports, data sources, security settings, schedules and execution history. The second holds session state and cached results. Back up ReportServer like any other important database. ReportServerTempDB holds nothing permanent, but the report server cannot work without it.

Encryption key. Stored credentials and connection strings in the catalog are encrypted with a key held by the report server. Back it up when you install, using Report Server Configuration Manager. If you restore the databases on another server without the key, every stored credential has to be re-entered.

Web portal. The browser interface where users find and run reports and administrators manage folders, permissions, data sources and subscriptions. By default it is at http://<server>/Reports, and the web service endpoint is at http://<server>/ReportServer. Older articles call the portal "Report Manager", the name it had before SQL Server 2016.

Report Server Configuration Manager. A desktop tool on the server for setting the service account, the URLs, the catalog database connection, email settings and the encryption key.

Since SQL Server 2017, Reporting Services has had its own installer, downloaded separately. It is no longer a checkbox in SQL Server Setup. The catalog databases still need a Database Engine instance, which can be on another machine.

What a report is made of

A paginated report is an XML file with the extension .rdl (Report Definition Language). It contains:

• Data sources. Connection details for SQL Server, Azure SQL, Analysis Services, Oracle, ODBC and OLE DB sources, among others. A data source can be embedded in one report or shared across many.
• Datasets. A query or stored procedure call against a data source, and the fields it returns.
• Parameters. Values the user supplies or picks from a list, passed to dataset queries or used in expressions.
• Data regions. Tables, matrices (cross-tabs with dynamic columns), lists, charts, gauges and maps that display dataset rows.
• Expressions. Formulas in a Visual Basic-style syntax, for example =Sum(Fields!Amount.Value), used for values, formatting and visibility.

A dataset query is ordinary SQL with parameters:

SELECT o.OrderId, o.OrderDate, c.CustomerName, o.TotalDue
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= @StartDate
  AND o.OrderDate <  DATEADD(day, 1, @EndDate)
ORDER BY o.OrderDate;

Do the filtering in the query, as here, not in the report. A report-level filter pulls every row to the report server first.

Authoring tools

• Report Builder. A free standalone designer aimed at analysts and power users. It is the quickest way to build a single report and publish it. Power BI Report Builder is the equivalent for the Power BI service.
• Visual Studio with the Reporting Services projects extension. For developers who keep many reports in source control and deploy them as a project.

Both produce the same .rdl format.

Delivering reports

Users can run a report in the web portal and export it to PDF, Excel, Word, PowerPoint, CSV, XML, MHTML or TIFF.

Subscriptions run a report on a schedule and deliver it by email or to a file share. A standard subscription has fixed parameters and recipients. A data-driven subscription reads its recipients and parameter values from a query, so one definition can send each manager their own region. Data-driven subscriptions require Enterprise edition. Subscriptions need SQL Server Agent running on the instance that hosts the catalog, and they need credentials stored in the data source, because nobody is logged in when they run.

Caching and snapshots let an expensive report run once and serve many viewers from the stored result.

Security

Access is granted with role assignments on folders and items, usually to Windows or Active Directory groups. The built-in item roles are Browser (view and subscribe), Report Builder, Publisher, My Reports and Content Manager (full control). Two system-level roles, System User and System Administrator, cover site-wide settings.

Permissions on the portal only control who can open a report. What data the report can read depends on the credentials in its data source: a stored account, the viewer's own Windows identity, or a prompt.

Finding slow and unused reports

Every execution is logged in the catalog. The ExecutionLog3 view is the supported way to read it:

USE ReportServer;

SELECT TOP (20)
       ItemPath,
       COUNT(*)                  AS Executions,
       AVG(TimeDataRetrieval)    AS AvgQueryMs,
       AVG(TimeProcessing)       AS AvgProcessingMs,
       AVG(TimeRendering)        AS AvgRenderingMs,
       MAX(TimeStart)            AS LastRun
FROM dbo.ExecutionLog3
GROUP BY ItemPath
ORDER BY AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) DESC;

A high TimeDataRetrieval means the query is slow, so tune the SQL. High processing or rendering time means the report is handling too many rows or has a heavy layout. Log entries are kept for 60 days by default. Reports that never appear in the log are candidates for retirement before a migration.

Features that have been removed

Older tutorials describe features you will not find in current releases:

• Mobile reports, Mobile Report Publisher and Pin to Power BI were removed in SSRS 2022.
• SharePoint integrated mode was dropped after SQL Server 2016. Current versions run in what used to be called native mode, and a Report Viewer web part is the way to show reports on a SharePoint page.
• Report models and the original Report Builder 1.0 are long gone. Use shared datasets instead.

Moving from SSRS to Power BI Report Server

.rdl reports run on Power BI Report Server without changes. The migration is mostly a matter of moving the catalog:

1. Back up the SSRS encryption key and the ReportServer and ReportServerTempDB databases.
2. Install Power BI Report Server.
3. Restore the databases where the new server will use them, point Power BI Report Server at them in Report Server Configuration Manager, and restore the encryption key.
4. Test data source credentials, subscriptions and any custom extensions or custom security.

Read Microsoft's migration guide before you start, because the supported paths depend on your source version.

Further reading

• Reporting Services consolidation FAQ
• What is Power BI Report Server?
• Migrate a report server installation
• What's new in SQL Server Reporting Services

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting