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 .
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 ***/
, DimEmployee.FirstName + ' ' + DimEmployee.LastName AS EmployeeName
, 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
, DimEmployee.FirstName + ' ' + DimEmployee.LastName
) AS ThisYearSales
/*** Last Year Sales ***/
, 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
) AS LastYearSales
ON ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey
AND LastYearSales.CalendarYear = ThisYearSales.CalendarYear - 1
FULL OUTER JOIN
/*** Sales Quota ***/
, SUM(FactSalesQuota.SalesAmountQuota) AS SalesAmountQuota
INNER JOIN DimEmployee AS DimEmployee_2
ON FactSalesQuota.EmployeeKey = DimEmployee_2.EmployeeKey
) AS SalesQuota
ON ThisYearSales.EmployeeKey = SalesQuota.EmployeeKey
AND ThisYearSales.CalendarYear = SalesQuota.CalendarYear
· 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:
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
Case Is < .85
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.