**************************************************************************** Location : Microsoft Reporting Services Host : bnbdatabase1 Path : Employee_Sales_Summary_2008R2 **************************************************************************** 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