email article printer friendly more resources

Build SQL Server Reports Quickly
Use the SQL Server Report Designer's wizards for a streamlined solution to setting up, designing, polishing, and publishing reports.
by Stacia Misner

Posted July 2, 2004

Technology Toolbox: SQL Server 2000

Editor's Note: This article is excerpted from Chapter 3 of Stacia Misner's upcoming book, Microsoft SQL Server 2000 Reporting Services—Step by Step [Microsoft Press, ISBN: 0735621063]. It has been edited for length and format to fit the magazine. You can read a PDF of the full chapter here.

SQL Server Reporting Services includes the SQL Server Report Designer, whose wizards give you a streamlined solution for setting up, designing, polishing, and publishing reports. Authoring a report starts with defining a SQL Server Reporting Services data source, which packages information about the location of the data your report uses. It also defines the credentials users must have before they're allowed to access that data. You must know which server hosts the data and which database or file stores it, and you need to have the right credentials and permissions to retrieve that data. You must define at least one data source for each report you author.

Next, you create a data set for the report, including at least one query, using the appropriate language and syntax needed to retrieve data—Transact-SQL (T-SQL), if you're accessing a Microsoft SQL Server database. A data set also includes a pointer to the data source and other information used when the query executes. The Report Project Wizard lets you define only one data set, but you can access multiple data sets in one report if you forego the wizard and use T-SQL (see Table 1).

Finish constructing your report by creating a report layout, which is the design template SQL Server Reporting Services uses to arrange and format the data. The report layout includes the structure, or data region (such as a table or matrix), where data is placed when the report is processed. You can define style properties for each section of a data region, including font, color, and format, and you can set these properties for report items as well, including the report title in a textbox, or the report background. You can control the look and feel of your report precisely.

The Report Project Wizard and the Report Wizard walk you through each step. They help you create Microsoft Visual Studio containers to hold your new report, project, and solution. Name these containers and give them a storage location on your computer's hard drive or on a network file share.

Fire up the Report Project Wizard, then Visual Studio. Go to the Visual Studio File menu, point to New, and click on Project. The New Project dialog box appears. View the available templates by clicking on the Business Intelligence Projects folder. Click on the Report Project Wizard, then on the More button at the bottom of the dialog box if it's visible. Name the project Adventure Works for the purposes of this exercise. The text in the New Solution Name box of the New Project dialog box changes to match the project name as you type along. Change the solution name later if you wish.

Give the project this location: c:\rs2000sbs\Workspace. Click on OK to bring up the Report Wizard's Welcome page (which you can disable). The Report Wizard differs from the Report Project Wizard, which lets you create a solution, a project, and a report in one step, then launches the Report Wizard. You can use the Report Wizard at any time to add a report to an existing project using a wizard interface. Click on Next.