ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


Using the SQL Server 2000 Reporting Services

by Wei-Meng Lee
11/29/2004

Microsoft has finally added reporting capabilities to its flagship database server, SQL Server 2000. Originally designed for its next release of SQL Server, SQL Server 2005 (codenamed Yukon), SQL Server 2000 Reporting Services was recently announced as an add-on to SQL Server 2000 due to the delay of SQL Server 2005's release. With the rollout of SQL Server 2000 Reporting Services, you can now generate reports from your database without the need to use third-party reporting tools.

In this article, I will walk you through the basics of creating a simple report using the SQL Server 2000 Reporting Services. In the next article, I will discuss how you can integrate reports into your .NET applications.

Installing SQL 2000 Reporting Services

SQL 2000 Reporting Services comes in three editions: Developer, Standard, and Enterprise. If you are running Windows XP, then you should use the Developer edition. There are a few prerequisites that you need to satisfy before you can install SQL Server 2000 Reporting Services on your machine:

Creating a Report

Once you are done with the installation, you should see a new project type in Visual Studio .NET 2003: Business Intelligence Projects (see Figure 1). There are two templates available: Report Project Wizard and Report Project.

Figure 1
Figure 1. The new project type for SQL Server 2000 Reporting Services

For now, select the Report Project template and name it "PubsReport". Click OK.

In Solution Explorer, you should see that under the PubsReport project are two folders: Shared Data Sources and Reports (see Figure 2). I will create a report using the Pubs database. Right-click on the Shared Data Sources folder and select Add New Data Source.

Figure 2
Figure 2. Adding a new data source

In the Data Link Properties dialog, enter the name of your SQL Server. In my case, my SQL Server is on my local machine, and hence I type "(local)" and select the Pubs database (see Figure 3). Click OK.

Figure 3
Figure 3. Selecting the database server

The next step is to add a new report to the project. In Solution Explorer, right-click on Reports and select Add New Report (see Figure 4). There are a few ways to add a report: use the wizard, manually add the report, or simply import a report. For starters, I strongly encourage you to use the wizard via the Add New Report option.

Figure 4
Figure 4. Adding a new report

You will now see the wizard. Select the shared data source you configured in the earlier step (see Figure 5). Click Next to proceed.

Figure 5
Figure 5. Selecting the data source to use for the report

For my report, I want to list the book titles and their publisher information from the titles and publishers tables. You can either use the Query Builder (click on Edit... in Figure 6) or type in the SQL string manually. Click Next.

Figure 6
Figure 6. Entering the SQL query

Select the report type. For this report, I have selected the Tabular type (see Figure 7). Click Next.

Figure 7
Figure 7. Selecting the report type

In the next dialog, you will choose the various fields to use for the report (see Figure 8). The list of fields is listed on the left list box. To assign a field to the relevant section of the report, select the particular field and click on any of the Page>, Group>, or Details> buttons. Figure 8 shows that I want to display the publisher information on every page (hence the fields in the Page section) with all of the books' information grouped by type (hence the type field in the Group section). Within each book type, the detail book information is displayed (hence the fields in the Details section). Click Next to proceed.

Figure 8
Figure 8. Designing the table

Choose the table layout and check the "Include subtotals" option if you want to sum up sales information of books of each type. The "Enable drilldown" option allows information to be collapsed or expanded (see Figure 9). Click Next to proceed.

Figure 9
Figure 9. Choosing the table layout

There are a couple of styles you can choose for the report (see Figure 10). Click Next to proceed.

Figure 10
Figure 10. Choosing the table style

In the last step, give a name to your report and click Finish to complete the wizard (see Figure 11).

Figure 11
Figure 11. Finishing the wizard

Deploying the Report

Before you deploy your report for the first time, you need to configure the target server for hosting your report. In Solution Explorer, right-click on the Project name and select Properties. In the TargetServerURL field, specify the location of your Report Manager, which is by default http://localhost/ReportServer (see Figure 12).

Figure 12
Figure 12. Specifying the server to deploy your report

To deploy the report, press F5. In your web browser, you will see the list of reports published by the current project (see Figure 13). Here, there are three reports (the first two were created prior to this current report): Report1, Report2, and TitlesReport. To view the new report, click TitlesReport.

Figure 13
Figure 13. The deployed reports

You should now be able to view the report (see Figure 14). Click on the + sign to expand the list of books under a particular type.

Figure 14
Figure 14. Viewing the report. Click image for full-size screen shot.

What's cool about the SQL Server 2000 Reporting Services is that you can also export the report to various file formats. My personal favorite is the PDF option (see Figure 15).

Figure 15
Figure 15. Exporting the report to other file formats

Summary

In this article, I have briefly shown you the steps to create a report using the SQL Server 2000 Reporting Services. Using the wizard, you can create a new report quickly and easily without much effort. While this is useful for beginners, you should really explore the other features of the Report Designer to customize the format of your report as you become familiar with how the Report Designer works.

In the next article, I will discuss how you can integrate reports into your .NET applications.

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.


Return to ONDotnet.com.

Copyright © 2009 O'Reilly Media, Inc.