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.