SSRS – SQL Server Reporting Services

Many are already familiar with SSRS, know what it is and how to use them, but also many do not know this MS SQL server component, so now let’s get acquainted with SQL Server Reporting Services, SSRS for short, and learn what features these services offer us.

In each organization there is a necessity to form reports on the basis of some data and means for it are used different, sometimes even it is done manually (for example, in Word or in Excel). Microsoft company offers the excellent, complex decision of construction of reports which is included into the complete set MS SQL Server Reporting Services and now we learn, what is SSRS?

Note! As an example of SQL Server Reporting Services, I will consider and describe the features on the example of MS SQL Server 2008 R2.

What is SSRS?

SQL Server Reporting Services (SSRS) are services for designing, building, delivering and viewing reports. You can use these services to create tabular, interactive, graphical, and other more complex reports using charts and other reporting elements.

SSRS is implemented as a web service and its administration, as well as report management is done through the web interface. Standard access to reports is also provided through the web interface. SSRS provides an opportunity to integrate developed reports into third-party applications, i.e. some API functionality is available. For example, SSRS can be integrated with SharePoint.

Since SSRS is a web service, it requires something like a web server, previously in MS SQL 2005 required a web server IIS, starting with MS SQL 2008, IIS is not required, because this functionality is already built into SSRS. In this regard, it is not recommended to use SSRS 2008 versions and higher on the same server with IIS (personally, I recommend not to deploy SSRS on the machine on which any web server is installed).

An SQL Server database is required for the report server to be able to store published reports, report models and folder hierarchy.

In SSRS, you can manage access rights to reports, i.e. one group (or a specific user) can be granted access rights to view a report, while another group cannot.

Reporting Services supports report caching, i.e. you have a report that runs for a long time and the data in it rarely changes, and you can cache the report to make it run faster.

SSRS provides the possibility to subscribe to published reports, i.e. for example, by schedule the user will receive the latest reports by mail or in a shared folder.

All reports created with the help of Reporting Services can be exported to different formats (preserving the visual design), for example:

• Excel;
• Word;
• PDF;
• CSV;
• XML;
• TIFF;
• MHTML (Web Archive).

Sources of data for SSRS reports

Reporting Services supports the following types of data sources for reports:

• Microsoft SQL Server;
• Microsoft SQL Azure;
• Microsoft SQL Server Analysis Services (multi-database support);
• OLE DB;
• ODBC – dBase, Excel, Access (MDB) and other files are supported accordingly;
• Microsoft parallel data storages;
• XML;
• Oracle;
• Teradata;
• Hyperion Essbase;
• Microsoft SharePoint list;
• Sap NetWeaver BI;
• Report Server Model.

Reporting Services Components and Reporting Development Tools

• Report Server – provides data and report processing and report delivery, it is a core component of Reporting Services.
• Report Manager is a web interface designed for viewing and managing reports. It is Report Manager that users use as their standard report viewer.
• Report Designer is a powerful tool for creating reports. The Report Designer works in the Business Intelligence Development Studio (BIDS) environment.
• Reporting Services Configuration Tool is a tool for configuring the Reporting Server and Report Manager, i.e. it is here that you can change their settings, such as setting up a Reporting Server Service account, specifying the URLs of the Reporting Server and Report Manager, creating or selecting a database, setting up e-mail settings, and more.
• Report Server Command Prompt Utilities – SSRS includes three command line programs with which you can administer the report server.

1. rs – A program to process scripts that can be used to automate administrative tasks;
2. rsconfig – serves to configure and manage the connection between the report server and the report server database;
3. rskeymgmt – a tool for managing encryption keys.

• Report Builder 3.0 is a reporting program with an interface similar to that of Microsoft Office. It is designed for business users who are used to working with Microsoft Office applications. It is a kind of simplified version of the Business Intelligence Development Studio constructor.

Now I think you have an idea of what SQL Server Reporting Services (SSRS) is and to sum it up, it is a very powerful, and most importantly, a comprehensive reporting solution. And finally, let us remember and highlight the main features of SSRS:

• Creating both simple tabular reports and complex interactive ones;
• Exporting reports to various popular formats (Excel, Word, PDF, etc.);
• Support of various data sources for reports (SQL Server, SQL Azure, OLE DB, ODBC, Oracle, etc.);
• Powerful Business Intelligence Development Studio constructor;
• Subscribe to reports;
• Report caching;
• Integration with SharePoint;
• API interface for integration with other third-party applications.

For theory, I think it is enough in the following materials we will cover installation and configuration of SQL Server Reporting Services, and learn how to create reports and publish them. That’s it, good luck!

SQL Server Reporting Services (SSRS) Tutorial