SQL Server Reporting Services (SSRS) Tutorial

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Administered via a Web interface, it can be used to prepare and deliver a variety of interactive and printed reports. The SSRS service provides an interface into Microsoft Visual Studio so that developers as well as SQL administrators can connect to SQL databases and use SSRS tools to format SQL reports in many complex ways. It also provides a ‘Report Builder’ tool for less technical users to format SQL reports of lesser complexity.

Architecture of SSRS

SSRS is a reporting platform it includes processing components.SSRS has two basics components there are

Processors: This component is used to add new functionality and it ensures the integrity of SSRS.

Extensions: This component is raised by processors and specifies the processing functionality

The important components of SSRS architecture are

• Report Builder
• Report Designer
• Report Manager
• Report Server
• Report server database

Report Builder: We can use this component easily with the drag and drop option to report design functionality. It is an ad-hoc user report publishing tool that is executed on a client computer.

Report  Designer: This tool is used to develop simple and complex reports

Report Manager: We can easily access the web-based reports using this tool.

Report server: This server is used to store metadata in SQL server database

Report Server database: This is a database that stores metadata, resources, security settings, and so on.

Benefits of SSRS

It’s important to make sure that you pick the right tool for the right job. SSRS has a specific set of things it is very good at and a specific set of things 

Pixel Perfect Control

More than any other Microsoft reporting tool, SSRS gives you a significant amount of fine-grained control over your report outputs. You have control over exactly where each report component is located. You can also control formatting details such as font, size, color and background color.

If you need something to print just right, SSRS is an ideal solution. This lends it to operational documents such as invoices, work orders, and anything else that might get mailed out to a customer.

Extracting Data

SSRS makes it very easy for you to get data out to your end users. If you have a line-of-business application with limited built-in reporting, you can get a report running against it in minutes. Once you’ve created the report, users can extract the data to whatever format they need (Word, Excel, PDF, etc.). I should note that in large-scale operations, running reports directly against an OLTP system is not advisable for performance reasons.

Data Governance

SSRS makes it easy to control who has access to your reports and data. It is possible to specify permissions on the whole server, specific folders of reports or on a single report. Permissions inherit down, like a regular file system, unless you explicitly break inheritance to specify custom permissions.

In addition to permissions, you have a central server to house and control your reports. This is critical when you need an authoritative source of truth for your reporting. Users can trust that they are reading the latest version of any given report.

In addition to the administrative side of things, SSRS provides a powerful development environment with SSDT. SQL Server Data Tools (SSDT) is based on Visual Studio, a very popular Integrated Developer Environment or IDE. SSDT makes it incredibly easy to store your reports in source control since your reporting artefacts are just XML files. Source control makes it possible to collaborate on a team or rollback to earlier versions of a report. This is a capability that is not available with Excel or Power BI reports.  

Downsides of using SSRS

At my prior employer, we used SSRS exclusively for a long time. It is a mature tool capable of covering a large number of needs. That being said, it’s not a one-size-fits-all tool. Now take a look at some areas where SSRS is a bit weaker.

Interactivity and Data Exploration

SSRS is much better at printing or exporting than it is at direct interactivity. There are some ways to get around this by using parameters, drill-through reports or action links; however, your options are still quite limited. Compare this to Power BI, where, by default, if you click on a visual, all of the other visuals automatically cross-highlight or cross-filter.

Because of the limited interactivity, SSRS is not ideal for data exploration. You have limited options for slicing and dicing the data. SSRS makes more sense when you know what you want the end result to look like. If you play around with the data, you are much better off with Excel or Power BI.

Learning Curve

While SSRS isn’t difficult to learn, it can be a bit unintuitive. Getting started is very easy, with a wizard guiding you through each step. After that, it’s not difficult to drag and drop new objects and change properties to existing objects. Going beyond the basics can be a struggle, however.

What I found most challenging when learning SSRS was dealing with container objects and grouping. For example, it took me quite a while to understand how to add summary rows versus detail rows. As another example, where something is placed on the report dramatically affects which dataset it is pulling from or if you are displaying detailed information or summary information.

Initial Pricing

Whether SSRS’s pricing is a strength or a weakness depends a lot on context. Compared to tools like Qlikview or Tableau, SSRS can be quite cheap. Instead of paying per user, you are paying per core just like SQL Server. SQL Server 2017 costs $1,859 per core for Standard or $7,128 per core for Enterprise edition.

If you have a lot of low-frequency users or can reuse an existing SQL Server, then this can be the way to go. That being said, most organizations will host SSRS on its own server for performance reasons. This means you can easily be paying $7,500 just for SSRS licensing.

If you are looking to start small and grow out organically, Power BI might be a better fit. Power BI is licensed by user at $10 per user, per month. Even Excel is pretty affordable with an Office 365 E3 license costing $25 per user per month, which is often already paid for by organizations.

Applications

Given these pros and cons, when does it make sense to use SSRS? As I said before, you can’t just look at what the tool can do. You also must consider if it is a good fit for your organization.

Printed Documents

If you need to print something out, SSRS is a no-brainer. While I’ve seen people use Excel for creating invoices, I wouldn’t advise it. For anything that requires branding, strong formatting control, or printing control, SSRS comes out on top.

SSRS has support for more advanced printing features as well, such as footers, headers, watermarks, and page numbers. You can easily configure the margins and layout of your report to get it exactly the way you want.

Detail Heavy Reporting

SSRS is excellent for displaying lots of textual and numerical data. You can format information to be quite readable. SSRS is an ideal fit for any operational reporting. Think anything that needs printed on a daily basis: work orders, invoices, purchase orders, etc.

Excel, by contrast, is great for displaying lots of numbers but the formatting and layout piece can get messy quickly. Finally, Power BI just wasn’t designed for this kind of work. While it does have table and matrix controls, it is optimized for displaying charts and for interactive reporting.

Strong SQL Skills

If your organization has strong T-SQL and SQL Server skills, SSRS is a good fit. This is because SSRS is licensed the same as SQL Server and is administered as a SQL Server component. Additionally, there is an active SQL community and plenty of resources to learn more about it.

Simple Mobile Reports

Up until 2016, SSRS was a poor choice for mobile reports. While it was always possible to expose the web portal to the outside internet, trying to read SSRS reports on a small mobile browser was a pain.

With the acquisition of Datazen, that changed. Now it is easy to quickly take a shared dataset and create something that looks good on a phone or tablet. Additionally, the SSRS Mobile tooling provides dummy data so you can start with your report design and work your way backwards.

SSRS Installation

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources.

When running the SSRS installation, you will need to install the Database Engine and SQL Server Reporting Services in Native mode. It is easier to install both services at one time, because you can choose to have the installation process configure the SSRS services for you. If SSRS is installed later, or if you select not to configure it at installation, you will have to configure it manually. During installation, be sure to select these features:

• Database Engine
• SQL Server Reporting Services – Native (if installing 2008 R2, you’ll be asked on a subsequent screen to select the mode)
• Management Tools – Basic
• Management Tools – Complete
• Business Intelligence Development Tools or SQL Server Data Tools if available with the media you are using, which is dependent on the version

If given the choice, select Install and Configure.

SSRS Development Tools

Business Intelligence Development Studio (BIDS) as the SSRS development tool. However, starting with SQL Server 2012, you will use SQL Server Data Tools – Business Intelligence (SSDT-BI), which may or may not be on the SQL Server media, depending on the version. Each tool runs as an add-in for Visual Studio. If you do not have Visual Studio in place, the BIDS or SSDT-BI installation will install a Visual Studio shell.

The following table should help you navigate the options, understand the differences, and find the correct process for installing the development tool for your version of SQL Server.

SSRS configuration

If you were unable to let the installation process configure SSRS for you, or are installing SSRS after the initial engine installation, or maybe you decided to configure SSRS on your own for fun, you will need to launch the Reporting Services Configuration Manager.

As discussed, we need to associate SSRS with a SQL Server instance, where it can create the reporting databases (ReportServer and ReportServerTempdb). When prompted, select the instance name and click Connect.

On the next screen, select the Database page on the left menu and click Change Database.

Select Create a new report server database and click Next. Enter the name of the SQL Server instance where you wish to create the reporting databases and click Next.

On the following screen, we configure the report databases. We can choose a different name from the default ofReportServer, if desired, but only do so if you have a good reason such as multiple instances of Reporting Services on the same server.

Continue clicking though the wizard and complete it. Once done, we need to configure the Report Server Web Service, so click the Web Service URL link.

Accept the defaults and click Apply. Finally, we need to configure Report Manager, so click the Report Manager URL link. Again, accept the defaults and click Apply.