DLDevLadder📒 My Notebook
Roadmap › 2. Database, ETL & BI › Step 32
RS

SSRS — SQL Server Reporting Services

Report server architecture, data sources and datasets, tablix (table/matrix/list), parameters and cascading parameters, expressions, grouping, drill-down and drill-through, subreports, charts, subscriptions, security, performance and Power BI Report Server.
Quick revision for interview day. Print it or save it as a PDF.

Architecture

Report Designer/Report Builder → RDL → Report Server (service) + ReportServer & ReportServerTempDB → web portal (/Reports) & web service (/ReportServer) → renderers → delivery.

Data regions

Tablix = Table (dynamic rows) · Matrix (dynamic rows + columns) · List (free-form) · Chart · Gauge · Map · Sparkline · Data bar · Indicator.

Expressions

vb
=Sum(Fields!Amount.Value, "GroupName")
=IIf(cond, a, b) / =Switch(...)
=RunningValue(Fields!X.Value, Sum, Nothing)
=RowNumber(Nothing)
=Lookup(src, dest, result, "DS")
=Join(Parameters!P.Value, ",")
=Globals!PageNumber & " of " & Globals!TotalPages
=User!UserID
=ReportItems!Textbox1.Value     ' in header/footer

Parameters

Single/multi-value · cascading (child dataset uses parent) · default =Today() · hidden/internal · SP multi-value via Join + STRING_SPLIT.

Interactivity

Drill-down (toggle visibility) · Drill-through (Action → Go to report) · Subreport · Interactive sort · Document map · Bookmarks · Go to URL.

Delivery & performance

Standard vs data-driven subscriptions (Enterprise) · SQL Agent required · caching · snapshots · shared dataset cache · ExecutionLog3 (DataRetrieval / Processing / Rendering).

Security

Folder/item roles: Browser, Content Manager, Publisher, Report Builder, My Reports · System Administrator/User · back up the encryption key · row-level via User!UserID.

Future

SSRS 2022 = last SSRS · Power BI Report Server for on-prem going forward · paginated reports in Power BI Service.