Skip to content

Reporting Services

What Is SSRS?

SSRS (SQL Server Reporting Services) is a web-based Microsoft tool for building and publishing interactive reports based on SQL Server data.

Reports are reachable via: http://[server]/Reports

Characteristics

  • Reports are built with Report Builder or Visual Studio (SSDT)
  • Data is retrieved via views and stored procedures
  • Reports support parameters (e.g. selecting an airport, a flight)
  • Cascading parameters: the content of parameter 2 depends on the value of parameter 1
  • Exportable to PDF, Excel, Word, ...

Workflow

1. Prepare data in SQL Server
   └── Create views and stored procedures for the needed data

2. Create a report in Report Builder / Visual Studio
   └── Set up the data source (connection to SQL Server)
   └── Define the dataset (stored procedure or query)
   └── Add parameters
   └── Design the report layout

3. Publish the report to the Reporting Services web server

4. View the report through a browser
   └── http://[server]/Reports

Parameters

Parameters make a report interactive. A parameter can: - Be a free-text input field - Be a dropdown list populated from a dataset (e.g. all airports) - Depend on another parameter (cascading)

-- Dataset for the "airport" parameter
SELECT airport_id, name FROM airport ORDER BY name;

-- Dataset for the "flight" parameter (depends on the chosen airport)
SELECT f.flight_id,
       f.flightno + ' - ' + a_dep.name + ' - ' + a_arr.name AS flight
FROM   flight f
JOIN   airport a_dep ON f.departure_airport = a_dep.airport_id
JOIN   airport a_arr ON f.arrival_airport   = a_arr.airport_id
WHERE  f.departure_airport = @airport_id;