Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Thursday, September 21, 2006

Introducing SQL Server 2000 Reporting Services

Related Links



Link to actual source

Microsoft has finally added reporting capabilities to SQL Server 2000. 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

The 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:

  • You must update SQL Server 2000 to Service Pack 3.
  • You need to have Visual Studio .NET in order to use the Report Designer to design your reports. Also, ASP.NET 1.1 must be enabled on your web server. Initially, I had some frustrations with this aspect when I was installing SQL Server 2000 Reporting Services on a development machine. It kept complaining that ASP.NET 1.1 is not installed or not registered with IIS, and refused to install the server-side components of the Report Manager, although ASP.NET 1.1 was indeed installed on the machine. One quick way to solve this problem is to run the aspnet_regiis.exe -i utility within the Visual Studio .NET command prompt to register the ASP.NET runtime with the IIS. To check if ASP.NET is associated with IIS, create a new ASP.NET web project in Visual Studio .NET and try to debug the web application by pressing F5. If its works, you should have no problem with installing SQL Server 2000 Reporting Services. For my case, I had no luck in installing SQL Server 2000 Reporting Services on my development machines. Ultimately, I managed to install it on a fresh machine in the following installation sequence: Windows XP, IIS, Visual Studio .NET 2003, SQL Server 2000, SP3, and then SQL Server 2000 Reporting Services.
  • You can (and should) install SQL Server 2000 Reporting Services on a separate server. This is the recommended option to avoid slowing down the main database server.
  • To use the SQL Server 2000 Reporting Services, you need to have a valid license for SQL Server 2000.
  • 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). We 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. For 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 book titles and their publishers' 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 in the left list box. To assign a field to the relevant section of the report, select the particular field and click Page>, Group>, or Details>. 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 detailed 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 in 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

Post a Comment