Reporting Services Overview

Reporting Services is a part of Microsoft SQL Services. It first appeared as an optional download for SQL2000 and is a standard option for SQL2005.

As its name implies it is a reporting tool. More specifically its a web based reporting tool built on SQL but that allows reporting on any data source that you can create an ODBC connection too.

Predominantly I use SQL databases as the data source, although I have also built reports using Analysis services as the data source.

The principle is very straight forward. Using Visual Studio 2005 you create a new report. Link it to a data source, either defined specifically in the report or by using an external shares link.

You can then write SQL queries against the data in a series of data sets. You can also set a data set to execute stored procedures on the SQL database. This allows for very complex queries to be written.

Within the report you have access to a number of different controls, including graphing and pivot table style controls.

You can specify that some controls are dependant on other controls and only appear when the parent is selected.

Another useful tool is the principle of 'drill through' reports. These are effectively extra reports that are called when part of a report is selected. All appropriate parameters are passed down to the new report. This allows you to write summary reports with the drill through allowing you to have details when needed.

Another feature of Reporting services is the ability to subscribe to reports. This allows a user to pick a report from the report server and specify when they would like to automatically get it. They can also specify the format the report should be delivered in, e.g. pdf, Excel Spreadsheet, tiff etc. The can set what parameters the report should use and where it should be delivered to. This allows a sales manager, for example, to have the previous days sales results delivered at 8am to his email as an excel spreadsheet.

One final tool that comes with some of the versions of Reporting Services is Report Builder. This is a web deployed report designer. It requires that a data model has been built in advance but once that has been done then an end user can use the Report Builder to build ad hoc reports on the data source. These could be one off reports or if the user decides that the report will be useful to other users they can deploy the report back to the report server where it becomes available to all users.

The Report Library itself is either accessed via its own web site or it can be linked to Microsoft SharePoint. Where it is linked the reports appear in their own library inside the SharePoint portal and can be used in the same was as from the web site.