Overview
- SQL Server Reporting Services, which is popularly known as SSRS.
- Introduced with SQL Server 2000. SSRS is an enterprise-wide centrally managed Reporting Solution.
- It’s a Server side reporting platform and comes with tools for creating, managing, and viewing reports.
- It offers web-enabled report access and administration functionality.
- Publish reports in various formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
- The data for reports can come from relational or multidimensional data from SQL Server, Analysis Services, Oracle, XML data sources, or any Microsoft .NET data provider, such as ODBC or OLE DB.
- SQL Server Reporting Services provides a middle-tier server that runs under Microsoft Internet Information Services (IIS)
SSRS Ecosystem
The main participants in the SSRS ecosystem are:
- End Users: Users who are interested in generated reports
- Administrators: Users responsible for maintaining the SSRS services and Report Manager
- Designers: Users responsible for designing the reports
- Report Manager: Web-based administration tool for SSRS
- Report Services: Core SSRS functionality deployed as Windows Service
- Report Designers: Tools for designing and previewing reports
- Report Server Database: Data store where configuration data for SSRS is stored
- Report Data: Business data store for data published using reports
- Client Applications: Custom applications integrating with SSRS using Web Service endpoints of SSRS
- Mail Server: Integrates with SSRS to send reports to subscribers
Figure 1: SSRS Ecosystem
- A designer uses Business Intelligence Development Studio on his workstation to design and publish reports to Report Server.
- A user can view reports directly by connecting to the web interface of Report Server using Report Manager.
- Reports Management and Publishing can be integrated with the custom application by using the Web Service interface provided by SSRS. Lot of features for integration with Sharepoint come out-of-the-box.
- Report Server can distribute the reports to subscribers through emails.
- SQL Server-based Report server database stores report server configuration.
- The data for reports can come from relational or multidimensional data from SQL Server, Analysis Services, Oracle, or any Microsoft .NET data provider such as ODBC or OLE DB.
Features
Following are the main SSRS feature areas:
- Creation
- Parameterized Reports
- Data Connection Configuration
- Access & Delivery of Reports
- Report Formats
Creation
Two main tools are available for creating report definitions: Report Designer and Report Builder.
- Report Designer: Fully featured and powerful tool that runs in Business Intelligence Development Studio (BIDS) of Visual Studio. It can be used to create simple to complex reports that include using custom assemblies for custom functionality.
- Report Builder: A quick, simple report building tool that works based on model data sources. It is used mainly to create ad hoc tabular reports. Drag and drop the fields on the template from the model data sources. It is integrated into Report Manager of SSRS.
Creating a report involves two main steps:
- Design and preview the report using the above designers. These are called Report definitions and are stored in .rdl files that are in XML format.
- Deploy the report definition on the report server. SSRS partially compiles the report definitions and stores them in an SSRS database. These are called Published Reports.
Parameterized Reports
Parameters in reports are mainly supported for three kinds of scenarios:
- To select data from a data source based on parameters that can be taken as input from the client also.
- To filter the data retrieved from the data source.
- To selectively format the report. For example, the report can be based on parameter values showing a particular column or not.
There are two kinds of parameters supported:
- Query Parameters: Used to filter data.
- Report Parameters: Mainly used for selective layout formatting of a report.
Data Connection Configuration
Connection information to data sources can be configured in two ways:
- Report-Specific: Connection information is stored at the report level as part of the report definition and cannot be used by any other report.
- Shared: Connection information is created and maintained at a central location on the report server and can be used by multiple reports.
Accessing and Delivering Reports
Two primary modes are supported: On-demand access and Subscription-based access.
- On-demand access: A report can be selected from already deployed reports for viewing. Data for the report will be generated at runtime.
- Subscription-based access: Reports are generated automatically as per schedule and delivered to all subscribers via file share, email, and so forth.
SSRS also comes with Sharepoint web parts that can be integrated with the Sharepoint portal.
Report Formats
SSRS supports publishing reports in various kinds of formats which include Web-oriented, page-oriented and desktop application formats. Some of the file formats supported are: HTML, PDF, XML, CSV, TIFF, Excel, etc.
Architecture
Figure 2: SSRS Architecture
The core parts of SSRS architecture are the following:
- Web Component: This is installed as a web application on IIS and is responsible for providing Web-based management and report viewing functionality. This tool is called ‘Report Manager’. This component also is responsible for deploying Web Service endpoints so that the custom application can connect to SSRS.
- Report Processor: The major functionality of SSRS is implemented by this component. It has three most important sub-components that are responsible for Security/Access of Reports, Rendering and Data Processing. The data processing component is responsible both for persisting SSRS configuration data into the database and retrieving report data from data stores.
The Scheduling and Delivery Processor takes care of sending reports to subscribers as per the schedule. It integrates with Mail Server or Filer Server to publish reports in a configured mode.
SSRS provides a fair number of extensibility options. Extensions can be developed and deployed for all core functionalities, such as Security, Rendering, Delivery, and Data processing.
- Data Sources: Two mainly types of data sources are required for SSRS. One stores the configuration data of SSRS; it can be a SQL Server database or even a Windows internal database. The other retrieves the data that needs to be populated in reports.
Using Report Builder to Create a Report
Creating a report basically involves two main steps:
- Designing the report in terms of layout and the data fields that need to be displayed on the report.
- Deploying and Publishing the report. This involves setting up the data source for the report data in case of shared data sources.
SSRS services are installed with a basic Report Designer tool that can be used to create basic reports with a few clicks. Here is the walkthrough of how a basic report can be created using the Report Builder tool.
- Start the Report Manager using the URL that was set up during the Virtual Directory configuration of ‘Report Manager’. The default URL is http://<MachineName>/Reports.
Figure 3: Report Manager
- Click ‘New Data Source’ to set up the link with the database that will be used as the data source for the generated report.
- Once Data Source has been set up, go to the details page of Data Source and Generate Model for the Data Source.
- Once Data Source has been set up and Model has been generated successfully, the Report Manager home will have two entries, as shown in Figure 5:
- The next step is to design the report.
- Click on ‘Report Builder’. It will launch a new application that provides a workspace for designing the report using the Data Model generated above (see Figure 6).
- Select the Model generated above and click ‘OK’.
- For this walkthrough, the sample database used has only one table. It has the following structure:
Figure 4: Setting up Data Source
Figure 5: Report Manager
Figure 6: Report Builder Workspace
Figure 7: Data Structure
- Drag and drop the fields available on to the report. Following is the sample report designed for this walkthrough.
- Once you are through with designing the report, save the report. It will get deployed directly and published on Report Server.
- After the successful ‘Save’, the Home of Report Server will have an entry for the report, as shown in Figure 9.
- To view the report, click on the Report. The report will get generated in HTML format with the option of exporting the report in various other formats, as shown in Figure 10.
Figure 8: Report Layout in Design Mode
Figure 9: Report Manager
Figure 10: Generated Report
Conclusion
SQL reporting Services provides an excellent out-of-the-box solution for managing and distributing reports. The Web Service endpoints of SSRS give an excellent integration opportunity to .NET application developers to expose reports using SSRS without building the overheads on their own.
About the Author
Vikas Goyal is a Microsoft MVP Solutions Architect with several years of industry experience. He is involved mainly in designing products/solutions for the Financial industry. He can be contacted via his blog, http://dotnetwithme.blogspot.com.
