Conditional Formatting Question RS 2005

Hi,

Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).

I have tried approaching it in two ways. One was with a case statement as follows:

CASE

WHEN TargetPer < 83 THEN 'Red'

WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'

WHEN TargetPer >= 90 THEN 'Green'

END AS BGColor

TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:

=Fields!BGColor.Value

Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)

The other approach i have used is the following expression to set the background colour

=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))

For this i still have no yellows just reds and greens!

I am using a matrix report that has subgroups on the columns for table!

Any ideas anyone?

Thanks

Marek

You have 2 options to implement CASE logic. Using nested Iif() calls or using the Switch() method. The definition is Switch(<condition>, <value>[,<condition>, <value>],....). So for the first condition that evaluates to true it's corresponding value is returned.

In your case the problem is that you are not applying any aggregation function around your field references. Any groupping naturally means that some aggregation will be applied. If not specified, the default aggregation function is First() rather than Sum(). Also you seem to have Fields!BGColor.Value in your condition which I believe should be Fields!TargetPer.Value.

So the way I see it you have 2 options to re-write your statement:

Using Iif():

=Iif

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Iif

( Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, "Red"

)

)

Using Switch():

=Switch

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, Sum(Fields!TargetPer.Value) < 83 'You could also put True here (CASE ELSE)

, "Red"

)