Overview of SQL Server Reporting Services (SSRS)

In the previous articles, we have discussed the following:

Overview of SQL Server Integration Services (SSIS)

Creating your first SSIS package with the help of Import Export Wizard

Overview of SQL Server Analysis Services (SSAS)

Creating a Cube and Dimension using SSAS

In this article we will discuss an overview and need for SSRS and various kinds of report delivery. In the next article, we will discuss how to create a report and deploy it.

We have discussed how organizations maintain data in various forms, how the data can be fetched and placed at a uniform platform. We also discussed 2 different ways of storing data, for transactional requirement (OLTP – Online Transactional Processing) and for historical usage (DW - Data Warehouse).

We need to now present this data in a user friendly format so that business decisions can be taken based on it. The business decisions example can be - what may be the planned percent of business growth? or which business units to focus on for expansion? and so on. For making these decisions it is better to base the decisions on some reports, rather than making abstract decisions without any data support. There can two kinds of reports that are required by business. One of them is trend analysis, in which trends in the business transactions can be displayed. The trend can be in the form of - how different customers have given business and which customers are giving more business. The trend can provide us output in the form of list of customers which have given us more business. The management can concentrate on this list to increase business. The other form of report is a status or snapshot report. There can be snapshot of inventory of material with ABC analysis for items in the organization depending upon the fast, medium and slow moving items. It can be in the form of pricing of items, high priced, moderate priced and low priced. The snapshot can comprise of current status of organization regarding the employees working, getting retired towards year end.

In order to create these kinds of reports we require processed and organized data. It can come in the form of transactional data or OLAP (Online analytical processing) data. We have discussed how to fetch data on a common platform with the help of SQL Server Integration Services and how the data can be aggregated and made available for querying with the help of SQL Server Analysis Services.

Let us see how the reports can be created with the help of SQL Server Reporting Services (SSRS). This tool is one of the major tools for report creation provided by Microsoft. The other tools are Report Builder 1.0 and Report Builder 2.0. Check this article to find out some differences between the three.

With SSRS, the data related to reports is stored in 2 databases namely ReportServerDB and ReportServerTempDB. Item like reports, linked reports, shared data sources, report models subscriptions and schedules, report snapshots which are managed by report server are stored in Report Server database. Session and execution data are stored with temporary database.

Report Server architecture provides 2 main features namely Report Manager and Report Server Web Service. Report Manager provides web interface to the report server web service. Report Manager runs in the browser on client side. Nothing is stored on client side like files or settings. Any setting to a particular user is stored in database and retrieved when required. You can associate URL for Report Manager. Report Server Web service is the main feature which provided all reports and report models. The user authentication and authorization is handled by web service for any report processing.

There can be different delivery kinds of reports, pull delivery, push delivery and local processing. In pull delivery the URL for the report is given to view the report. In push delivery a subscription for the reports is created and the report gets automatically delivered to the concerned person or in a shared folder when the criterion for subscription matches. In local processing the report can be viewed on client with the help of report viewer with windows form or web form.

In this article, we took a quick overview of the SSRS and the various kinds of report delivery. In the next article, we will create a report based on the cube created in the last article Creating a Cube and Dimension using SSAS