SQL Server Reporting Services: Getting Started
- 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)
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.
Following are the main SSRS feature areas:
- Parameterized Reports
- Data Connection Configuration
- Access & Delivery of Reports
- Report Formats
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.
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.