Wrox Programmer Forums
BOOK: Professional Microsoft SQL Server 2008 Reporting Services ISBN: 978-0-470-24201-8
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Reporting Services by Paul Turley, Thiago Silva, Bryan C. Smith, Ken Withee; ISBN: 9780470242018
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Microsoft SQL Server 2008 Reporting Services ISBN: 978-0-470-24201-8 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 19th, 2010, 03:10 PM
Authorized User
Join Date: Sep 2009
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Figure 10-9: KPI Scorecard

The issues discussed beginning on page 362 are illustrated in the sample report “Chapter 10 Reports / KPI Scorecard” contained in the downloadable samples. Examination of the rendered report in Figure 10-9 (page 366) indicates some problems. The most obvious is that the magnitude of many of the quota and actual sales amounts is in the billions and trillions of dollars, which is not intuitively reasonable.

Closer inspection in Report Designer shows further problems with the underlying data set. In particular, the query relates sales quotas to territory of the sale when, based upon the FactSalesQuota table, quotas are related only to employee and calendar period (year/quarter).

Also, the dataset query does not return a row for the first year of sales for each employee. From a business requirement perspective, there are multiple problems with this. First, the sales quota variance for this year is not included. Furthermore, totals by sales territory region are incomplete. Lastly, when looking at the Year Variance column, there is no displayed value to compare with the second employee sales year (thefirst year actually shown).

I have modified the report as follows:

· Tablix data set query:

o Change join of derived tables ThisYearSales and LastYearSales from INNER to LEFT JOIN to include first year of employee sales. (For the first year of sales for an employee from ThisYearSales, there is no row in LastYearSales based on the join criteria.)

o Add new derived table, SalesQuota, to generate quota amounts rather than generating them within the ThisYearSales derived table. OUTER JOIN with ThisYearSales allows for employee having a sales quota in a year with no actual sales or sales where a quota was not established. (Sales quota and amount values have a different granularity in the underlying data warehouse. Generating these in the same derived table causes incorrect aggregation.)

o Aggregate employee/year sales quota and sales amounts within each derived table, rather than after the join of these tables.

o Generate sales territory from DimEmployee (vs FastResellerSales) because there are some employee sales in multiple territories in same year. Also, the granularity of sales quota in FactSalesQuota includes employee and calendar/year but not territory. Based on this new join criterion, there are several employees assigned to a non-specified territory (NA).

 KPI Scorecard - Revised data set query - rmb99 - 01/18/10
 ,ThisYearSales.CalendarYear AS CalendarYear
 ,ThisYearSales.SalesAmt AS ExtendedAmountSum
 ,SalesQuota.SalesAmountQuota AS SalesAmountQuotaSum
 ,LastYearSales.SalesAmt AS ExtendedAmountSumLastYear
/*** This Year Sales ***/
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName AS EmployeeName
       , DimDate.CalendarYear
       , SUM(FactResellerSales.ExtendedAmount) AS SalesAmt
       INNER JOIN DimEmployee 
             ON FactResellerSales.EmployeeKey = DimEmployee.EmployeeKey 
       INNER JOIN DimSalesTerritory 
             ON DimEmployee.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey
       INNER JOIN DimDate 
             ON FactResellerSales.OrderDateKey = DimDate.DateKey
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName
       , DimDate.CalendarYear
 ) AS ThisYearSales 
/*** Last Year Sales ***/
       , DimEmployee_1.EmployeeKey
       , DimDate_1.CalendarYear
       , SUM(FactResellerSales_1.ExtendedAmount) AS SalesAmt
       FactResellerSales AS FactResellerSales_1 
       INNER JOIN DimEmployee AS DimEmployee_1 
             ON FactResellerSales_1.EmployeeKey = DimEmployee_1.EmployeeKey
       INNER JOIN DimSalesTerritory AS DimSalesTerritory_1 
             ON DimSalesTerritory_1.SalesTerritoryKey = DimEmployee_1.SalesTerritoryKey
       INNER JOIN DimDate AS DimDate_1 
             ON FactResellerSales_1.OrderDateKey = DimDate_1.DateKey
       , DimEmployee_1.EmployeeKey
 , DimDate_1.CalendarYear
 ) AS LastYearSales
ON ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
 AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey
 AND LastYearSales.CalendarYear = ThisYearSales.CalendarYear - 1 
/*** Sales Quota ***/
       , FactSalesQuota.CalendarYear
       , SUM(FactSalesQuota.SalesAmountQuota) AS SalesAmountQuota
       INNER JOIN DimEmployee AS DimEmployee_2 
             ON FactSalesQuota.EmployeeKey = DimEmployee_2.EmployeeKey
       , FactSalesQuota.CalendarYear
 ) AS SalesQuota
ON ThisYearSales.EmployeeKey = SalesQuota.EmployeeKey
 AND ThisYearSales.CalendarYear = SalesQuota.CalendarYear
 , ThisYearSales.CalendarYear
 , ThisYearSales.EmployeeName
· When each employee first sales year is included in the data set, the logic for the yearly sales variance indicator (to right of the Year Variance column) must display a blank when last year sales are zero. The expression has been modified as follows, and includes nested IIF logic:

,iif(Fields!ExtendedAmountSum.Value /iif(isnothing(Fields!ExtendedAmountSumLastYear.Value),.000001,Fields!ExtendedAmountSumLastYear.Value) < .8
Note that all values within the conditions tested by IIF statements are evaluated before a value is assigned to the entire expression. Therefore, it is necessary to conditionally replace a null value of Fields!ExtendedAmountSumLastYear.Value with a non-zero dummy value (0.000001) when it is the denominator to avoid dividing by zero and causing an invalid image to be displayed. (An alternative would be to create a division function using custom code that could be called by the indicator expression.)

· The sales quotas and amounts in the data mart do not cause the sales quota variance indicator to the right of the Total Sales column to be other than a yellow triangle. To better illustrate different conditions, I modified the custom code in report properties that is called by this indicator as follows:

Function SalesQuotaVarianceIndicator(ActualSales As Decimal, Quota As Decimal) As String
Select Case ActualSales/Quota
Case Is > 1.00
Return "green"
Case Is < .85
Return "red"
Case Else
Return "yellow"
End Select
End Function
The indicator is green if annual employee sales are greater than the quota (rather than 125%), red if less than 85% (vs. 75%) and otherwise yellow. Only two employees in the data mart exceeded their quotas in any year. Otherwise, employees and region totals have either a red or yellow indicator. In an actual report, this logic would be determined by the business rules but this change better illustrates the utility of the indicator.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Editing KPI functionality boneill SharePoint Development 0 July 14th, 2008 01:20 PM
BSM scorecard page filter problem saikrishna_80 SharePoint Development 1 February 8th, 2007 04:40 PM
Reporting Services and Business Scorecard Accelera lidsurfer BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 May 10th, 2005 10:46 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.