Export (0) Print
Expand All

Sample Report - Employee Sales Summary

SQL Server 2000

The Company Sales report displays 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.

Data

This report uses a several datasets to provide data to the various charts and tables within the report. Each dataset uses on the AdventureWorks shared data source, and the queries in each dataset are based on tables from the AdventureWorks2000 database.

The EmpSalesYearOverYear dataset contains a query that retrieves summary sales data by employee and date, with a query parameter that filter data by employee. It is used by the Sales Comparison chart. The dataset returns the following fields.

Field Description
Employee Name of employee
OrderYear Order year, calculated in query based on order date
OrderMonthNum Number of order month, calculated in query based on order date
OrderMonth Name of order month, calculated in query based on order date
Sales Product sales

The EmpSalesMonth dataset contains a query that retrieves summary sales data by employee, product category, and date, with query parameters that filter data by date and employee. It is used by the Current Month Sales Comparison chart. The dataset returns the following fields.

Field Description
Employee Name of employee
OrderYear Order year, calculated in query based on order date
OrderMonthNum Number of order month, calculated in query based on order date
OrderMonth Name of order month, calculated in query based on order date
ProdCat Name of product category
Sales Product sales

The EmpSalesDetail dataset contains a query that retrieves detail sales data by employee, product subcategory, and date, with query parameters that filter data by date and employee. It is used by the Current Month Order Summary table. The dataset returns the following fields.

Field Description
Employee Name of employee
OrderMonthNum Number of order month, calculated in query based on order date
SubCat Name of product subcategory
Sales Product sales
SalesOrderNumber Sales order number
Product Name of product
OrderQty Quantity of product ordered
UnitPrice Single unit price for product
ProdCat Name of product category

The SalesEmps dataset contains a query that retrieves a list of employees. It is used to populate the list of employees for the Employee parameter. The dataset returns the following fields.

Field Description
EmployeeID ID number of employee
Employee Name of employee

For information about using datasets, see Querying a Data Source.

The AdventureWorks shared data source contains connection information for the AdventureWorks database. By default, this data source uses the AdventureWorks2000 database on the local computer, but you can change it to use a database on a different computer. Because the data source is shared, the same connection information can be used by all reports that use the data source. For information about data sources, see Connecting to a Data Source.

Layout and Report Features

The general layout of this report consists of a set of text boxes for the report title, two charts, and a table. Some cells in the table provide drillthrough functionality to open the Sales Order Detail report when the user clicks on a sales order. The report uses parameters to filter data by date and employee.

Charts

The first chart is the Sales Comparison line chart. This chart uses the EmpSalesYearOverYear dataset. The category (x) axis of the chart displays months, the value (y) axis displays sales, and the data series is based on data from each year.

The second chart is the Current Month Sales Comparison bar chart. This chart uses the EmpSalesMonth dataset. The category (x) axis of the chart displays product category, the value (y) axis displays sales, and the data series is based on the current year.

For information about charts, see Adding a Chart.

Tables

The final data region in the report is Current Month Order Summary table. This table uses the EmpSalesDetail dataset. The table lists the products that the employee sold by individual sales order. For information about tables, see Adding a Table.

Drillthrough

The OrderNumber text box in the table includes a drillthrough action that enables a link from this report to the Sales Order Detail report. The drillthrough action contains the name of the target report and the name of the parameter on the target report. When the user clicks on the sales order, another report is opened which uses the selected sales number as a parameter value. For information about drillthrough links, see Adding a Drillthrough Report Link.

Parameters

The Employee Sales Summary report uses three parameters to filter the data within the report: ReportYear, ReportMonth, and EmpID. When values are supplied for these report parameters, the values are passed to the query parameters behind each of the data regions and the data retrieved by the report is filtered by these parameters. The parameters contain the following:

  • Available values. The user can select from a static list of values for ReportYear and ReportMonth. These values are defined within each of these parameters. For the EmpID parameter, the available values are from a dynamic list based on the query in the SalesEmps dataset.
  • Labels. Each of the three parameters use a value/label pair; for example, the EmpID parameter takes the employee ID as a value but displays the employee name as a label to the user.
  • Default values. The ReportYear and ReportMonth parameters also have a default value; those values are automatically populated when the user runs the report, but can also be changed by the user. The EmpID parameter does not have a default value. When the report is run, users must select an employee before viewing the report.

The parameters are also used in the title of the report. For example, the month and year of the report are included on the page through the following text box expression:

=MonthName(Parameters!ReportMonth.Value) & " " & Parameters!ReportYear.Value & " Sales Report"

The selected employee name is included through the following text box expression:

=Parameters!EmpID.Label

For information about parameters, see Using Parameters in a Report.

See Also

Reporting Services Sample Reports

Show:
© 2014 Microsoft