Problem
Where can I see the various examples of the setup and features that can be utilized in SQL Server Reporting Services? I would like to set up my Reporting Services instance quickly and with minimal stress to report from my SQL Server database.
Solution
Microsoft SQL Server Reporting Services, commonly known as SSRS, continues to be a reporting platform of choice for many organizations, big and small. Even with all the hype around artificial intelligence, SSRS garners significant acclaim as the reporting platform for various and sundry organizations. SSRS provides the basis for creating paginated reports; these reports provide consistent formatting and visualization, which support numerous methods of drilling into details and exporting them to external sources such as Excel or CSV files.
For this tutorial, we are looking to accumulate the various setup references, different features, and main sub-tools of SSRS into a single living document.
These main areas include:
- Installation
- Configuration and administration
- Main and power features
- Report builder tool
- Security
Installing SQL Server Reporting Services
Topic Area | Description | Resources |
|---|---|---|
SQL 2022 Install | Installing SSRS with the latest version and the Microsoft download location for Windows. | |
SQL 2022 System Requirements | The system requirements for the latest version of SSRS | |
Prior Installation | If prior installations are needed (and you have the prior installation files) | |
Enable for AWS RDS | Use an Amazon RDS SQL Server instance | |
Installing Visual Studio Reporting Services Projects / Extensions | To develop reports in SSRS, must install Visual Studio plus the Reporting Services Project Extension. |
SQL Server Reporting Services Configuration and Administration
Topic Area | Description | Resources |
|---|---|---|
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 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. | |
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 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. | |
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. |
SQL Server Reporting Services Report Development and Features
Topic Area | Description | Resources |
|---|---|---|
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. | |
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. | |
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. | Select All Parameter Values by Default |
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. | |
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. | |
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. | |
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. | |
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. | |
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. |
SQL Server Reporting Services Security
Topic Area | Description | Resources |
|---|---|---|
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. | |
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. |
SQL Server Reporting Services Report Builder
Topic Area | Description | Resources |
|---|---|---|
Install Report Builder | Report Builder is a lightweight development tool that allows nearly any report consumer to design their own reports. | |
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. |
This tutorial should provide a report designer with a quick jumping off point for installing SSRS and creating new reports to meet your Business Intelligence (BI) needs.
Next Steps
- Additional Resources
- If you are looking for an alternative to Microsoft Server Reporitng Services, be sure to research Power BI for dashboards and Microsoft Fabric as a new analytics platform.
- If you have a need to perform ETL \ ELT operations, be sure to check out SQL Server Integration Services (SSIS) and Azure Data Factory (ADF).
