AdventureWorks Report Samples

Article
08/31/2009

The AdventureWorks 2008 sample reports are a set of predefined report definition files that use the AdventureWorks 2008 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 SQL Server 2008 Reporting Services (SSRS) or as templates for designing new reports.

Important

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Note

If you are using SQL Server 2008 Express 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 SQL Server 2008 Analysis Services (SSAS), which is not available in SQL Server 2008 Express.

Scenario

Adventure Works Cycles 2008 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 2008 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 2008	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 2008	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 2008	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 2008	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.
Sales Order Detail 2008	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 2008	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.
Store Contacts 2008	Adventure Works Cycles store contacts. Each store can have one or more store contacts. This report is a subreport used in Sales Order Detail to show all contacts for a store and a drillthrough report for the Product Line sales chart. The border style for the table is None so that borders do not appear in the main report. Note that the page header and footer content are not included when the report is a subreport.
Sales Trend 2008	Adventure Works Cycles sales trend analysis that compares year over year (Y/Y) growth for products by category and subcategory. This report illustrates the following features: a Tablix with conditionally hidden static columns, repeat column and row headers, and KeepTogether static Tablix member; the aggregate function Previous that calculates Y/Y trends; custom code; conditional font, background cell color, KPI images, and drillthrough parameters.

Prerequisites

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

Server

Microsoft SQL Server 2008 Reporting Services (SSRS) (Evaluation, Standard, Developer, or Enterprise), or SQL Server 2008 Express with Advanced Services.
Microsoft SQL Server 2008.
AdventureWorks 2008 sample database.

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

Deploying the AdventureWorks Sample Reports

On the Business Intelligence Development Studio toolbar, click File, point to Open, and then click Project/Solution.
Browse to <drive:>\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports, select the file AdventureWorks 2008 Sample Reports.sln and click Open.
If you are using SQL Server Express 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 2008 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=AdventureWorks2008
7. Click OK to close the Shared Data Source dialog box.
On the standard toolbar, select the Production solution configuration. (The default configuration is Debug.)
On the Build menu, click Deploy AdventureWorks 2008 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

Open a browser window. Report Manager requires Internet Explorer 6.0 or later with scripting enabled.
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.
Navigate to the folder that contains the reports. By default, it is the AdventureWorks Sample Reports folder.
Click the name of the report that you want to open.

Share via