Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 293

AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey
AND ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
GROUP BY ThisYearSales.SalesTerritoryRegion, ThisYearSales.SalesTerritoryKey
, ThisYearSales.CalendarYear, ThisYearSales.LastName, ThisYearSales.FirstName
, ThisYearSales.EmployeeName
ORDER BY ThisYearSales.SalesTerritoryRegion, ThisYearSales.CalendarYear
, ThisYearSales.LastName, ThisYearSales.FirstName
When running complex queries like this one, you may need to increase the default connection timeout
setting on the data source. The default setting is 15 seconds, which may not be sufficient for this query
on all hardware. In a production application with data volumes greater than the sample database, I
would recommend testing query performance and possibly using an Analysis Services database with
cubes and precalculated aggregates. To populate the data warehouse, you will use queries similar to this
one and store the results for later retrieval.
Figure 7-26 shows a simple table with two groups, on the SalesTerritory and CalendarYear fields.
This table is much like several previous examples. The detail row is hidden by default, allowing for drill-
down using the SalesTerritoryRegion text box. Two more images will serve as indicators. These are
based on expressions used to change the indicator image.
Figure 7-26
You will notice that the images have a white background even though I've used background colors to
separate the rows. I've only done this to simplify this example. I have simply added the images to the
cells in the table header. If you want to use transparent images over a colored or shaded background,
you will need to add rectangles to the header cells and then place images in the rectangles. This way,
you can set the BackgroundColor property for each rectangle and take advantage of the image trans-
parency. The final example, shown in Figure 7-30, uses this technique to fill the background color behind
the scorecard indicator images.
Looking at the columns with text headers, the first column contains the SalesTerritoryRegion field
in the first group header and the CalendarYear field in the detail row.
The second column contains the EmployeeName in the detail row.
The third text column is for the SalesAmountQuota field. The header uses the SUM() function to aggre-
gate the details for the sales territory.
The forth text column contains total sales values, using the ExtendedAmount field.
The last column of text boxes, labeled Yr. Variance, calculates the total sales amount annual variance. In
the header row, the expression uses the SUM() function. In the detail row, the SUM() function is omitted.
=1-(Sum(Fields!ExtendedAmountSumLastYear.Value)/
Sum(Fields!ExtendedAmountSum.Value))