SQL Server 2000 Reporting Services, the SQL Server-based enterprise reporting solution Microsoft released in January 2004, is positioned to become one of the most popular SQL Server components. Nearly all organizations need to produce reports from their data, and with Reporting Services, Microsoft filled this large hole in SQL Server's toolkit. You can install Reporting Services on any SQL Server 2000 computer at no additional cost, and you'll be able to install it as part of SQL Server 2005.
In spite of the solution's benefits and the excitement surrounding its initial release, many SQL Server professionals have limited or no hands-on experience with Reporting Services. If you're like many database professionals, you might have put off using Reporting Services because of its relative newness, the fact that it requires a separate SQL Server installation that works along with your production SQL Server, or maybe its list of prerequisites. But Reporting Services isn't so new any more, and Microsoft has released Reporting Services Service Pack 1 (SP1), which fixes the bugs in the initial release. In addition, Microsoft is integrating Reporting Services with SQL Server 2005, so learning how to use Reporting Services now will give you a head start on SQL Server 2005. This article gives you the basics for getting started with Reporting Services and includes SP1 examples that you can reproduce in your test environment. I start by giving you the prerequisites for using Reporting Services and explaining where to get it. Then, I walk you through the steps for authoring two reports and for deploying those reports to the Report Server, Reporting Services' main component. Finally, I teach you two ways to view deployed reports.
Installing Reporting Services
To properly install Reporting Services, your system needs four elements. First, you need Windows Server 2003, Windows XP, or Windows 2000 with the most recent service packs installed. (For a list of service packs for each OS, see the Microsoft article "Reporting Services System Requirements," which is listed in Related Reading.) Second, you need Microsoft IIS because Reporting Services runs as an XML Web service. Third, you need the standard, enterprise, or developer edition of SQL Server 2000. (Reporting Services isn't compatible with earlier SQL Server releases.) Fourth, report designers need Visual Studio .NET 2003, which hosts Reporting Services' Report Designer component. (For administrators who don't design reports, Reporting Services provides a different UI that permits the creation of folders, data sources, and users and the assignment of permissions to users.)
After you make sure your system meets the prerequisites, you can install Reporting Services, then install SP1 to update the initial release. You can download a trial version of Reporting Services at the URL in Related Reading.
Creating Your First Report
The only Report Designer Microsoft offers for authoring Reporting Services reports is in Visual Studio .NET 2003. When you install Reporting Services, the installation process automatically updates Visual Studio .NET by adding a new project type called Business Intelligence Projects. You don't necessarily need to have Visual Studio .NET installed on the same server as Reporting Services. As I explain in a moment, you can reference a target-server URL for Reporting Services, which can be different from the location of the workstation you use to run Visual Studio .NET. Within this project type are two templates named Report Project Wizard and Report Project. Both templates let you perform the steps to create a report: defining a report's data source, specifying a report's layout, previewing a report, and deploying a finished report to the Report Server.
To create your first report, start a new Business Intelligence project in Visual Studio .NET, and choose the Report Wizard Project template. Name your project SSMRS-Intro. Read the wizard's welcome screen, then click Next to go to the Select the Data Source screen and specify the report's data source. Click Edit to open the familiar Data Link Properties dialog box that Figure 1 shows. On the dialog box's Provider tab, select Microsoft OLE DB Provider for SQL Server as the type of data you want to connect to. As Figure 1 shows, the dialog box's Connections tab lets you specify on the local SQL Server instance a Windows NT Integrated security-based connection to the Northwind database. Click Test Connection, then click OK to return to the Select the Data Source screen, which now shows a connection string that points to a data source named after the database. Note that unless you select the Make this a shared data source check box at the bottom of the screen, the wizard embeds the data source so that you can use it exclusively for this one report.
Clicking Next opens the wizard's Design the Query screen. You can either type an SQL query statement into the Query string text box or click Edit to open a graphical query designer that operates like the query builder in Enterprise Manager. For this example, you can use the following query:
SELECT CompanyName, ContactName, Phone, Country
FROM Customers
WHERE (Country = 'Canada') OR
(Country = 'Mexico') OR
(Country = 'USA')
Then, click Next to open the Select the Report Type screen. The wizard offers two report types: tabular and matrix. The matrix type is for a cross-tab report, which we won't create in this article's examples. For this demonstration, select Tabular.