Introduction
SQL Server Reporting Services is a comprehensive, server-based platform for creating, managing, and delivering reports that contain data from relational and multidimensional data sources.
This article explains in brief about the Features that are supported and about Designing a report.
Features
1. Graphical Report Authoring
Reporting Services has a powerful graphical report designer that lets users create report definitions by dragging and dropping design and data elements.
2. Flexible Report Delivery
With support for both push- and pull-style report delivery, Reporting Services provides a flexible mechanism for generating reports. In pull-style report delivery, users can run previously created reports on demand. Users can also automate push-style report delivery so that the reporting server generates reports based on predetermined events.
3. Multiple Data-Source Options
Reporting Services isn't limited to using data from Analysis Services and SQL Server databases. The new product is compatible with other databases, including Oracle. Data-source options include ODBC, OLE DB, XML for Analysis, and Web services. Reporting Services can use multiple data sources for a single report and can even use reports as sources for other reports.
4. Multiple Reporting Options
Reporting Services supports a variety of reporting formats, including Web-based, XML, email, and standard printed reports. In addition to these basic options, Reporting Services supports several more sophisticated formats including Excel, PDF, and custom formats.
5. Support for SQL Server and Analysis Services
Reporting Services can create reports for relational databases such as SQL Server and for Analysis Services. This flexibility lets users generate reports from SQL Server databases or Analysis Services cubes without using Office or other external applications.
6. Support for ActiveX controls
Reporting Services provides support for embedded ActiveX controls helps to add many custom capabilities i.e. such as a calendar or multimedia elements to the reports.
Designing a Report
Two templates are provided in Visual Studio.Net 2003. Report Project Wizard which helps to build the report from scratch and Report Project which invokes Report Designer.
First step is to define a Data source. Data Source can either be defined for each report or in a report project so that multiple reports share the same Data source.
In addition, a report can contain multiple data sources if it needs to retrieve information from more than one location.
The Report Life Cycle consists of the following phases:
Authoring
The Authoring phase consists of :
1. Defining the Data area where the Query is constructed to retrieve the data. Reporting Services executes the query defined for the data set against the data source. The rows and columns that result from this query
become the data set. It is possible to define the Input Parameters for the
Query.
2. The Layout Area where the report is constructed by dragging controls from the Toolbox and fields from the Fields window and dropping them in the Report design. Only four Report controls can be bound to a Data set which are :the Table, Matrix, List, and Chart controls.
- The Table control produces a column-and-row report with optional gGrouping and subtotaling.
- The Matrix control outputs a Crosstab report.
- The List control produces repeating blocks of free-form data.
- The Chart control also interacts directly with a data set.
The definition of the report is stored in a Report Definition Language(RDL)
which is an XML.
Visual Studio deploys the RDL to a Report Server, which stores it in a SQL Server database. When Reporting Services delivers a report to a user, it processes the RDL report definition and renders it into a more familiar format such as an HTML page or an Adobe PDF document.
Management
After a report definition is complete, the report designer publishes it to a report server and it becomes a managed report. Report Manager is used to manage the reports and the reporting environment.
Report definitions, folders, and resources are published and managed as a Web service.
The Security, Properties and Scheduled operations applicable are defined for the items like reports,folders,Data source connections etc. It mainly consists of organizing folders to store various reports, Securing access rights to folders and reports and setting up shared schedules and shared data sources that are available for general use.
Deployment
The report can be deployed by uploading its RDL file directly from the Report
Manager. To use this method, you must locate the RDL file for the report you
want to deploy. This will also help to deploy a singe report from a Project which
might contain several reports.
Delivery
The reports can be distributed in the Report Catalog through the Report Manager, Microsoft's SharePoint Portal Server, or a custom application. The Report Manager lets you organize reports into folders and lets users access the reports on demand or subscribe to them by email. Reports can be live reports in which the data is requeried each time they run, or they can be snapshots of standard reports that are updated regularly.
CONCLUSION
Reporting Services will let users of varying skill levels create their own dynamic reports. Programming Experience will only be needed in case of designing Complex reports. You can make reports available over the Internet or an intranet in several formats, including PDF and TIFF, that look good both in a browser and in print. And Reporting Services provides the infrastructure that makes accessing these reports convenient and secure.