SSRS — SQL Server Reporting Services

In short: SQL Server Reporting Services is a server-based reporting platform for creating, deploying and delivering paginated reports.

• Components: report designers (Visual Studio Reporting Services Projects, Report Builder), the Report Server Windows service (processing, rendering, scheduling), the ReportServer and ReportServerTempDB databases, the web portal and web service, and Report Server Configuration Manager.
• Reports are RDL (XML) files.

In short: All three are forms of the tablix data region.

• A table has fixed columns and dynamic rows (detail and group rows).
• A matrix has dynamic rows and dynamic columns based on groups — a pivot/cross-tab (e.g. products × months with totals).
• A list repeats a free-form layout per row or group — good for cards, labels or forms.

In short: A shared data source (.rds) is stored once on the report server and reused by many reports; changing it (e.g. server name, credentials) updates all of them.

• An embedded data source belongs to a single report.
• Shared ones simplify environment changes and credential management; subscriptions and caching need stored credentials.

In short: Parameters let users filter reports (dates, region) and are passed into dataset queries.

• Cascading parameters are dependent: the available values of a child parameter come from a dataset that uses the parent parameter (Country → State → City).
• The parent must appear before the child in the parameter list; changing the parent refreshes the child's list.

In short: Drill-down shows/hides detail rows within the same report by toggling group visibility (+/− icons) — all data is already retrieved.

• Drill-through opens a different (target) report when you click an item, passing parameters (e.g. click a region total → regional detail report) — the target runs its own query only when requested.

In short: Stored procedures can't receive SSRS multi-value arrays directly.

• In the dataset parameter, pass =Join(Parameters!Region.Value, ",") and split it in SQL using STRING_SPLIT(@Regions, ',') (2016+), a table-valued function or XML/JSON.
• With an embedded text query, WHERE Region IN (@Region) works natively because SSRS expands the list.

In short: Subscriptions automatically deliver reports on a schedule or event to email, a file share or SharePoint.

• Standard subscriptions have fixed recipients, parameters and format defined by the user.
• Data-driven subscriptions (Enterprise edition) get recipients, delivery settings and parameter values from a query at runtime, so each person receives a personalised report.
• Both rely on SQL Server Agent and stored credentials.

In short: Check ExecutionLog3 to see whether data retrieval, processing or rendering dominates.

• Optimise queries/stored procedures and indexes, return only needed rows/columns, filter in SQL rather than dataset filters, push aggregations to SQL, avoid subreports in detail rows (use joins/Lookup/drill-through), reduce complex expressions, use caching, snapshots or shared dataset caching for expensive reports, and schedule heavy reports via subscriptions.

In short: The bottleneck is the query.

• Capture the dataset's SQL (stored procedure) with the actual execution plan;
• look for scans, missing indexes, non-SARGable predicates on date parameters, parameter sniffing (the same proc fast in SSMS but slow from SSRS — use OPTION (RECOMPILE) or local variables), and returning far more rows than displayed.
• Add covering indexes, pre-aggregate in SQL, limit default parameter ranges, and consider caching/snapshots or a summary table refreshed by ETL.

In short: Use a data-driven subscription (Enterprise edition): a query against a table of managers (Email, RegionId, Format) drives the subscription;

• map the Region parameter to RegionId and the To field to Email;
• schedule weekly Monday 8 AM;
• deliver as PDF.
• On Standard edition, create one standard subscription per manager, or use a SQL Agent job/.NET service that calls the SSRS REST/URL API to render per region and email it.