AdventureWorks Report Samples

11/05/2008 21:36:06


The 2008 sample reports are a set of predefined report definition files that use the 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

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 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 AdventureWorks 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 AdventureWorks 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.
  • 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

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.

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 <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.
  3. 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 2008 sample database. The default values in this sample are not correct for a SQL Server Express installation:
  4. On the Project menu, select Properties.
  5. In OverwriteDataSources, select True.
  6. In TargetServerURL, type the following URL: http://localhost/reportserver$SQLExpress
  7. Click OK to close the Solution Property Pages dialog box.
  8. In Solution Explorer, in Shared Data Sources, double-click AdventureWorks.rds.
  9. 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
  10. Click OK to close the Shared Data Source dialog box.
  11. On the standard toolbar, select the Production solution configuration. (The default configuration is Debug.)
  12. 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
  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 http://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
Considerations for Installing SQL Server Samples and Sample Databases
Help and Information
  • Getting SQL Server 2008 Assistance* © 2008 Microsoft Corporation. All rights reserved.

Last edited May 18, 2010 at 11:26 PM by mlingel, version 10

Comments

mshankar Jul 15, 2012 at 12:53 PM 
Good Exmples

jdhin Mar 6, 2012 at 12:02 AM 
Hi,

I'm getting the following error on some tables when I try to deploy. Please advise on a fix.

Error:
[rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded. C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\AdventureWorks 2008R2 Sample Reports\AdventureWorks 2008R2\Employee_Sales_Summary_2008R2.rdl

moontear Jan 20, 2010 at 11:55 AM 
@candlynn I had the same problem as you did and that was because the "AdventureWorks OLTP 2008" database wasn't installed. I couldn't install it because I didn't enable FILESTREAM beforehand (hence only the "AdventureWorks OLTP" and other databases were installed but not the 2008 version).
After enabling FILESTREAM as described in the database prerequisites http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites I could install the 2008 version of Adventure Works and all sample reports work just fine now.

candlynn Sep 8, 2009 at 2:23 PM 
The AdventureWorks 2008 Sample reports do not seem to match up with the sample AdventureWorks 2008 database. Company Sales is the only report that works as installed. Some element named BusinessEntityID is referenced in the reports but I cannot find it in any version of the AdventureWorks database.

castlewalls Jan 3, 2008 at 6:20 AM 
the section on "Deploying the Sample Cube " is hard to read. Would be nice even if the core was a bulleted list of things to do.
thanks