SSRS — SQL Server Reporting Services
What it is & where it is used
SSRS (SQL Server Reporting Services) is Microsoft's server-based platform for paginated reports — pixel-perfect, printable reports (invoices, statements, operational lists) that page nicely to PDF, Excel, Word or print.
Components:
| Component | Role |
|---|---|
| Report Designer (Visual Studio Reporting Services Projects) / Report Builder | Author .rdl reports |
| Report Server (Windows service) | Processes and renders reports, runs subscriptions |
| ReportServer + ReportServerTempDB databases | Catalog (reports, data sources, security, schedules) and session/cache data |
Web portal (/Reports) | Browse, run, manage reports, KPIs, mobile reports (retired in 2022) |
Web service (/ReportServer) | SOAP/REST APIs, URL access |
| Report Server Configuration Manager | Configure URLs, service account, DB, email, encryption keys |
Report definitions are RDL (Report Definition Language) XML files. Shared data sources (.rds) and shared datasets (.rsd) are reusable.
Paginated vs interactive: SSRS/paginated reports are for fixed-layout operational output; Power BI reports are for interactive analytics and dashboards. Power BI Report Server (on-prem) hosts both. Power BI Service also hosts paginated reports.
Versions & history
| Version | Highlights |
|---|---|
| SSRS 2000 / 2005 | Introduced as an add-on for SQL Server 2000; 2005 added Report Builder 1.0 |
| 2008 / 2008 R2 | Tablix (combines table/matrix/list), new charts, gauges; R2: maps, sparklines, data bars, shared datasets, Report Builder 3.0 |
| 2012 | SharePoint integrated mode as a SharePoint service, data alerts, Excel/Word 2010 renderers |
| 2016 | Modern web portal (HTML5), mobile reports & KPIs, Power BI pinning, new charts |
| 2017 | Separate installer (no longer in SQL setup); REST API |
| 2019 | Azure SQL Managed Instance catalog support, Microsoft Entra (Azure AD) app proxy support, accessibility |
| 2022 | Updated portal (Angular), accessibility, security improvements; mobile reports and Pin to Power BI removed |
| SQL Server 2025 era | Microsoft announced that Power BI Report Server is the on-premises reporting solution going forward — no new SSRS version after 2022 (SSRS 2022 stays supported under its lifecycle). Existing RDL reports run on Power BI Report Server and in the Power BI service as paginated reports |
Lifecycle
Report development lifecycle: requirements (layout, parameters, audience, delivery) → design data model/queries (stored procedures preferred) → create data source and datasets → design layout (tablix, groups, charts) → add parameters and expressions → preview/test (accuracy, page breaks, export formats) → deploy to the report server (dev → test → prod) → configure security, caching, snapshots, subscriptions → monitor execution log → maintain.
Report processing lifecycle (when a user runs a report):
- Request arrives via web portal or URL with parameters.
- Authentication/authorisation — Windows (or custom/forms) authentication; role assignments checked on the item.
- Data processing — the report processor executes dataset queries against data sources (unless served from cache/snapshot).
- Report processing — combines data with the layout: grouping, sorting, aggregates, expressions → an intermediate format.
- Rendering — the requested renderer produces HTML, PDF, Excel, Word, CSV, XML, TIFF/image, PowerPoint.
- Delivery — returned to the browser, or for subscriptions emailed / written to a file share / SharePoint.
- Logging — execution details go to
ExecutionLog3(time in data retrieval vs processing vs rendering).
Expression evaluation order matters for things like RunningValue, page headers (can't reference dataset fields directly — use ReportItems!TextboxName.Value) and visibility.
Tools used to develop
| Tool | Use |
|---|---|
| Visual Studio + Microsoft Reporting Services Projects extension | Report projects, deploy to server |
| Report Builder (standalone, free) | Business-friendly authoring |
| Power BI Report Builder | Paginated reports for Power BI Service / Report Server |
| Report Server Configuration Manager | Server setup, backup encryption keys |
| SSMS | Connect to Reporting Services (jobs, roles), query ReportServer DB |
| Web portal | Manage folders, security, subscriptions, caching |
rs.exe utility, PowerShell ReportingServicesTools module, REST API | Automated deployment and administration |
| ReportViewer control (WinForms/ASP.NET WebForms) | Embed reports in apps (no official ASP.NET Core viewer — use URL access/REST or third-party) |