Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 19th, 2010, 02: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).

Code:
/***
 
 KPI Scorecard - Revised data set query - rmb99 - 01/18/10
 
***/
 
SELECT 
 ThisYearSales.SalesTerritoryRegion
 ,ThisYearSales.SalesTerritoryKey
 ,ThisYearSales.CalendarYear AS CalendarYear
 ,ThisYearSales.EmployeeName
 ,ThisYearSales.SalesAmt AS ExtendedAmountSum
 ,SalesQuota.SalesAmountQuota AS SalesAmountQuotaSum
 ,LastYearSales.SalesAmt AS ExtendedAmountSumLastYear
 
FROM
 
/*** This Year Sales ***/
 
 (SELECT 
       DimSalesTerritory.SalesTerritoryKey
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName AS EmployeeName
       , DimDate.CalendarYear
       , SUM(FactResellerSales.ExtendedAmount) AS SalesAmt
 
 FROM 
       FactResellerSales 
       INNER JOIN DimEmployee 
             ON FactResellerSales.EmployeeKey = DimEmployee.EmployeeKey 
       INNER JOIN DimSalesTerritory 
             ON DimEmployee.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey
       INNER JOIN DimDate 
             ON FactResellerSales.OrderDateKey = DimDate.DateKey
 
 GROUP BY 
       DimSalesTerritory.SalesTerritoryKey
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName
       , DimDate.CalendarYear
 
 ) AS ThisYearSales 
 
LEFT JOIN
 
/*** Last Year Sales ***/
 
 (SELECT  
       DimSalesTerritory_1.SalesTerritoryKey
       , DimEmployee_1.EmployeeKey
       , DimDate_1.CalendarYear
       , SUM(FactResellerSales_1.ExtendedAmount) AS SalesAmt
 
 FROM 
       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
 
GROUP BY 
DimSalesTerritory_1.SalesTerritoryKey
       , DimEmployee_1.EmployeeKey
 , DimDate_1.CalendarYear
 
 ) AS LastYearSales
 
ON ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
 AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey
 AND LastYearSales.CalendarYear = ThisYearSales.CalendarYear - 1 
 
FULL OUTER JOIN
 
/*** Sales Quota ***/
 
 (SELECT 
       DimEmployee_2.EmployeeKey
       , FactSalesQuota.CalendarYear
       , SUM(FactSalesQuota.SalesAmountQuota) AS SalesAmountQuota
 
 FROM 
       FactSalesQuota 
       INNER JOIN DimEmployee AS DimEmployee_2 
             ON FactSalesQuota.EmployeeKey = DimEmployee_2.EmployeeKey
 
GROUP BY 
       DimEmployee_2.EmployeeKey
       , FactSalesQuota.CalendarYear
 ) AS SalesQuota
 
ON ThisYearSales.EmployeeKey = SalesQuota.EmployeeKey
 AND ThisYearSales.CalendarYear = SalesQuota.CalendarYear
 
ORDER BY 
 ThisYearSales.SalesTerritoryRegion
 , 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:

Code:
=iif(isnothing(Fields!ExtendedAmountSumLastYear.Value)
,nothing
,iif(Fields!ExtendedAmountSum.Value /iif(isnothing(Fields!ExtendedAmountSumLastYear.Value),.000001,Fields!ExtendedAmountSumLastYear.Value) < .8
,"indicator_yellow_16"
,nothing))
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:

Code:
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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 03: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



All times are GMT -4. The time now is 08:37 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.