Chapter 14. SQL Server Reporting Services (SSRS)

SQL Server 2005 Reporting Services (SSRS) is a middle-tier server that provides a reporting environment running on top of Internet Information Services (IIS). You can build reports from any data source that has a .NET Framework-managed data provider, an OLE DB provider, or an ODBC provider. This lets you build reports based on relational, multidimensional, and XML data sources from a wide range of database servers. You can access a report by using a URL, the report viewer control in either a web or Windows application, or Report Server web services.

SSRS supports tabular, matrix, chart, and free-form report layoutsthe layouts, together with graphical elements, can be combined in a single report if required. You can include links to additional information or to related reports and drill-down functionality. Reports can contain both mandatory and optional parameters. Ad hoc reporting is supported through use of Report Buildera ClickOnce Windows application that is accessed through the URL http://<servername>/reportserver/reportbuilder/reportbuilder.application from the report server or from Report Manager.

SSRS supports a variety of output formats, including HTML 3.2, HTML 4.0, MHTML, PDF, XML, Excel, CSV, and Image (TIFF). SSRS automatically provides a navigation toolbar for those formats that support toolbars. Additionally, you can add document maps and bookmarks to facilitate navigation in large reports.

Three tools are provided for designing reports. Report Designer is a report-authoring application hosted within Business Intelligence Development Studio. Report Designer lets you define, preview, and publish reports. Report Designer provides query builders, an expression editor, and wizards to help you work with images and to create simple reports. Ad hoc reporting is supported by two tools: Model Designer, which you use to define, edit, and publish report modelsbusiness-oriented abstractions of underlying data used to help build ad hoc reportsand Report Builder, which you use to create ad hoc reports based on published report models and manage them as you would any other reports.

SSRS includes configuration, monitoring, and management tools. Role-based security controls access to folders, reports, and resources. Report-delivery options include on-demand, through included SharePoint Web parts that let you view a report from a SharePoint site, automated by subscription, or automated based on data.

SSRS is an open and extensible reporting platform. You can use the API to develop, install, and manage SSRS component extensions to support custom-data, delivery, and rendering requirements. Report Definition Language (RDL) is used to describe the layout and content of a report. RDL is an XML-based grammar that you can extend to implement custom functionality. RDL can be generated manually or programmatically using Report Designer.

This chapter discusses how to programmatically access SSRS reports, and how to incorporate them into your applications. For more information about designing, defining, deploying, delivering, and securing reports, see Microsoft SQL Server 2005 Books Online.