This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Reporting Services Tools

SQL Server Reporting Services contains a set of graphical and scripting tools that support the development and use of rich reports in a managed environment. The tool set includes development tools, configuration and administration tools, and report viewing tools. This article gives a brief overview of each tool in Reporting Services and how it can be accessed.

To find a tool right away, see Tutorial: How to Locate and Start Reporting Services Tools (SSRS) .

Tools for report authoring

The following table lists the available tools for report authoring in SQL Server Reporting Services.

SQL Server Mobile Report Publisher is deprecated for all releases of SQL Server Reporting Services after SQL Server Reporting Services 2019. It is discontinued starting in SQL Server Reporting Services 2022 and Power BI Report Server.

Report parts are deprecated for all releases of SQL Server Reporting Services after SQL Server Reporting Services 2019.

Tools for Report Server administration

A set of graphical and scripting tools are available for administering the report server in SQL Server Reporting Services. The tools that you use depend on the deployment mode of your report server.

Native mode

The following table lists the available tools for administering the report server that is deployed in native mode.

SharePoint integrated mode

In SharePoint mode, the Reporting Services is a service application in the SharePoint architecture, and is administered directly through SharePoint

Tools for report content management

A set of graphical and scripting tools are available for managing content in SQL Server Reporting Services. The tools that you use depend on the deployment mode of your report server.

Reporting Services Report Server Reporting Services Concepts (SSRS) What is SQL Server Reporting Services (SSRS)

Additional resources


SSRS Tutorial: What is SQL Server Reporting Services?

What is ssrs.

SSRS stands for SQL Server Reporting Services is a reporting software that allows you to produce formatted reports with tables in the form of data, graph, images, and charts. These reports are hosted on a server that can be executed any time using parameters defined by the users. It is part of Microsoft SQL Server Services suite.

In this SSRS tutorial, you will learn

Types of reporting services

Example of ssrs reporting, features of ssrs, how ssrs works, ssrs architecture, reporting life cycle, what is rdl, type of ssrs reports, advantages of using ssrs, disadvantages of using ssrs.

Here, are prime reasons for using SSRS tool:

Example of SSRS reporting

Consider a SSRS report example of a medical research institute where patients are recruited for various clinical trials.

The staff in the institute creates a database record for each patient.

Once they agree to be part of the trial, and the hospital gets the payment form the drug company based on the price at which it is ready to participate.

Without SSRS, the medical institute would need to manually email a report to the pharma company with the total number of weekly participants. The institute must also add details of every patient included in the trial, the number of drugs used, and all the unwanted situations. As a result, the time taken to collect and send this data in the correct format may consume loads of valuable time in the clinic.

If the institute were recording data, with the help of SSRS tool, they would be able to produce on-demand reports in a pre-defined format.

Now in this SSRS tutorial, we will learn how SSRS works:

Example of SSRS reporting

Example of SSRS reporting

SSRS has quite a complex architecture. The report services architecture includes development tools, administration tools, and report viewers.

Here, are important components of SSRS

Report Builder

It is an ad-hoc report publishing tool that is executed on a client’s computer. It has a drag and drop interface that is easy to use.

Report Designer

The Report designer tool helps to develop all types of reports. It is a publishing tool, that is hosted in Visual Studio or Business Intelligence Development Studio (BIDS).

Report Manager

The report managers check the report, matching it with the given requirements. They make decisions based on those reports.

Report Server

It is a server which uses the SQL Server database engine to store metadata information

Report server database

It stores metadata, report definitions, resources, security settings, delivery data, etc.

Data sources

Reporting services retrieve data from data sources like relational and multidimensional data sources.

Every organization follows a standard reporting lifecycle which can be classified as follows:

Example of SSRS reporting

Authoring: In this phase, the report author defines the layout and syntax of the data. The tools used in this process are the SQL Server Development Studio and SSRS tool.

Management: This phase involves managing a published report which is mostly part of the websites. In this stage, you need to consider access control over report execution.

Delivery: In this phase, you need to understand when the reports need to be delivered to the customer base. Delivery can be on-demand or pre-defined schedule. You can also add an automation feature of subscription which creates reports and sends to the customer automatically.

Report Definition Language is shortly known as RDL. It describes all possible elements of a report using an XML grammar which is validated by an XML schema.

The report definition of an individual report is based on RDL. It contains instructions for rendering the design of the report at the run time.

Here, are types of reports which you can develop using SSRS tool.

Some limitations of SSRS are given below:

You Might Like:

report service in sql

SQL Server Reporting Services (SSRS)

Michael Otey

SQL Server Reporting Services (SSRS) is a SQL Server subsystem that enables the creation of graphical, mobile and printed reports using SQL Server and other data sources. SQL Server is a relational database management system ( RDBMS ) that supports transaction processing, business intelligence and analytics applications.

SSRS allows businesses to provide decision making information to end users in a variety of formats based on information stored in SQL Server and other data sources. SSRS has been delivered as a part of the SQL Server Standard and Enterprise editions since the SQL Server 2005 release. 

SSRS is used by both end users and database professionals. Reports can be created by database professionals using the Report Designer in SQL Server Data Tools (SSDT) as well as by end-users with the Report Builder tool. End users consume the SSRS reports and select end users can create reports. Database professionals also manage subscriptions and the SSRS Report Server.

Features of SQL Server Reporting Services

SSRS can connect to multiple data sources including SQL Server’s relational database and SQL Server Analysis Services as well as any other ADO.NET compatible data source to produce graphical, mobile and printed reports in a variety of formats including HTML, Excel, PDF, CSV, XML and TIFF. SSRS supports the ability to drill down into the data details for select report lines. Parameterized reports allow users to run the same reports with different data. For instance, a report user might select to rerun a report with different customers, products or date ranges. Users can create subscriptions to automatically run and route reports to select users. SSRS reports can be integrated with Power BI .

How SQL Server Reporting Services work

SSRS consists of four main components: The Reporting Services service, the Web Portal, the Report Designer and the Report Builder. Reports are created using the Report Designer or the Report Builder. The Report Designer is a more robust and complicated tool that is designed for data professionals. It can be run from SSDT or as an add-on to Visual Studio 2019. The Report Builder is a more straightforward tool designed to enable end-users to create reports. These SSRS report creation tools generate Report Definition Language (RDL) files that control how the reports will look and act. The Reporting Services service takes the RDL files as input and uses them to render the SSRS reports. The Web Portal takes the place of the older Report Manager and it is used to manage, secure and run SSRS reports.

Continue Reading About SQL Server Reporting Services (SSRS)

Related Terms

Dig deeper on database management.

report service in sql

Retool mobile, a shiny spanner for native iOS & Android apps


Compare Azure Data Factory vs. SSIS


A cloud services cheat sheet for AWS, Azure and Google Cloud


The top augmented analytics tools vendors are offering


The self-service analytics specialist's platform has greatly increased the healthcare organization's efficiency after its old BI ...

The vendor's first 2023 update makes Data Stories available in Server after it was initially launched only in Cloud. In addition,...

The latest version of the vendor's BI suite includes action logs that enable admins to see how end users are using a tool that ...

Many organizations struggle to manage their vast collection of AWS accounts, but Control Tower can help. The service automates ...

There are several important variables within the Amazon EKS pricing model. Dig into the numbers to ensure you deploy the service ...

AWS users face a choice when deploying Kubernetes: run it themselves on EC2 or let Amazon do the heavy lifting with EKS. See ...

File discovery is one of the most popular use cases for SharePoint Syntex. But how else can you use it? Here, see how real-world ...

DAM systems offer a central repository for rich media assets and enhance collaboration within marketing teams. However, users may...

SharePoint Syntex is Microsoft's foray into the increasingly popular market of content AI services. This introduction explores ...

With its Cerner acquisition, Oracle sets its sights on creating a national, anonymized patient database -- a road filled with ...

Oracle plans to acquire Cerner in a deal valued at about $30B. The second-largest EHR vendor in the U.S. could inject new life ...

The Supreme Court ruled 6-2 that Java APIs used in Android phones are not subject to American copyright law, ending a ...

SAP's sale of Qualtrics reaches its final stage as it sells shares for $7.7 billion. The deal recoups the initial investment but ...

A new service on SAP Business Technology Platform is aimed at helping companies get value from SAP and non-SAP data by keeping ...

When its ERP system became outdated, Pandora chose S/4HANA Cloud for its business process transformation. The new system is ...

report service in sql

08 April 2019


Printer friendly version

SQL Server Reporting Services has changed quite a bit since it was introduced in 2004. Despite new analytic services like Power BI, SSRS is still a popular tool for paginated reports. In this article, Kathi Kellenberger explains the architecture of SSRS and walks you through installing an SSRS development environment on your workstation or laptop.

The series so far:

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows you to create and manage a wide variety of different types of reports and deliver them in a range of formats. You can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. You can even create dashboards that will run on your phone! Reports can draw their data from SQL Server databases, but also from other relational database such as Oracle or Azure SQL Database, and other types of data sources such as Analysis Services.

You can present your finished reports from the Reporting Services website typically running on premises, called the Web Portal, or users can view them within web- or Windows-based applications. Reports can also be viewed in SharePoint with a special webpart. (In earlier versions of SSRS, there was also a SharePoint integrated mode.) End users can run reports on demand or schedule them as subscriptions. This article is the first in a series that will provide in-depth coverage of the basics of report development with SSRS.

The report development examples in this series will work for SSRS 2017 and later. Many of the examples will also work for earlier versions. If you are still using SSRS 2005, please refer to Steve Joubert’s original SSRS series . If you are working with 2008 to 2012, look at the series I wrote a few years ago.

SSRS Through the Ages

SSRS has been around since 2004, first shipping as an “add-on” for SQL Server 2000, and then fully integrated into SQL Server 2005. I first saw it demonstrated at the 2003 PASS Summit and had the Beta version installed and running at work as soon as I could get my hands on the media. Back in 2003, the SQL Server community were very excited about SSRS as it made available, for the first time, built-in reporting capabilities for SQL Server. If you owned SQL Server, you owned SSRS.

Since its integration into SQL Server 2005, Microsoft has made a number of improvements over the years and gave it a complete overhaul in 2016. One big change in 2016 was the renaming of the website from Report Manager to Web Portal. In 2017, SharePoint integrated mode was discontinued, but a special version for hosting Power BI dashboards called Power BI Reporting Services became available.

SSRS Architecture

An SSRS deployment must be associated with a SQL Server instance. On the instance will be two databases, named by default:

ReportServer – contains the report definitions, configuration, history, security of deployed reports and more

ReportServerTempdb – much like tempdb, it is used as a workspace for building reports and doesn’t maintain any objects permanently.

You will also need a location for the Report Server Web Service , which can be on the same server as the databases, as in the simple deployment architecture shown in Figure 1, or on a different server. On whichever server you choose, you will have access to the Web Portal that allows you to deploy and manage the reports. The data sources will typically be found on other servers throughout the network. End users can run reports from the Web Portal, create subscriptions, and publish their own reports if they have permission.


Figure 1: A simple SSRS deployment

The end user sends an HTTP request for a report, providing any required parameters. The SSRS server finds the metadata of the report and sends a request for data to the data sources. The data returned by the data sources is merged with the report definition into a report. As the report is generated, it is returned to the client. There are two layers of security to get past before viewing the report.

SSRS Security

You have many options for securing the reports. The first layer of security is built into the Web Portal. You can control access to folders and individual reports based on network groups or user accounts. The second layer of security is to the database. You can either base the security on the individuals running the report or store an account within the data source that can be passed on to the database system. Security will be covered in more detail in a later article on deploying reports.

Tools for Building SSRS Reports

SSRS reports are XML files with the extension RDL. If you were a robot instead of a human, you might be able to just create the RDL files with a text editor. Instead, there are a number of tools from Microsoft for building reports that can be hosted in the SSRS Web Portal depending on the type of report and who is building the report.

Traditional SSRS reports, which also might be called paginated reports, can be build using an extension for Visual Studio (VS) or Report Builder. Developers will be more comfortable working in the VS environment. VS is based on a solution/project model and integrates with version control software such as Azure DevOps formerly known as Visual Studio Team Services. This is the tool you will use for this series of articles.

Report Builder is meant more for power users to create their own reports, but developers in some shops use this tool in place of VS. Instead of a project model, each report is independently created. One advantage of Report Builder is that it has an Office look and feel which makes it more comfortable for non-developers to use. It also has more wizards to help beginners get started. Users of Report Builder can take advantage of published report parts to make building a dashboard simple. Figure 2 shows what Report Builder looks like when it’s launched:

report service in sql

Figure 2: Report Builder

A new type of report available with SSRS 2016 is called Mobile Reports. Mobile Reports must be created with the Mobile Report Publisher which is launched from the Web Portal. These reports are dashboards which can run on phones and tablets as well as the Web Portal. (If you are a previous developer or user of Datazen reports, these will look familiar to you as Microsoft acquired the company and brought the technology into SSRS). Figure 3 shows what the Mobile Report Publisher looks like:

report service in sql

Figure 3: Mobile Report Publisher

Power BI dashboards can also run in a special type of SSRS Web Portal called Power BI Report Server. The tool for creating Power BI dashboards is called Power BI Desktop. Power BI is out of scope for this series, but if you would like to learn more about this topic, take a look at the article series written by Robert Sheldon.

Installing and Configuring SQL Server and Reporting Services

Since this series is focused on report development, it will cover just enough about installation and configuration to help you get things set up on your development computer. This section is not intended to provide information for installing SQL Server or SSRS in a production environment. 

To follow along with this series of articles, you will need to install the following:

You should be running a Windows 10 computer, making sure that there is at least 50 GB free on the hard drive. You will also need to restore the AdventureWorks sample database to follow along with the examples.

At the time of this writing, SQL Server 2019 is available as a Community Technology Preview (CTP), which is a fancy way of saying Beta version. The following instructions are based on SQL Server 2017, the latest generally available version. One big change made in the installation of SSRS in 2017 is that the SSRS media must be downloaded and installed separately.

SQL Server Instance Installation

If you already have a SQL Server instance in place, version 2008 or later, you can skip this section. The instance is used to host the two databases used by SSRS and the sample databases used in the examples.

To install the SQL Server instance, you will need to first download the media. I will not provide a link here, because invariably it will change, so just search for “SQL Server 2017 downloads.” The page that was current as of this writing had links for a free trial and for a Developer Edition shown in Figure 4. Choose the Developer edition and save the downloaded file.

report service in sql

Figure 4: The SQL Server media download page

I’m not going to show you every step of installing SQL Server because that information can be found elsewhere. Here are the important things to note:

report service in sql

Figure 5: Launch the installation

Make sure to choose the Developer Edition shown in Figure 6.

report service in sql

Figure 6: The Developer edition

The only instance feature you will need for learning SSRS is the Database Engine Services shown in Figure 7.

report service in sql

Figure 7: Features

To make things simpler, just install the Default instance shown in Figure 8. If you have already installed an instance, you’ll see it listed. If that’s the case, you may want to just cancel out of the wizard at this point and use the previously installed instance.

report service in sql

Figure 8: Install the Default instance

Be sure to click Add current user to make your account an administrator. You may also want to set the security to Mixed mode . Figure 9 shows these options.

report service in sql

Figure 9: Security settings

Review the summary shown in Figure 10 and go make yourself a cup or coffee or tea after clicking Install because it could take 10 or 20 minutes for the installation to run.

report service in sql

Figure 10: The Summary

Figure 11 shows that the installation was a success!

report service in sql

Figure 11: Success!

SSMS Installation

SQL Server Management Studio (SSMS) is the tool you will use to connect to the SQL Server instance to restore the sample databases and run queries. If you already have this tool in place, you can skip to the next section.

Microsoft is now releasing new versions of SSMS on a frequent basis, so it is no longer installed during the database engine installation. You can search for “SSMS download” to find the latest version. You can also find a link on the Installation page of the SQL Server 2017 Installation Center, if you used this to install your instance. You can launch it again from the Windows Start menu. Figure 12 shows where you can find the link.

report service in sql

Figure 12: Launching the download page for SSMS

Once you have downloaded the media for SSMS, just run the installation wizard accepting the defaults.

SSRS Service Installation and Configuration

Previous versions of SQL Server allowed you to include SSRS during the installation of the database engine, but that is no longer the case beginning with 2017. Installing them together was quite convenient since the installation took care of the SSRS configuration for you automatically. Now, you must download the media separately and configure SSRS yourself. It’s not difficult, but I’ll walk you through the steps.

Begin by searching for “Download SSRS” or you can also find a link to the download page from the SQL Server 2017 Installation Center shown in Figure 13.

report service in sql

Figure 13: A link to the download page for SSRS

Save and run the downloaded file. To get the installation started, click Install Reporting Services shown in Figure 14.

report service in sql

Figure 14: Install Reporting Services

Click through the installation wizard. The most important question for you to answer is to specify the edition. Make sure that you select the Developer edition.

Once the installation is complete, you’ll be asked to configure the report server. If you are required to restart, do that first. After you restart, you will need to launch the Report Server Configuration Manager. You can do this from the item found in the Windows Start menu or from the final page of the SSRS installation shown in Figure 15 if you didn’t have to restart.

report service in sql

Figure 15: The final page of the SSRS installation wizard

Either way you launch Report Server Configuration Manager, you will need to connect as shown in Figure 16.

report service in sql

Figure 16: Connect to SSRS

There are three items that must be set up when configuring SSRS just to get it running:

Click Database on the left menu. Click Change Database shown in Figure 17.

report service in sql

Figure 17: Click Change Database

This will launch the Report Server Database Configuration Wizard . Leave Create a new report server database selected and click Next as shown in Figure 18.

report service in sql

Figure 18: Create a new database

On the following screen, make sure that the local computer name is filled in. If you are using a named instance, then the Server Name will be computer\instance . If you’re not sure, take a look at the “Connecting to Your SQL Server Instance” section later in this article. Leave the Authentication type set at Current User – Integrated Security and click Next as shown in Figure 19.

report service in sql

Figure 19: The server name

On the next page, you will specify the SSRS database name. The default database name is ReportServer . Figure 20 shows that you can leave it at the default and click Next .

report service in sql

Figure 20: The database name

Leave the Authentication Type set at Service Credentials and click Next as shown in Figure 21.

report service in sql

Figure 21: The authentication type

That’s the last question you’ll be asked about the database. Complete the wizard to create the database. Once the database is in place, click Web Service URL .

Leave the defaults in place and click Apply as shown in Figure 22.

report service in sql

Figure 22: The Web Service URL

Click Web Portal URL . Again, leave the defaults in place and click Apply as shown in Figure 23.

report service in sql

Figure 23: The Web Portal URL

Make a note of the URL. This is what you’ll use to connect to the Web Portal after deploying reports. Once done, exit out of the Report Server Configuration Manager. Now that the SSRS service is in place, the next step is installing the development tool.

Installing in Visual Studio

NOTE: At this time, you can install in one step with VS 2017 using SSDT. If you have VS 2019 installed, add the SSRS extension. The instructions in this section cover the 2017 instructions.

You’ll use the SSRS extension for VS to develop reports, and this is the last item to install. You can search for “SSDT Download” or launch the page from the SQL Server 2017 Installation Center shown in Figure 24.

report service in sql

Figure 24: The link to the SSDT download

You will need to scroll down the web page to find the link for SSDT standalone installer as shown in Figure 25.

report service in sql

Figure 25: The SSDT installer

Once downloaded, run the file. Make sure that SQL Server Reporting Services is selected and click Install as shown in Figure 26.

report service in sql

Figure 26: Install SSDT

Accept any other defaults and complete the installation.

Connecting to Your SQL Server Instance

You may have trouble connecting to your local SQL Server instance for a couple of reasons. First, you may not know the actual server name needed to connect to it, especially if you have installed a named instance. The other problem may be that it’s just not running. I’ve seen that happen quite often with laptops.

To solve both problems, launch SQL Server Configuration Manager . You may find it in the SQL Server programs section of the Windows Start menu. If not, take a look at this post for more information. With this tool, you can see the instance name as well as start up an instance that isn’t running. Click SQL Server Services and take a look at the items on the right as shown in Figure 27.

report service in sql

Figure 27: The SQL Server Configuration Manager

Default instances are called SQL Server (MSQLSERVER) . When connecting to the default instance locally, you just need the computer name or an equivalent:

If you see something else in parentheses after the words SQL Server, that’s a named instance. To connect to a named instance, you’ll need the computer name followed by a backslash and the instance name. Here are some examples assuming that my computer is called MyComputerName :

Notice in the previous image that the default instance is stopped. Just right-click the instance and choose Start to get it running.

Now that you know the name of your SQL Server and have ensured that it’s running, launch SSMS and connect to it as shown in Figure 28.

report service in sql

Figure 28: Connect to the SQL Server

You’ll see the SQL Server and several folders in the Object Explorer window shown in Figure 29.

report service in sql

Figure 29: The Object Explorer

Restoring a Sample Database

To follow along with the examples in this series of articles, you’ll need to restore one or more sample databases. The main database that will be used for the examples is called AdventureWorks2017 . At the time of this writing, Microsoft is hosting the sample databases on GitHub, a well-known software repository site. Search for the AdventureWork2017.bak file. A bak file is a backup file, and that’s what you need. Figure 30 shows the download page.

report service in sql

Figure 30: Download AdventureWorks

Do not download the file to your Desktop , Documents , or Downloads file. Since SQL Server is running under an identity that is not you, it can’t see files in those locations. I recommend just downloading to a file in your C:\ drive such as C:\Temp .

Follow these steps to restore the database:

Connect to the SQL Server instance by launching SSMS and connecting to it. If you have trouble, review the “Connecting to Your SQL Server Instance” section.

Right-click on the Databases folder and select Restore Database… as shown in Figure 31.

report service in sql

Figure 31: Choose Restore Database

Select Device and click the ellipses as shown in Figure 32.

report service in sql

Figure 32: Click the elipses.

This brings up the Select backup devices window. Click Add shown in Figure 33.

report service in sql

Figure 33. Click Add

Navigate to the folder where the bak file is stored as shown in Figure 34.

report service in sql

Figure 34: Navigate to the backup file

Select the file and click OK . You’ll click OK two more times to fire off the restore.

Once the database has been restored, you’ll see the message shown in Figure 35. Click OK two more times to dismiss the dialogs.

report service in sql

Figure 35: A successful restore

If you have followed the instructions in this article, you now have a development environment for SSRS set up on your workstation or laptop. In the next article, I’ll show you how to create some basic reports.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Subscribe to our fortnightly newsletter

Rate this article

report service in sql

Kathi Kellenberger

Kathi Kellenberger is a Customer Success Engineer at Redgate and a Microsoft Data Platform MVP. She has worked with SQL Server for over 20 years and has authored, co-authored, or tech edited more than 20 technical books. Kathi is a volunteer at LaunchCode, the St. Louis based organization providing free training and paid apprenticeships in technology. When Kathi isn’t working she enjoys spending time with family and friends, cycling, singing, and climbing the stairs of tall buildings. Be sure to check out her courses on Pluralsight .

Follow Kathi on

View all articles by Kathi Kellenberger

Load comments

Related articles

report service in sql

Datamarts and exploratory analysis using Power BI

ChristianSteven | Analytics & BI | Power BI | SSRS | Crystal Reports

SSRS Reports, Tools, Tutorials & Automation

Send UNLIMITED SSRS reports to UNLIMITED users with ONLY ONE license.

Get Features PDF Start Free Trial

What is SSRS and what does it stand for?

SQL Server Reporting Services ( SSRS ) is a server-based report generating software system made by Microsoft and used as a solution for companies who need to build custom reports from a variety of data sources, such as SQL databases and other external sources, that gives administrators the ability to share reports to users based on access permissions and user groups.

What are Data-Driven Subscriptions in SSRS?

From :

A data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined.

To put it simply, a data-driven subscription in SSRS is a scheduled report that automatically pulls data from external sources and compiles the data based on the parameters defined by the administrator.

For example, large organizations typically use data-driven subscriptions to distribute reports to a list of vendors, employees, stakeholders, customers and subscribers.

Moving data back and forth through an organization where systems vary and the dataset is large can very often be extremely complicated and time-consuming at best. SSRS helps by connecting these external data sources and compiling the required reports into an easily readable format that delivers the right information to the right people based on user permission and group access.

What is SSRS used for?

SSRS can be used to prepare and deliver various interactive and printed reports. With SQL reporting services combined with SQL Server, firms have a unified solution for both report design and data warehousing. SSRS can help you to create tabular, graphical and free-form reports from relational, multidimensional and XML based data sources. The reports can also be published and accessed on demand. SSRS also has a built in scheduling tool to perform basic report deployments via email, file share, or SharePoint.

SSRS can be used in different ways and with other systems to deploy business reports . SQL reporting services can be used with business process management tools such as PBRS to automate SSRS reports, move data across various databases, and even drive tasks based on events.

The Payables Management department can use SQL server reporting services to generate vendor summary analysis, vendor cash requirements reports and all transaction history details. The Payroll unit may use it for generating and updating employee wage and work hours report, pay history reports and for maintaining earnings register. For the manufacturing units, SQL Reporting Services manages Picking Reports and item standard costs changes history.

Types of SSRS Reports

SSRS SQL Reporting Services

There are many useful types of SSRS reports, each with its own unique way of pulling and displaying data. While an SSRS report can have functionality and characteristics from more than one category, below is the main SSRS report types:

Snapshot reports in SSRS are predefined reports built with the purpose of delivering a “snapshot” of the current dataset that is being tracked using SSRS.

These types of SSRS reports are useful for sales orders, pipelines, inventory or any other dataset that an organization would need to track. A snapshot report in SSRS represents data retrieved at a specific point in time. SSRS allows administrators to create on demand or scheduled reports, which are then stored on the server for easy access. This reduces server load by eliminating the need for multiple users to run the same reports — a snapshot report can be created on a set schedule and users can easily access the report snapshot anytime. Snapshot reports serve three main purposes:

Accurate historical data.  With snapshot reports you can build out a timeline of historical data that allows you to recognize trends and see how data changes over time.

Data consistency across the organization.  If multiple users are running on-demand or ad hoc reports when the needs arise, there is a very real chance that different users can be looking at different data, making organizational decision-making difficult and prone to error. With report snapshots, each user receives the same report on a predetermined schedule. This allows for apple to apple comparison because the data is consistently pulled from the same point in time.

Server performance.  While most modern servers can handle real-time processing of information, especially as more organizations have adopted the cloud, the reality is that reducing server requests leads to better performance. SSRS allows administrators to process large reports during off-peak hours and save these reports as snapshot reports, so they can be easily accessed by users anytime.

Drilldown reports in SSRS is a report that allows users to display summary information and then “drilldown” into more detailed information, usually by clicking on a plus or minus icon. This is useful when needing to look at data on a macro view in order to recognize patterns or trends, and then dig further into the data where necessary.

A parameterized report in SSRS uses data inputs set by the user at runtime. These types of reports are frequently used for filtering reports with related data.

Drillthrough reports in SSRS is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, in this illustration, the sales summary report lists sales orders and totals. When a user clicks an order number in the summary list, another report opens that contains details about the order.

An ad hoc report in SSRS is a report that is created by a user as the need arises. Ad hoc reporting is a self-serve reporting function that empowers users to ask their own questions and drill down into data themselves to find answers. In SSRS, you would create an ad hoc report using the SSRS Report Builder.

A linked report in SSRS is a report server item that provides an access point to an existing report, and retains the original report’s definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

A paginated report in SSRS refers to the number of pages within a report and how report items are arranged on these pages. Pagination in Reporting Services varies depending on the rendering extension you use to view and deliver the report.

A cached report in SSRS is a report that is run and stored to be retrieved by users at a later time. The purpose of this is to decrease server load and increase performance by preventing multiple users from running the same processes. Instead, multiple users are able to access the same report.

A clickthrough report in SSRS is a report that provides detailed information about the data contained within the main report. A clickthrough report is displayed when the user clicks the interactive data that appears in the main report. These reports are automatically generated by the report server.

A subreport in SSRS is a report that is used to embed one report within another, usually in the same folder of the parent report. Any report can be used as a subreport.

Benefits of Reporting in SSRS

There are seven main benefits of SSRS for reporting:

Easy access to report formats:  The reports can be accessed easily from within Microsoft Dynamics GP. There is also a one click access from the user’s personalized ‘My Reports’ list of frequently used formats. In terms of generating the report, SSRS supports a variety of formats including PDF or Excel. Additional formats can be used via plugins or an external tool such as PBRS .

Drill down action:  The user can quickly access essential information with the ability to drill down within the reports.

Chart options:  SQL reporting services offer various report layout options including pie chart, line chart and bar chart capabilities. These can be used to highlight key information and enhance business presentations.

Customized Filtering:  With SQL server reporting services the users can filter report data using dynamic parameters.

Flexible report views:  SQL reporting services allow for a collapsible report view to expand sections, reducing complex reports to manageable proportions.

Sub-reports:  Users can create sub reports with a main report and also the main report to one or more sub reports through a set of parameters.

Table View:  This report layout option quickly presents the data in a table format for better viewing and report distribution across different units of a business.

Many will find that SSRS has a bit steeper of a learning curve, especially for users that transitioning from  Crystal Reports . However the benefits of a unified data warehouse and reporting solution add a greater amount of flexibility. Eventually you will have the need to expand SSRS’ feature set by relying on plugins and external tools to add more report formats, distribution methods, and advanced scheduling. If you are beginning to transition to  SQL Server as your reporting system , with a bit of learning, you will not be disappointed!

Drawbacks of Reporting in SSRS

Steep learning curve that requires users to know SQL and SSRS-specific functions.  Wi thout a highly technical person on your team or an IT department, using SSRS to its full potential will be largely out of reach.

Somewhat limited ability to display data graphically. Although, Microsoft has recently updated this in the latest release and is now incorporating SSRS into Power BI in order to marry the interactive graphical display of Power BI reports with the paginated reports found in SSRS.

SSRS means you have another server to maintain.  Microsoft SSRS requires a Windows server hosting environment, which adds to the complexity of IT support.  

Limited automation or mass reporting features.  SSRS was built more as a self-service reporting tool — not a report automation tool. While the Enterprise edition  does  have mass reporting capabilities, the feature set is small in comparison to  what other tools are able to accomplish with SSRS .

For example, even with the Enterprise addition, there are limitations:

These limitations dramatically change how easily you can distribute reports across your organization. For example, what if you needed to send reports based on specific events e.g. database record has changed, new order has been created, unread email has been received, and have these reports sent directly to Printer, DropBox, Fax, Slack and FTP? What if you need to send certain reports to a specific set of users via  Google Sheets? What if you need to email 50 Excel based reports, but need the Workbooks combined instead of emailed separately? This cannot be done, even in Enterprise.

And these features do not even exist in Standard.

Fortunately, PBRS - ChristianSteven's tool for automating Power BI & SSRS reports - can accomplish all this and more, even with a Standard SSRS license. For a full list of features and to download your free 30-day trial:

Costs increase with number of users.  While SSRS is a great product and does a great job streamlining data across organizations, the biggest reality is the enormous investment it requires to realize its full potential.

Microsoft uses  two pricing models  for SSRS: Standard and Enterprise.

SSRS Pricelist on Microsoft's website

As you can see, the listed price for Enterprise edition starts at $14,256 — but is this the actual price you pay?

If you drill into the fine print, you’ll see that the costs are actually quite a bit more.

report service in sql

As the fine print states, “Editions sold in the per-core licensing model are sold as 2 core packs.” Your $14,256 investment is actually a minimum of $28,512  per year  in licensing costs — assuming your server has only 2 cores.

If you refer to  Microsoft’s SQL Server 2017 licensing guide , you’ll see that installing on a server will more than likely require at least four licenses.

report service in sql

This brings your yearly costs from $28,512 to $57,024.

Of course, you can skip these costs and opt for the per user license, but keep in mind that the Standard Edition is missing nearly all of the features that allow some level of automation and completely eliminates data-driven subscriptions.

How to Automate Report Distribution in SSRS

Do not let the costs from persuading you from seeing SSRS as a worthwhile reporting tool for your organizational needs. Its ability to connect disparate data sources and effectively compile and send data to the right users at the right time give it enormous value to CEOs and stakeholders with a need to have absolute control over the dissemination of data across their company.

There is a straightforward solution to minimizing the costs and dramatically expanding the features and functionality of SSRS, even if your organization only runs SSRS Standard Edition. If your organization already uses Enterprise, you can still easily expand the functionality to every user in your organization.

HubSpot Video

PBRS by ChristianSteven Software  is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® SQL Server Reporting Services (SSRS) Reports to unlimited users.

The need to deliver reports in the best format for your audience is critical to streamlining data consumption and putting the right information in front of the right people at the right time. Organizations can quickly realize the benefits of PBRS by automating repetitive reporting tasks — allowing administrators to create and distribute reports without error and without the costly support time.

PBRS is able to send scheduled reports by:

The available formats include Excel, Word, PDF, Txt, CSV, PNG and more.

PBRS is the leader in SSRS automation and report delivery, containing powerful system, event triggered, data-driven and business process workflow functions which will make an instant impact on the efficiency of your business.

Over 1,000 companies in more than 46 countries trust ChristianSteven Software for their report automation needs. Download your free trial of PBRS today.

Start Your PBRS Journey Today!

Get your free 'software features' companion pdf for pbrs.

Take advantage of this 100% free, extensive PDF to help you plan your Power BI & SSRS distribution solution!

About ChristianSteven

With over 1,000 clients in 47 countries, we are committed to a culture where we put people first – our customers, our employees & our partners. We specialize in Data Analytics, Business Intelligence, reporting, report distribution, report scheduling, dashboards & automating business processes.

From scheduled BI report delivery to browser-based Data Analytics & mobile-enabled dashboards, the magic sauce is in our proprietary business process automation experience & know-how that leverages business rules, workflows & instant notification capabilities built right into our Business Intelligence, Data Analytics & report distribution solutions.

Quick Links

Contact details.


  1. SQL Server backup report

    report service in sql

  2. Microsoft reporting services tutorial video

    report service in sql

  3. SQL Server Report Builder

    report service in sql

  4. SQL Server Reporting Services Best Practices

    report service in sql

  5. What's new in SQL Server 2008 Reporting Services?

    report service in sql

  6. SQL Server Reporting Service Configuration Manager

    report service in sql


  1. Publishing SQL Server 2008 R2 Reporting Services Reports

  2. Working with Report Parameters

  3. How to Install SQL Server Reporting Services(SSRS)

  4. The missing part if you use SSRS Report Builder with MySQL, you should go MS SQL

  5. SSRS

  6. Twitter users report service interruptions


  1. SQL Server Reporting Services (SSRS)

    SQL Server Reporting Services (SSRS) provides a set of on-premises tools and services that create, deploy, and manage paginated reports.

  2. Reporting Services Tools

    SQL Server Reporting Services contains a set of graphical and scripting tools that support the development and use of rich reports in a

  3. SSRS Tutorial: What is SQL Server Reporting Services?

    SSRS stands for SQL Server Reporting Services is a reporting software that allows you to produce formatted reports with tables in the form

  4. SQL Server Reporting Services

    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

  5. SQL Server Reporting Services (SSRS) Tutorial for Beginners

    This is Part 2 of a 2 part step by step tutorial oh how to create reports using SQL Server Reporting Services (SSRS).

  6. SSRS 101: The Basics of SQL Server Reporting Services | Webinar Recording from 8/30/2018.David Gigear of Crestwood walks you through a live demo of everything you need

  7. SQL Server Reporting Services (SSRS)

    SQL Server Reporting Services (SSRS) is a SQL Server subsystem that enables the creation of graphical, mobile and printed reports using SQL Server and other

  8. Reporting Services Basics: Overview and Installation

    SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows you to create and manage a wide variety of different types

  9. To Locate the SQL Reporting Server

    From the Start menu, navigate to Microsoft SQL Server 2012 > Configuration Tools > Reporting Services Configuration Manager. · In the Reporting Services

  10. SSRS Reports, Tools, Tutorials & Automation

    What is SSRS? SQL Server Reporting Services (SSRS Reports) is report generating software system from Microsoft. It is part of MS SQL Server services.