Report Information
Description Purpose: Summary report. 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, multivalue parameters, and parameters to control conditional visibility of text boxes and toggle rows.
Author Mary Lingel MSFT
ReportID 602b0e43-347c-4a5b-8326-d0c562c9ddcb
Data Sources
AdventureWorks2008R2
Name AdventureWorks2008R2
Security Type None
Datasets
EmployeeSalesYearOverYear2008R2
Name EmployeeSalesYearOverYear2008R2
Fields
Employee
Name Employee
Source Employee
Data Type String
Year
Name Year
Source Year
Data Type Int32
MonthNumber
Name MonthNumber
Source MonthNumber
Data Type Int32
Month
Name Month
Source Month
Data Type String
Sales
Name Sales
Source Sales
Data Type Decimal
EmployeeSalesDetail2008R2
Name EmployeeSalesDetail2008R2
Fields
Employee
Name Employee
Source Employee
Data Type String
Subcategory
Name Subcategory
Source Subcategory
Data Type String
Sales
Name Sales
Source Sales
Data Type Decimal
SalesOrderID
Name SalesOrderID
Source SalesOrderID
Data Type Int32
SalesOrderNumber
Name SalesOrderNumber
Source SalesOrderNumber
Data Type String
Product
Name Product
Source Product
Data Type String
OrderQty
Name OrderQty
Source OrderQty
Data Type Int32
UnitPrice
Name UnitPrice
Source UnitPrice
Data Type Decimal
Category
Name Category
Source Category
Data Type String
EmpSalesMonth2008R2
Name EmpSalesMonth2008R2
Fields
Employee
Name Employee
Source Employee
Data Type String
Year
Name Year
Source Year
Data Type Int32
MonthNumber
Name MonthNumber
Source MonthNumber
Data Type Int32
Month
Name Month
Source Month
Data Type String
Category
Name Category
Source Category
Data Type String
Sales
Name Sales
Source Sales
Data Type Decimal
SalesEmployees2008R2
Name SalesEmployees2008R2
Fields
BusinessEntityID
Name BusinessEntityID
Source BusinessEntityID
Data Type Int32
Employee
Name Employee
Source Employee
Data Type String
JobTitle
Name JobTitle
Source JobTitle
Data Type String
CategoryQuotas
Name CategoryQuotas
Query
Data Source AdventureWorks2008R2
CommandText SELECT 1 as ID, 'Accessories' as Category,
1000 as Quota, 25 as PercentDeviation
UNION SELECT 2 as ID, 'Bikes' as Category,
70000 as Quota, 5 as PercentDeviation
UNION SELECT 3 as ID, 'Clothing' as Category,
2500 as Quota, 20 as PercentDeviation
UNION SELECT 4 as ID, 'Components' as Category,
20000 as Quota, 10 as PercentDeviation
Parameters
Fields
ID
Name ID
Source ID
Data Type Int32
Category
Name Category
Source Category
Data Type String
Quota
Name Quota
Source Quota
Data Type Int32
PercentDeviation
Name PercentDeviation
Source PercentDeviation
Data Type Int32
QuotaF
Name QuotaF
Source =CDbl(Fields!Quota.Value)
Parameters
ReportMonth
Name ReportMonth
Data Type Integer
Prompt Report Month
Default Value
Dataset Reference
Values 11
Valid Value
Dataset Reference
Values January : 1
February : 2
March : 3
April : 4
May : 5
June : 6
July : 7
August : 8
September : 9
October : 10
November : 11
December : 12
ReportYear
Name ReportYear
Data Type Integer
Prompt Report Year
Default Value
Dataset Reference
Values 2007
Valid Value
Dataset Reference
EmployeeID
Name EmployeeID
Data Type Integer
Prompt Employee
Default Value
Dataset Reference
Values 283
Valid Value
Dataset Reference
Dataset Name SalesEmployees2008R2
Label Employee
Value BusinessEntityID
ShowAll
Name ShowAll
Data Type Boolean
Prompt Show all information?
Default Value
Dataset Reference
Values false
Valid Value
Dataset Reference
ExpandAllTableRows
Name ExpandAllTableRows
Data Type Boolean
Prompt Expand all table rows?
Default Value
Dataset Reference
Values false
Valid Value
Dataset Reference
CategoryQuota
Name CategoryQuota
Data Type Float
Prompt Category quotas (Accessories, Bikes, Clothing, Components):
MultiValue true
Default Value
Dataset Reference
Dataset Name CategoryQuotas
Value QuotaF
Valid Value
Dataset Reference
Embedded Images
AdventureWorksLogo
Name AdventureWorksLogo
Type jpeg
Custom Code
Private colorPalette As String() = {"Blue", "Orange", "Green"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
If groupingValue = Nothing Then
Return "Black"
End If
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function
Variables
SumofAllParameterThresholds
Name SumofAllParameterThresholds
Value =CDbl(Parameters!CategoryQuota.Value(0)+Parameters!CategoryQuota.Value(1)+Parameters!CategoryQuota.Value(2)+Parameters!CategoryQuota.Value(3))
Page Header
Report Items
Logo
Name Logo
Type Image
Source Type Embedded Image Name
Embedded Image Name AdventureWorksLogo
ReportName
Name ReportName
Type Text Box
Content =Globals!ReportName
Textbox5
Name Textbox5
Type Text Box
Content Sales Report for =Parameters!EmployeeID.Label
=Parameters!ReportMonth.Label, =Parameters!ReportYear.Value
Body
Report Items
tbToggle
Name tbToggle
Type Text Box
Content Click + to view the scenario story.
tbScenarioText
Name tbScenarioText
Type Text Box
Content As a salesperson for AdventureWorks, I want to know how my sales compare to seasonal sales in previous years.
The line and sparkline charts show year over year sales for all categories .
The matrix with indicators shows current sales compared to quotas. Change quota values on the reportviewer toolbar.
The column and databar charts show sales in each category.
Visibility
Show or hide based on expression true
Display can be toggled by this report item tbToggle
Rectangle1
Name Rectangle1
Type Rectangle
Visibility
Show or hide based on expression =NOT Parameters!ShowAll.Value
ParameterValues
Name ParameterValues
Type Text Box
Content Report Month: [@ReportMonth] [@ReportMonth.Label]
Report Year: =Parameters!ReportYear.Value
EmployeeID: [@EmployeeID] [@EmployeeID.Label]
DataSourceandDatasets
Name DataSourceandDatasets
Type Text Box
Content Data Source: =DataSources!AdventureWorks2008R2.DataSourceReference
Dataset EmployeeSalesYearOverYear2008R2: =DataSets!EmployeeSalesYearOverYear2008R2.CommandText
Dataset EmployeeSalesDetail2008R2: =DataSets!EmployeeSalesDetail2008R2.CommandText
Dataset EmpSalesMonth2008R2: =DataSets!EmpSalesMonth2008R2.CommandText
Dataset SalesEmployees2008R2: =DataSets!SalesEmployees2008R2.CommandText
rectCharts
Name rectCharts
Type Rectangle
tbChartPurpose
Name tbChartPurpose
Type Text Box
Content Compare seasonal sales by year.
Textbox7
Name Textbox7
Type Text Box
Content Year
Sales
Name Sales
Type Text Box
Content =Sum(Fields!Sales.Value)
Textbox3
Name Textbox3
Type Text Box
Content Sales
Textbox8
Name Textbox8
Type Text Box
Year
Name Year
Type Text Box
Content =Fields!Year.Value
rectMatrix
Name rectMatrix
Type Rectangle
tbChartPurpose2
Name tbChartPurpose2
Type Text Box
Content Compare sales to quotas by category.
rectMatrixCorner
Name rectMatrixCorner
Type Rectangle
Textbox21
Name Textbox21
Type Text Box
Content Met
Quota?
tbMatrixQuotas
Name tbMatrixQuotas
Type Text Box
Content Quota: =Variables!SumofAllParameterThresholds.Value
Total: =Sum(Fields!Sales.Value)
Visibility
Show or hide based on expression =NOT Parameters!ShowAll.Value
Textbox9
Name Textbox9
Type Text Box
Content Sales Order Number
Sales2
Name Sales2
Type Text Box
Content =Sum(Fields!Sales.Value)
rectCategoryIndicator
Name rectCategoryIndicator
Type Rectangle
Sales1
Name Sales1
Type Text Box
Content =Sum(Fields!Sales.Value)
Textbox24
Name Textbox24
Type Text Box
Textbox13
Name Textbox13
Type Text Box
Content =Sum(Fields!Sales.Value)
Textbox25
Name Textbox25
Type Text Box
Textbox2
Name Textbox2
Type Text Box
Content Sales
Textbox22
Name Textbox22
Type Text Box
Content Met Category Quota?
Category
Name Category
Type Text Box
Content =Fields!Category.Value
rectCategoryQuotas
Name rectCategoryQuotas
Type Rectangle
tbCategoryQuotas
Name tbCategoryQuotas
Type Text Box
Content Quota: =CDbl(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))
-=FormatPercent(Variables!PercentDeviation.Value,0): =(1.0-Variables!PercentDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))
+=FormatPercent(Variables!PercentDeviation.Value,0): =(1.0+Variables!PercentDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))
SalesOrderNumber
Name SalesOrderNumber
Type Text Box
Content =Fields!SalesOrderNumber.Value
Actions
Go to Report Sales_Order_Detail_2008R2
Go to Report Parameters SalesOrderIDStart=Fields!SalesOrderID.Value
SalesOrderIDEnd=Fields!SalesOrderID.Value
Textbox11
Name Textbox11
Type Text Box
Content Total
Textbox12
Name Textbox12
Type Text Box
Content =Sum(Fields!Sales.Value)
rectSeasonalSales
Name rectSeasonalSales
Type Rectangle
Textbox30
Name Textbox30
Type Text Box
Content Category
Textbox28
Name Textbox28
Type Text Box
Content =Parameters!ReportMonth.Label & " Sales Comparison for each Year"
Category2
Name Category2
Type Text Box
Content =Fields!Category.Value
tbChartPurpose3
Name tbChartPurpose3
Type Text Box
Content Compare seasonal sales for =Parameters!ReportMonth.Label for each category.
Data Regions
Tablix1
Name Tablix1
Type Matrix
Column Member 1
ColumnGroup
Name ColumnGroup
Column Member 2
Row Member 1
Year
Name Year
Expressions =Fields!Year.Value
Sort Expressions =Fields!Year.Value
Source Dataset EmployeeSalesYearOverYear2008R2
Tablix2
Name Tablix2
Type Matrix
Column Member 1
ColumnGroup1
Name ColumnGroup1
Column Member 2
Row Member 1
Category
Name Category
Expressions =Fields!Category.Value
Sort Expressions =Fields!Category.Value
Row Member 1
Row Member 2
SalesOrderNumber
Name SalesOrderNumber
Expressions =Fields!SalesOrderNumber.Value
Sort Expressions =Fields!SalesOrderNumber.Value
Row Member 4
Row Member 4
Source Dataset EmployeeSalesDetail2008R2
Tablix3
Name Tablix3
Type Matrix
Column Member 1
ColumnGroup2
Name ColumnGroup2
Row Member 1
Category1
Name Category1
Expressions =Fields!Category.Value
Sort Expressions =Fields!Category.Value
Source Dataset EmpSalesMonth2008R2
Data Visualizations
Chart1
Name Chart1
Type Chart
Category Member 1
Chart1_CategoryGroup
Name Chart1_CategoryGroup
Expressions =Fields!Month.Value
Sort Expressions =Fields!MonthNumber.Value
Label =Fields!Month.Value
Series Member 1
Chart1_SeriesGroup
Name Chart1_SeriesGroup
Expressions =Fields!Year.Value
Sort Expressions =Fields!Year.Value
Label =Fields!Year.Value
Data Series Sales
Name Sales
Chart Area Default
Name Default
Primary Category Axis
Name Primary
Secondary Category Axis
Name Secondary
Caption Axis Title
Primary Value Axis
Name Primary
Caption In Thousands
Secondary Value Axis
Name Secondary
Caption Axis Title
No Data Message
Name NoDataMessage
Caption No Data Available
Source Dataset EmployeeSalesYearOverYear2008R2
Sparkline1
Name Sparkline1
Type Sparkline
Category Member 1
Sparkline1_CategoryGroup
Name Sparkline1_CategoryGroup
Expressions =Fields!Month.Value
Sort Expressions =Fields!MonthNumber.Value
Label =Fields!Month.Value
Series Member 1
Label Sales
Data Series Sales
Name Sales
Chart Area Default
Name Default
Primary Category Axis
Name Primary
Caption Axis Title
Secondary Category Axis
Name Secondary
Caption Axis Title
Primary Value Axis
Name Primary
Caption Axis Title
Secondary Value Axis
Name Secondary
Caption Axis Title
No Data Message
Name NoDataMessage
Caption No Data Available
Source Dataset EmployeeSalesYearOverYear2008R2
GaugePanel1
Name GaugePanel1
Type Indicator
Source Dataset EmployeeSalesDetail2008R2
GaugePanel2
Name GaugePanel2
Type Indicator
Source Dataset EmployeeSalesDetail2008R2
Chart2
Name Chart2
Type Chart
Category Member 1
Chart2_CategoryGroup
Name Chart2_CategoryGroup
Expressions =Fields!Category.Value
Sort Expressions =Fields!Category.Value
Label =Fields!Category.Value
Series Member 1
Chart2_SeriesGroup
Name Chart2_SeriesGroup
Expressions =Fields!Year.Value
Sort Expressions =Fields!Year.Value
Label =Fields!Year.Value
Data Series Sales
Name Sales
Chart Area Default
Name Default
Primary Category Axis
Name Primary
Secondary Category Axis
Name Secondary
Caption Axis Title
Primary Value Axis
Name Primary
Caption In Thousands
Secondary Value Axis
Name Secondary
Caption Axis Title
No Data Message
Name NoDataMessage
Caption No Data Available
Source Dataset EmpSalesMonth2008R2
DataBar1
Name DataBar1
Type Data Bar
Category Member 1
Series Member 1
DataBar1_SeriesGroup
Name DataBar1_SeriesGroup
Expressions =Fields!Year.Value
Sort Expressions =Fields!Year.Value
Label =Fields!Year.Value
Data Series Sales
Name Sales
Chart Area Default
Name Default
Primary Category Axis
Name Primary
Caption Axis Title
Secondary Category Axis
Name Secondary
Caption Axis Title
Primary Value Axis
Name Primary
Caption Axis Title
Secondary Value Axis
Name Secondary
Caption Axis Title
No Data Message
Name NoDataMessage
Caption No Data Available
Source Dataset EmpSalesMonth2008R2
Page Footer
Report Items
tbDescription
Name tbDescription
Type Text Box
Content Purpose: Summary report. 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, multivalue parameters, and parameters to control conditional visibility of text boxes and toggle rows.
Visibility
Show or hide based on expression =NOT Parameters!ShowAll.Value