AdventureWorks Report Samples

5/28/2010


The AdventureWorks 2008R2 sample reports are a suite of predefined report definition files that use the AdventureWorks 2008R2 database as a data source. 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 features of SQL Server 2008 R2 Reporting Services (SSRS) or as templates for designing new reports. For step-by-step instructions about how to build each report, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports.
For troubleshooting information, see Troubleshooting SQL Server 2008 R2 Reporting Services AdventureWorks Sample 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, or if you are publishing reports to a SharePoint site that is integrated with a report server, you must modify the report server URL before publishing the sample reports to the report server.



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 AdventureWorks 2008R2 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
AdventureWorks2008R2_Base The base report, which includes the shared data source for the AdventureWorks 2008R2 database, the AdvnetureWorks logo image and report title in the page header, a report description with parameter-controlled visibility in the page footer, scenario text with toggle-controlled visibility.
EmployeeSalesSummary2008R2 Adventure Works Cycles sales summary report for an individual employee. Displays year over year sales for all categories, for individual categories, and for quotas. Includes charts (line, sparkline, column with scale breaks, databar), tablix (toggle rows, row group, indicators at table and group level), group and report variables, rectangle containers, shared datasets, shared data source, custom code for chart line color, bookmarks, drilthrough action to the SalesOrderDetail2008R2 report, multivalue parameters, and parameters to control conditional visibility of text boxes and toggle rows.
SalesOrderDetail2008R2 Detail of an individual Adventure Works Cycles order. Displays details for a range of orders, and is a drillthrough report for an individual sales order from EmployeeSalesSummary2008R2. Includes free form layout, tablix used as list and table, a subreport, session variables for calculating running page totals, custom code, image, expression-based page breaks based on the number of detail rows, page names, page numbering with report and within group, and conditionally hidden text.
StoreContacts2008R2 AdventureWorks store contacts. Used in SalesOrderDetails_2008R2 to display multiple store contacts. Includes page header and footer items that are not included when the report is rendered as a subreport.
SalesbyRegion2008R2 Shows regional sales by US state. For each state, displays sales totals for individuals, sales totals for reseller stores, and store locations. Includes the US map from the map gallery, analytical data from a dataset, polygon layer (one for each state), polygon fill for no sales, polygon color rules based on individual sales, polygon drillthrough action to bookmark in a matrix, markers for stores, marker colors based on store sales, marker drillthrough action to CustomersNearStores2008R2 report, multiple map titles and legends, and use of Lookup function.
CustomersNearStores_2008R2 Displays information about customers who live within the specified number of miles from a store. The store geolocation is a String parameter that contains a Well Known Text (WKT) value. The spatial data calculation is performed in the query.


Shared Dataset Description
EmployeeSalesYearOverYear2008R2 The data helps answer the question: How do my seasonal sales compare year over year? .
EmployeeSalesDetail2008R2 This data helps answer the question: Am I meeting my sales quotas for the whole month and for each category?
EmpSalesMonth2008R2 This data helps answer the question: How do the category sales for this month compare to sales for the same month in previous years?
SalesEmployees2008R2 This data provides valid values for the @EmployeeID parameter.



Prerequisites

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

Server

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

Client

  • Business Intelligence Development Studio 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 AdventureWorks 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 2008R2 Sample Reports, select the file AdventureWorks 2008R2.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 2008R2 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=AdventureWorks2008R2
  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 2008R2 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 AdventureWorksR2 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 2008R2 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

© 2010 Microsoft Corporation. All rights reserved.

Last edited Aug 23, 2013 at 11:22 PM by sabottaca, version 3

Comments

codeulike Mar 15, 2012 at 2:11 AM 
SQL Server 2008 R2 Express with Advanced Services - I could not deploy the sample reports because Shared Datasets are not supported in SQL Server Reporting Services 2008 R2 Express edition.
Got a series of errors like this when deploying from Bids:
Deploying data set '/Datasets/EmployeeSalesDetail2008R2'.
Error rsOperationNotSupported : The feature: "Shared dataset" is not supported in this edition of Reporting Services.
Some of the reports did deploy however; presumably the ones that dont need shared datasets.

mariwill Sep 16, 2010 at 11:10 PM 
A number of people are having problems running the SQL Server 2008 R2 Reporting Services AdventureWorks sample reports. The problem is most likely that the OLTP version of the AdventureWorks database is not installed. The installation of the database failed because the instance of the SQL Server Database Engine does not meet requirements. For information about the solving this problem, see the TechNet wiki , Troubleshooting SQL Server 2008 R2 Reporting Services AdventureWorks Sample Reports, available at http://social.technet.microsoft.com/wiki/contents/articles/troubleshooting-sql-server-2008-r2-reporting-services-adventureworks-sample-reports.aspx.
This resolution applies to the SQL Server 2005 and SQL Server 2008 versions of the AdventureWorks sample reports as well.

mariwill Jul 9, 2010 at 11:02 PM 
The AdventureWorks 2008R2 databases are available for download at http://msftdbprodsamples.codeplex.com/releases/view/45907.
Make sure the instance of the SQL Server Database Engine you install the AdventureWorks 2008R2 databases on meets the requirements specified at http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites&referringTitle=Installing%20SQL%20Server%202008R2%20Databases. If the requirements are not met, some of the databases might not install.
For more information about downloads of specific versions of samples, go to http://social.technet.microsoft.com/wiki/contents/articles/ssrs-reporting-services-samples-on-codeplex.aspx

SS86 Jun 19, 2010 at 7:10 PM 
OK, I ignored the notice about not having FILESTREAM enabled. Once I followed the prerequisite instructions at http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites&referringTitle=Home and enabled FILESTREAM, then reran the sample db installer all is well with the world.
<p/>
Chalk it up as a failure to RTFM.
<p/>

SS86 Jun 19, 2010 at 6:46 PM 
The R2 reports are looking for db AdventureWorks2008R2, which isn't packaged with the sample databases in the AdventureWorks2008R2_RTM.exe found at http://msftdbprodsamples.codeplex.com/releases/view/45907. Only the AW, AWDW and AWLT versions are packaged.

sundanceca Jun 14, 2010 at 9:00 PM 
These reports refer to a database that is not part of the most recent Sql 08 R2 sample databases. Specifically, AdventureWorks2008R2 is not one of the samples installed with the latest Sql Samples. Hence, when you install the Sql Server DB samples, and these SSRS samples, none of the reports work. I ran the report queries against all the sample dbs installed as part of the Sql 08 R2 sample dbs, and none of them worked. I had to go back and dig through my backup files and locate an older version of AdventureWorks2008R2 db and restore it. It used to be on Codeplex, but now 'tis gone.