AdventureWorks Report Samples

  • Article

The AdventureWorks sample reports are a set of predefined report definition files that use the AdventureWorks databases as data sources. You can upload and view each report separately; however, some reports are designed to work together through the use of links. You can use the sample reports to familiarize yourself with the capabilities of Reporting Services or as templates for designing new reports.

Note

If you are using SQL Server 2005 Express Edition with Advanced Services, you must modify the report server URL before publishing the sample reports to the report server. Also, be aware that the Sales Reason Comparison report is not supported. That report requires Analysis Services, which is not available in the Express edition.

Scenario

Adventure Works Cycles publishes product and sales information that is accessed by employees throughout the company. The reports present this data in a variety of formats. Users can view these reports on demand by opening them in Report Manager.

Language

Report Definition Language (RDL)

Features

The AdventureWorks sample reports demonstrate a wide range of report features. The following table provides descriptions that explain which features are implemented in each report.

Report

Description

Company Sales

Adventure Works Cycles sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.

Employee Sales Summary

Adventure Works Cycles sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.

Product Catalog

The Adventure Works Cycles full product catalog with pictures. This report illustrates the use of embedded images, database images, page breaks, page footers, tables, conditional formatting, and a document map.

Product Line Sales

Adventure Works Cycles top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, multivalued parameters, end-user sorting, calculated fields, and drillthrough links.

ms160721.note(en-US,SQL.90).gifNote:

Starting in SQL Server 2005 Service Pack 1, the Subcategory parameter drop-down list no longer includes an "All" member.

Sales Order Detail

Detail of an individual Adventure Works Cycles order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.

Territory Sales

Adventure Works Cycles sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.

Sales Reason Comparison

Shows sales reason comparison data from an Analysis Services cube. Also illustrates the use of a multivalued parameter. The Sales Reason Comparisons report requires SQL Server Analysis Services and the AdventureWorks Analysis Services database.

Prerequisites

Your system must meet the following requirements to use the sample reports.

Server

  • Microsoft SQL Server 2005 Reporting Services (Evaluation, Standard, Developer, or Enterprise editions), or SQL Server 2005 Express Edition with Advanced Services
  • Microsoft SQL Server 2005
  • AdventureWorks and AdventureWorksDW sample databases, which are included with SQL Server 2005, and are also available at the SQL Server Developer Web site.
  • Microsoft SQL Server 2005 Analysis Services (required for the Sales Reason Comparison report)
  • The AdventureWorks OLAP cube (required for the Sales Reason Comparison report)

Client

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

    Note

    Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server 2005 sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples.

Installing Samples

The SQL Server samples are not installed automatically during setup. For instructions about how to install the samples, see Installing Samples.

Deploying the Sample Cube

The sample AdventureWorks Analysis Services project is available in Standard and Enterprise Editions. The Sales Reason Comparisons report works with both versions of the project.

How to deploy the Standard Edition version of the sample cube

  1. From the Business Intelligence Development Studio tool bar, click File, point to Open and then click Project/Solution.

  2. Browse to C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Standard, select the file Adventure Works DW.sln and click Open.

  3. From Solution Explorer, right-click Adventure Works DW Standard Edition and select Deploy.

  4. In Management Studio, connect to the default Analysis Services instance.

  5. Expand Databases and verify that the Adventure Works DW Standard Edition is available.

  6. In Business Intelligence Development Studio, click File, point to Open and then click Project/Solution.

  7. Browse to C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports, select the file AdventureWorks Sample Reports.sln and click Open.

    Note

    Reporting Services samples and the Analysis Services samples are located in different folders. If you cannot find the AdventureWorks Sample Reports.sln file, verify that the path information is correct.

  8. In Solution Explorer, double-click the AdventureWorksAS.rds file.

  9. Type or copy and paste the following connection string. The connection string specifies the Standard Edition of the Adventure Works DW sample database:

    Data Source=(local);Initial Catalog="Adventure Works DW Standard Edition"

  10. Deploy the sample reports using the instructions provided in "Deploying the AdventureWorks Sample Reports". Because the solution is already open, you can proceed to step 3.

How to deploy the Enterprise Edition version of the sample cube

  1. On the Business Intelligence Development Studio toolbar, click File, point to Open and then click Project/Solution.
  2. Browse to C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise, select the file Adventure Works DW.sln, and then click Open.
  3. In Solution Explorer, right-click Adventure Works DW and select Deploy.
  4. In SQL Server Management Studio, connect to the default Analysis Services instance.
  5. Expand Databases and verify that the sample database AdventureWorksDW is available.
  6. Continue with the steps in the next section to deploy the AdventureWorks sample reports to the report server.

Deploying the AdventureWorks Sample Reports

  1. On the Business Intelligence Development Studio toolbar, click File, point to Open, and then click Project/Solution.
  2. Browse to C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports, select the file AdventureWorks Sample Reports.sln and click Open.
  3. If you are using SQL Server 2005 Express Edition with Advanced Services, you must modify the target report server URL property and change the default connection string that specifies a connection to the AdventureWorks sample database. The default values in this sample are not correct for a SQL Server Express installation:
    1. On the Project menu, select Properties.
    2. In OverwriteDataSources, select True.
    3. In TargetServerURL, type the following URL:
      https://localhost/reportserver$SQLExpress
    4. Click OK to close the Solution Property Pages dialog box.
    5. In Solution Explorer, in Shared Data Sources, double-click AdventureWorks.rds.
    6. In the Shared Data Source dialog box, in the General tab, modify the connection string to use the following value:
      Data Source=localhost\SQLExpress; Initial Catalog=AdventureWorks
    7. Click OK to close the Shared Data Source dialog box.
  4. On the standard toolbar, select the Production solution configuration. (The default configuration is Debug.)
  5. On the Build menu, click Deploy AdventureWorks Sample Reports. When you deploy the solution, the reports are published to the report server.

Viewing Report Samples in Report Manager

After you publish the report samples, you can view them in Report Manager. Viewing reports requires the following permissions:

  • You must have permission to log on to the local SQL Server Database Engine that hosts the AdventureWorks database. By default, report data sources are configured to use Windows integrated security. Your Windows account credentials are used to log on to the database.
  • You must have a role assignment that grants you access to the report. If you are a local administrator on the computer, all of these permissions are granted automatically. If you are not a local administrator, you must configure role assignments and login permissions before you view the report.

To view a report in Report Manager, follow these steps:

  1. Open a browser window. Report Manager requires Internet Explorer 6.0 or later with scripting enabled.
  2. Type the Report Manager URL in the address bar of a Web browser. By default, the URL is http://<webservername>/reports. If you are using SQL Server Express, the default URL is https://localhost/reports$SQLExpress.
  3. Navigate to the folder that contains the reports. By default, it is the AdventureWorks Sample Reports folder.
  4. Click the name of the report that you want to open.

See Also

Tasks

Script Samples (Reporting Services)

Other Resources

Installing AdventureWorks Sample Databases and Samples
How to: Publish Report Samples (Report Designer)
How to: Upload Report Samples (Report Manager)

Help and Information

Getting SQL Server 2005 Assistance