Reporting Services provides a set of tools, designers and Application Programming Interfaces (APIs) that you can use to author, manage and publish reports. As of now, Reporting Services is available as a seperate download that you can install and use. Future versions of SQL Server (namely Yukon) will have Reporting Services integrated into the server platform and will provide improved features.

If you were to slightly drill down into the architecture of Reporting Services, it is made up of a series of integrating blocks. The architecture is a perfect example of a multi-tiered, multi-layered architecture and has a presentation layer, a services layer and a database layer. If you were to give names to each of these layers, you would have the following blocks:

The Report Manager is the presentation layer that provides a set of interfaces and functions that you can use to manage and publish reports. This interface layer has been written using .NET. The Report Server is the main engine that provides all the capabilities for reporting (handling reporting requests, retrieving report properties, formatting information, merging data and the formatting and rendering of the final report) and is implemented as a web-service. The Report Manager is actually implemented using the Report Server web-service. This means that you can also easily write your own management interface using various calls to the web-service. The Report Server Database is the final block that implements a database and related tables to hold all the information required for Reporting Services to work. The following diagram (picked from the Reporting Services online help) shows how the various components work together.

Notice how the various blocks perform various portions of the work and how we can integrate extensions into the system to extend the capabilities of Reporting Services. One example of an extension is to output a report to a WAP enabled phone. Having seen the various components, one questions that remains is: how do you design reports?? Designing is accomplished using the Report Designer. Report Designer is a tool that integrates with Visual Studio .NET 2003 and allows the design and publishing of reports to the Report Server. The Report Designer provides the familiar metaphor of developing projects in Visual Studio, compiling them and then deploying them.

As of the current release, Reporting Services and its tools can be used to create the following types of reports:

Once the reports are designed, you can either test it locally on your machine itself or deploy it to the Report Server and then test the same. When you save a report definition (which is your layout, formatting etc), it is saved into a new XML schema called as RDL (Report Definition Language). Storing the report definition as XML enables the consumption of the report across various platforms and tools. For example, if you are adventurous, you could consume the RDL definition in another platform like a Mac and write a designer that allows the manipulation of this XML and output the same into RDL, which can again be consumed in Windows.

Data for a report can come from many sources. Data is fetched from these various sources using data processing extensions, which are implemented using ADO.NET managed providers. Out of the box, Reporting Services provides extensions for the following data sources:

Since Reporting Services is extensible, you can also write your own data provider extension that exposes data from a particular source (for example Active Directory). Finally, apart from the various APIs, Reporting Services also provides a set of command line tools that can be used to manage a report.

Having now seen a 5000 feet overview of Reporting Services, let us now create a simple report to see how all the various blocks come together to help us create, manage and publish reports. The example that we will create in this article is a simple list of authors from the pubs database. The following sections outline the various steps that need to be followed to create this report.

The first step is to create a project in Visual Studio that can be used to create reports. To create a report project click File > New > Project . In the new project dialog box, choose the Business Intelligence Projects project type and choose the Report Project Wizard template. Provide a name and location for the report and click on the OK button. The following figure shows the contents filled in the dialob box for this report.

Step 2: Create Data Source

After you provide the project details, Visual Studio will crank for some time and present you with the first of the wizard screens. In this screen, you will select the data source for your report. In the screen that is shown, click on the Edit button. This shows the Data Link Properties dialog box as shown. You can use this dialog box to configure the connection.

Note that I have pointed to the server on my local machine and that I'm using Windows Integrated Security to connect to the server. The database that we want to connect will be the pubs database. After making all the selections, you can click on the Test Connection button. If everything is fine, you should get a confirmation that says that the test was successful. Click on OK to close the dialg box. Closing the dialog box will update the contents of the data source wizard screen as shown. Note that we have configured this connection as a shared connection. This means that we can reuse this connection across other reports as well. Click on the Next button to advance in the wizard.

Step 3: Design the Query

The next step is to define the query that needs to be used to fetch the data for the report. In our example, it is a very simple query since we want only the contents of the authors table. The following figure shows the query defined in this wizard screen. After you have entered the necessary data in this screen, click on the Next button to go to the next wizard screen.

Step 4: Select Report Type

Having defined the query, the next step is to decide the kind of report that you are going to design. The options are Tabular and Matrix. Since we are creating a simple row/column report, choose Tabular. This selection is shown in the following figure.

Step 5: Designing the Report Table

Reports normally contain a Page header / footer section, a detail section where data will be displayed and optional group sections using which you can group data based on some criteria. In this wizard screen, you will do exactly this, which is to identify what data goes into the various portions of the report. We will not complicate this report too much and thus, will use only the detail portion. You can select the various fields that needs to be displayed and click on the Details button. For our example, we will use au_id, au_lname, au_fname, city, state and zip. The following figure shows the selection in this wizard. Click on the Next button to advance in the wizard.

Step 6: Deciding the Table Style

This is more of an aesthetic step. In this screen, you will specify how the report will look like. There are predefined formats available that you can select from. The following figure shows the selection that we have made. Click on the Next button to advance in the wizard.

Step 7: Choose Deployment Location

In this step, you will indicate where the report will be deployed. You will identify the report server that will receive this report and what folder it will be stored against. The following figure shows the selection that we have made. Click on the Next button to advance in the wizard.

Step 8: Complete the Wizard

This is the last step where you will be shown a summary of all your selections made in the previous wizards. This is your final chance to change anything that you have entered in the wizard screens. If you miss this step, you can still change the report information, but you will have to do it inside the report designer IDE. The following figure shows the final dialog box. After reviewing all your data, click on the Finish button.

Step 9: Preview, Manipulate and Publish report

At this stage, Visual Studio will again crank based on the various information entered and then present you the IDE with all the information layed out based on your selections. Note that the IDE is broken into 3 major sections (available as tabs in the screen):

In this article, we will not manipulate anything in this report, since we want to understand what basic capabilities are present in Reporting Services out of the box. So, just click on the Preview button and if everything goes fine, you will now see the following report output.

Well, that was very simple to get a great looking report and remember, there was absolutely no coding done anywhere. We just used the wizard to do all the hard work for us. Note that Visual Studio has created a solution for us and the report is saved as an XML (in the schema dictated by RDL). Having now designed the report and tested it, we now have only one step remaining and that is, to publish the report. To publish the report, right-click the project in the Solutions Explorer and choose Deploy. If you recall, we configured the location of the report server in one of the wizard screens earlier. Visual Studio uses this value and deploys the report to that location. Note that after publishing the report, the report is stored inside the database and is used for all subsequent retrieval. The XML format is still present, but from a Report Server point of view, it is never used for rendering or maintenance inside Report Server . To see the result of our deployment, we need to use the Report Manager URL, which on my machine is: http://localhost/reports. When you hit this URL, note the following:

If you click on the report, the processing starts and the report is now displayed as shown in the following figure.

The output has various elements. The items of interest to us now are the output section, the rendering format and the report navigation sections. You can use the report navigation section to navigate through the various pages of the report. The rendering format is a very interesting piece. Reporting Services, out of the box supports rendering to a variety of formats. You can choose rendering format from the drop-down and the report will be rendered accordingly.

Well, that brings us to the end of this article. SQL Server Reporting Services is an excellent platform for developing, managing and publishing reports. The platform by itself is very extensible and scalable. In this article we have just scratched the surface of what is possible using Reporting Services. In subsequent articles, we will drill down deeper into other types of reports, formatting, sub-reports, parameters etc and working against the API itself.