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

The expression for the sales first set of indicators (the images column after total sales column) calls a
Visual Basic function to apply the KPI threshold banding. Figure 7-27 shows this custom code.
Figure 7-27
Since the image names for the green, yellow, and red indicators are Green, Yellow, and Red, these values
are simply returned in the Value property of the image item using the following expression:
=Code.SalesQuotaVarianceIndicator(Sum(Fields!ExtendedAmountSum.Value),
Sum(Fields!SalesAmountQuotaSum.Value))
For variety, I've resolved the second indicator column images using only an in-line expression rather than
using a custom function. This is the expression for the header row. The detail row expression is the same
but without the SUM() function. As a rule, once I've decided to use custom code, I'll typically continue to
use custom functions for all but the simplest expressions so that I can keep business logic in one place.
=IIF(Sum(Fields!ExtendedAmountSum.Value) /
Sum(Fields!ExtendedAmountSumLastYear.Value) < .8, “exclamation_small”, “nothing”)
This expression returns the name of an exclamation mark icon image when this year's sales amount is
less than 80% of last year's. I created an image file called “nothing,” which is a blank icon with a white
background. Using this image effectively displays nothing in the image cell.
Synchronizing Charts and Related Report Items
One of the great advantages to the scorecard approach is that all the information is presented in a con-
cise page. In order to make the best use of screen space, I can use a separate report item to show content
related to the item selected in the scorecard.
Figure 7-28 shows the table and chart items. When I select a sales territory, by clicking on the small pie
chart icon in the first column, I want to see sales trend information in a column chart. I've placed a chart