Ssrs

---
name: ssrs
description: "Expert coverage of SQL Server Reporting Services (SSRS) across all versions. Provides deep expertise in RDL report design, data sources, subscriptions, rendering, security, deployment, and migration to Power BI Report Server. WHEN: \"SSRS\", \"SQL Server Reporting Services\", \"RDL report\", \"Report Builder\", \"ReportServer\", \"SSRS subscription\", \"paginated report\", \"report definition language\", \"SSRS web portal\", \"rs.exe\", \"ReportingServicesTools\", \"SSRS migration\", \"Power BI Report Server\", \"PBIRS\"."
license: MIT
---

# SSRS

This skill covers SQL Server Reporting Services (SSRS) across all supported versions (2019 and 2022) and the transition to Power BI Report Server (PBIRS) in SQL Server 2025:

- Report Definition Language (RDL) -- XML-based report schema, expressions, data regions, parameters
- Report Server architecture -- engine, catalog databases, rendering pipeline, web portal
- Data sources and datasets -- embedded vs shared, credential management, query optimization
- Subscriptions and delivery -- standard, data-driven, email, file share, scheduling
- Rendering extensions -- PDF, Excel, Word, HTML, CSV, XML, TIFF, MHTML, PowerPoint
- Security -- role-based access control, SSL/TLS, row-level filtering, service accounts
- Deployment and CI/CD -- rs.exe, ReportingServicesTools PowerShell module, REST API v2.0, SSDT
- Migration -- SSRS to PBIRS, SSRS to Power BI Service, version upgrades

When a question is version-specific, read the matching file under `references/versions/`. When the version is unknown, provide general guidance and note where behavior differs across versions.

## How to Approach Tasks

1. **Classify** the request:
   - **Report design / RDL** -- Load `references/architecture.md` for RDL schema, data regions, expressions, parameters, rendering
   - **Troubleshooting** -- Load `references/diagnostics.md` for ExecutionLog3 queries, common errors, subscription failures, timeout analysis
   - **Best practices** -- Load `references/best-practices.md` for performance, parameter design, security, CI/CD, subscription management
   - **Architecture / deployment** -- Load `references/architecture.md` for server components, scale-out, high availability, configuration files
   - **Migration** -- Identify source and target, load both `references/diagnostics.md` (migration diagnostics) and version agents as needed

2. **Identify version** -- Determine which SSRS version the user runs. Key version-gated features:
   - Angular web portal (2022+)
   - TLS 1.3 support (2022+)
   - Mobile reports (removed in 2022)
   - Full-screen report view (2022+)
   - Azure AD Application Proxy guidance (2019+)
   - If version is unclear, ask.

3. **Load context** -- Read the relevant reference file for deep technical detail.

4. **Analyze** -- Apply SSRS-specific reasoning. Consider data source type, credential model, rendering format, deployment topology.

5. **Recommend** -- Provide actionable guidance with RDL examples, SQL queries, PowerShell commands, or configuration snippets.

6. **Verify** -- Suggest validation steps (ExecutionLog3 queries, trace log inspection, test rendering, subscription status checks).

## Core Architecture

### How SSRS Works

```
                  ┌────────────────┐
                  │  .rdl files    │  Report Definition Language (XML)
                  └───────┬────────┘
                          │
                  ┌───────▼────────┐
                  │  Report Server │  Core processing engine
                  │    Engine      │
                  └───────┬────────┘
                          │
            ┌─────────────┼─────────────┐
            │             │             │
     ┌──────▼──────┐ ┌───▼─────┐ ┌─────▼──────┐
     │ ReportServer │ │  Data   │ │ Rendering  │
     │  Database    │ │ Sources │ │ Extensions │
     │  (catalog)   │ │ (SQL,   │ │ (PDF, XLS, │
     └─────────────┘ │ Oracle) │ │  HTML, CSV) │
                      └─────────┘ └────────────┘
```

1. **Retrieve** -- Report Server loads the RDL definition from the ReportServer catalog database
2. **Execute** -- Queries run against configured data sources, retrieving result sets
3. **Process** -- Data is combined with the RDL layout, expressions are evaluated, an intermediate format is produced
4. **Render** -- The intermediate format is transformed into the requested output (HTML, PDF, Excel, etc.) via rendering extensions

### Dual Database Architecture

- **ReportServer** -- Catalog database storing report definitions, metadata, folder hierarchy, security, scheduling, subscriptions, encrypted credentials
- **ReportServerTempDB** -- Temporary storage for cached reports, session data, intermediate processing products. Survives restarts (unlike SQL Server tempdb)

### Report Definition Language (RDL)

RDL is the XML schema that defines every aspect of an SSRS report:

```
Report
  +-- DataSources          (connection definitions)
  +-- DataSets             (queries + field mappings)
  +-- ReportParameters     (user input: String, Integer, Float, Boolean, DateTime)
  +-- Body
  |     +-- ReportItems    (Tablix, Chart, Gauge, Map, Image, Subreport)
  +-- PageHeader / PageFooter
```

- Expressions use VB.NET syntax: `=IIF(Fields!Revenue.Value > 1000000, "Green", "Red")`
- Built-in collections: `Fields`, `Parameters`, `Globals`, `User`, `ReportItems`
- Aggregate functions: `Sum`, `Count`, `Avg`, `Min`, `Max`, `CountDistinct`, `RunningValue`
- File extensions: `.rdl` (server reports), `.rdlc` (client reports for ReportViewer control)

### Data Sources

| Aspect | Embedded | Shared |
|--------|----------|--------|
| **Scope** | Single report | Available server-wide |
| **Management** | Update per-report | Update once, all reports pick up changes |
| **Best for** | Report-specific connections | Enterprise-wide standard connections |

Credential options: prompt, stored (encrypted), Windows Integrated (Kerberos), no credentials.

### Subscriptions and Delivery

- **Standard subscriptions** -- Fixed parameters, email (SMTP) or file share (UNC) delivery, SQL Server Agent scheduled
- **Data-driven subscriptions** -- Enterprise Edition; dynamic delivery per query row (varying recipients, parameters, format)
- **Snapshots** -- Pre-executed reports stored in catalog for point-in-time historical reporting
- **Caching** -- Temporary copies in ReportServerTempDB; time-based or schedule-based expiration

### Rendering Formats

| Format | Extension | Typical Use |
|--------|-----------|-------------|
| HTML 5 | HTML | Web portal viewing (default) |
| PDF | PDF | Print-ready, archival |
| Excel | XLSX | Data analysis |
| Word | DOCX | Editable documents |
| PowerPoint | PPTX | Presentations (2016+) |
| CSV | CSV | Data exchange |
| XML | XML | Data interchange |
| TIFF | TIFF | Image archival, faxing |
| MHTML | Web Archive | Email embedding |

## Report Design Essentials

### Layout Rules

- Ensure `Body Width + Left Margin + Right Margin <= Page Width` to prevent blank pages in PDF/print
- Minimize white space between report items -- SSRS renders gaps literally
- Use a common template for consistent headers, footers, and branding across all reports
- Display selected parameter values in header/footer so users know what filters were applied

### Parameter Best Practices

- Use descriptive names (`StartDate`, `RegionID`) not auto-generated names
- Provide default values so reports render immediately (critical for subscriptions and cache)
- Keep cascading parameter chains to 2-3 levels to avoid excessive round-trips
- For multi-value parameters with large lists, use table-valued parameters instead of long `IN` clauses

### Performance Critical Path

1. **Filter at the database** -- `WHERE` clauses beat report-level filters
2. **Avoid subreports in detail rows** -- Each instance fires a separate query (N+1 problem)
3. **Use stored procedures** -- Execution plan caching, easier tuning, parameterized by default
4. **Aggregate in SQL** -- Database servers handle `SUM`/`COUNT` more efficiently than the Report Server
5. **Set execution timeouts** -- Default is no timeout; always configure appropriate limits

## SSRS vs Power BI Report Server (PBIRS)

| Aspect | SSRS | PBIRS |
|--------|------|-------|
| Paginated reports (RDL) | Yes | Yes (full compatibility) |
| Power BI reports (.pbix) | No | Yes |
| Update cadence | Tied to SQL Server releases | Roughly every 4 months |
| Data-driven subscriptions | Enterprise only | Yes |
| Licensing (pre-2025) | Included with SQL Server | Enterprise + SA only |
| Licensing (SQL Server 2025+) | No new version | Any paid SQL Server edition |

**PBIRS is a superset of SSRS.** All RDL report assets transfer with minimal or no modification. PBIRS adds Power BI interactive report hosting, DAX data models, and DirectQuery support.

## Future Direction

**SSRS 2022 is the final standalone SSRS release.** Starting with SQL Server 2025, Microsoft has consolidated all on-premises reporting under Power BI Report Server:

- No new SSRS version ships with SQL Server 2025
- SSRS 2022 receives security patches through January 2033
- PBIRS is now available with any paid SQL Server edition (previously Enterprise + SA only)
- Migration from SSRS to PBIRS is low-complexity (database backup/restore preserves reports, subscriptions, security)

Organizations should plan PBIRS migration timelines. For details, see `references/versions/2025.md`.

## Deployment and Automation

### Tools

| Tool | Use Case |
|------|----------|
| `rs.exe` | VB.NET scripted deployment and administration |
| `ReportingServicesTools` (PowerShell) | 40+ cmdlets for report/data source/subscription management |
| REST API v2.0 | Programmatic CRUD for catalog items |
| SSDT / Visual Studio | Project-based development with source control |
| Report Builder | End-user ad-hoc report creation |

### CI/CD Pattern

1. Store `.rdl`, `.rds`, `.rsd` files in Git
2. Validate RDL (XML schema check) in CI pipeline
3. Deploy with PowerShell `ReportingServicesTools` or REST API in CD pipeline
4. Override data source connection strings per environment
5. Promote through Dev > Test > Production

## Security Overview

### Role-Based Access Control

SSRS uses a two-level role system:

- **System-level roles** -- Site-wide operations (System Administrator, System User)
- **Item-level roles** -- Per-folder/report permissions (Content Manager, Publisher, Browser, Report Builder, My Reports)

Assign roles to Active Directory groups (not individual users). Organize reports into folders by department and apply security at the folder level. Reports inherit parent folder permissions by default.

### Row-Level Filtering

SSRS has no built-in row-level security. Implement via query-based filtering using the `User!UserID` built-in field:

```sql
WHERE ManagerID = @UserID
```

### SSL/TLS

Always configure HTTPS for both the Report Server web service and web portal. SSL must be configured in two places via Reporting Services Configuration Manager. TLS 1.3 is supported in SSRS 2022.

## URL Access

Reports can be rendered and controlled via URL parameters:

```
http://<server>/ReportServer?/<folder>/<report>&rs:Format=PDF&Year=2024
```

- `rs:` prefix -- Report Server parameters (`rs:Format`, `rs:Command`)
- `rc:` prefix -- HTML Viewer parameters (`rc:Toolbar`, `rc:Parameters`)
- No prefix -- Report parameters (`&Year=2024&Region=West`)

## Anti-Patterns

| Anti-Pattern | Why It Fails | Better Approach |
|---|---|---|
| Subreports in detail rows | N+1 query execution; one query per row | Use JOINs, lookups, or shared datasets |
| No execution timeout | Runaway queries consume all server resources | Always set report and query timeouts |
| `SELECT *` in datasets | Retrieves unnecessary columns, wastes memory | Select only needed columns |
| Report-level filtering on large datasets | Full dataset transferred then filtered client-side | Filter in the SQL `WHERE` clause |
| Embedded images everywhere | Inflates RDL size, increases memory per execution | Use external/URL-based images |
| Ignoring ExecutionLog3 | Performance problems go undetected | Query ExecutionLog3 regularly for slow/failed reports |
| Single service account for everything | Over-privileged, audit trail unclear | Dedicated service accounts per environment |
| No encryption key backup | Lost key = irrecoverable encrypted credentials | Back up encryption key after every service account change |

## Version-Specific Guidance

| Version | Reference | What's Version-Specific |
|---|---|---|
| SSRS 2019 | `references/versions/2019.md` | Azure AD Application Proxy, accessibility improvements |
| SSRS 2022 | `references/versions/2022.md` | Angular portal, TLS 1.3, mobile reports removed, final standalone release |
| SQL Server 2025 / PBIRS | `references/versions/2025.md` | SSRS replaced by PBIRS, migration guidance, licensing changes |

## Reference Files

- `references/architecture.md` -- Server components, RDL deep dive, data sources, rendering pipeline, subscriptions, deployment topology, configuration files
- `references/best-practices.md` -- Report design, parameter patterns, performance optimization, subscription management, security, CI/CD
- `references/diagnostics.md` -- ExecutionLog3 queries, common errors, subscription failures, timeout analysis, configuration diagnostics, migration troubleshooting

## Diagnostic Scripts

Ready-made T-SQL against the ReportServer catalog database in `scripts/`, numbered by investigation order. All read-only; run in SSMS against the catalog instance.

- `scripts/01-execution-summary-daily.sql` -- 14-day volume/latency trend with per-phase breakdown
- `scripts/02-slowest-reports.sql` -- Top 20 slow reports; which phase (data/processing/rendering) dominates
- `scripts/03-report-usage-ranking.sql` -- Usage ranking plus never-run reports (cleanup/migration scoping)
- `scripts/04-subscription-failures.sql` -- Failed/stalled subscriptions with owner and delivery type
- `scripts/05-execution-source-breakdown.sql` -- Live vs Cache vs Snapshot mix (caching opportunities)

## Cross-References

- `overview` skill, this plugin -- cross-platform BI questions
- `sql-server` skill, database plugin -- database engine powering SSRS data sources and catalog