SQL Server Reporting Services Quick Reference Guide

SQL 2022 Install

Installing SSRS with the latest version and the Microsoft download location for Windows.

SSRS 2002 Install

SSRS Download

SQL 2022 System Requirements

The system requirements for the latest version of SSRS

System Requirements

Prior Installation

If prior installations are needed (and you have the prior installation files)

First standalone Install

SSRS 2019 Install

SSRS 2017 Install

Enable for AWS RDS

Use an Amazon RDS SQL Server instance

Connecting to Amazon RDS

Installing Visual Studio Reporting Services Projects / Extensions

To develop reports in SSRS, must install Visual Studio plus the Reporting Services Project Extension.

Add SSRS Project Extension

Visual Studio

SSRS Configuration

After installing SSRS, you will need to configure several settings, including service account, URL, database, email, execution account, subscription settings, and encryption keys. Be prepared to answer these prompts and have a location to securely save the details and files. Once all these setup items are completed, test your new website.

SSRS 2002 Configuration Settings

SSRS Configuration Manager

SSRS Backup

Backing up SSRS instances requires some forethought. Be sure to backup the encryption keys mentioned above, along with the SSRS databases that are set up during configuration. You also should use Source Control to backup the files (rdl and rds, for example) you create as you produce data sets and report models.

Backup SSRS tasks

SSRS Branding

To set a consistent look and feel, SSRS allows customization of the coloring and logos used within the SSRS Portal (home page).

SSRS Branding

Customize Report Manager Portal

SSRS Monitoring

After you create reports and they start to be used, you will also want to monitor who is running those reports and how long they take to execute. Fortunately, SSRS includes the execution log, which records those details. More broadly, you can review the ReportServer database to see details on items like subscriptions, parameters, and query text.

Execution Log

ReportServer Database

Report Execution Schedule

Performance Improvement Tips

RS.EXE

SSRS includes a utility called RS.EXE, which can run various operations such as publishing reports, adjusting security, and configuring some SSRS properties.

Running RS.EXE

RS.EXE (video)

Data Sources

Data sources are the connection between the report model (i.e., the template for the report view) and the data. The data pipeline feeds the report. As you can see from the resources to the right, there are many available data source connections for SSRS. Be sure you have the appropriate credentials to connect to a data source when creating the data source. You will generally need a login and password that has access to a database.

Data sources can be shared across reports or embedded into a single report.

Creating Data Source

SSAS

JSON

MySQL

Teradata

Dynamic Data Sources

Full List of Available Data Sources

Data Sets

Whereas data sources are the pipeline to connect to the location of the data, data sets are exactly that: sets of data that have been extracted to SSRS via the data source. Similar to data sources, data sets can be shared or embedded with shared data sets, allowing a report designer to share data across many reports.

A data set allows you two main additional features that assist with querying a dataset: 1) the use of parameters (see below), 2) the ability to use a query, an entire table, or, in some cases, a stored procedure.

Creating Data Sets

Parameters

SSRS allows not only querying a data set but also a report designer to create available and hidden parameters. Then, it can be used to filter a query or data set or even to control other logic within a report.

Report parameters can be sourced from constants, queries, and even cascading workflows.

Creating Parameters

Cascading Parameters

Date Based Parameters

Select All Parameter Values by Default

Display on Report Parameters Selected

Use Optional Parameters

Creating Report

There are so many ways a report can be designed, laid out, templated, and deployed. The basic components of a report are the main design area or grid, plus an optional header and footer. Within each of these areas, report objects, such as tables, matrices, charts, images, and maps, can be added. Within the table and matrix object, you can create row and column groups.

Creating a Report

Creating a Report Template

Subreports and Drilldown

SSRS features provide multiple opportunities to “see more details” concerning a particular set of figures or charts. One option is through the use of subreports. Subreports embed another report (rdl file) into a main or master report. Alternatively, SSRS provides direct drill-down using various toggle, link, and pop-up tooltip options.

Adding Subreports

Map Drilldown

Dynamic Drilldown

Formatting

A report designer could spend hours formatting a report. Those designers want the report’s look to be perfect for the audience reviewing and reading it. It is important to have consistency in font, colors, header, rows, columns, dates, sorting, and number formats. This functionality also includes the creation of navigation paths, such as bookmarks and document maps, that also lead to items, such as page breaks and placeholder-level formatting.

Alternating Line Formats

Conditional Formatting

Interactive Sorting

Repeating Headers

Rows per Page Limit

Bookmarks and Document Maps

Page Breaks

Placeholder Formatting

Charts

Numbers are great, but charts suggest a picture is worth 1,000 words. The variety of charts and visualization in SSRS is quite extensive and easy to implement. Some visualizations include trendlines, indicators, box plots, and pie charts.

Furthermore, due to the large variety of settings, a report designer can customize and personalize many charts in various ways, such as colors, multi-axis, multi-series, and embedded charts.

Box Plots

Indicators

Pie Charts

Multi-layer Pie Chart

Trend Lines

Custom Color Palettes

Embedded Charts

Multi-Series Charts

Secondary Axis

ReportItems Collection

The ReportItems collection is a set of objects that allows a report designer to reference a set of items within an SSRS report. This collection can read items such as the value in a textbox.

ReportItems

Textbox Expressions

Subscriptions

Subscriptions allow you to execute a report at a set interval with the results being sent to a specified location, like an email or file share.

Setting up Subscriptions

Site Wide Permissions

SSRS contains various authorization and authentication points. One of those authentication points is checked when users attempt to access the SSRS portal and individual folders under the portal home page.

Roles and Permissions

Custom Security

Report Level Permissions

At the report level, permissions checkpoints include access to the data source itself, access to the data sets, and permissions set by authenticated users.

Row Level Security

Column Level Security

Install Report Builder

Report Builder is a lightweight development tool that allows nearly any report consumer to design their own reports.

Download Report Builder

Create Report

Creating a report in Report Builder is menu-driven, but the data sources (and related credentials) need to be available to the report designer.

Create Report Builder Report