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"

)

Conditional Formatting - not so conditional?

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .What part of the matrix do you have this in, and what values show in the textbox?|||OK, well you pointed me right to my problem. The values are percentages. I was doing my conditional formatting based on 3 instead of .03 . . .

That is fixed but I still have something else wrong. A very small number of values that are not .03 are coloring red. I am also getting a warning that says "[rsNonAggregateInMatrixCell] The Color expression for the textbox ‘textbox22’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling."

I assume this message is related to my problem.

Here is a sample of what the output is looking like. All of the values formatted correctly except for the 2.60 %. The 2.60% was colored red as if it was >= .03. I know you can't see the colors here:

WRAP

3.50 %

1.22 %

1.15 %

3.13 %

2.43 %

2.60 %

14.21 %

0.00 %

8.41 %

6.14 %

5.23 %

5.23 %

2.42 %

2.42 %

1.87 %

1.87 %

2.39 %

2.39 %

4.17 %

4.17 %

3.15 %

|||

A matrix cell is always in the intersection of a row and a column group. Usually, several detail rows will go into the same matrix cell instance based on the row/column grouping. That's the reason why you get the warning. By just referencing a field without aggregate function (=iif(Fields!Wrap.Value >= 0.03 , "Red", "Green") ), you reference just the field value of the first row in that cell - while the textbox in the cell may actually show the aggregate of the values.

You should use the following color expression instead: =iif(Sum(Fields!Wrap.Value) >= 0.03 , "Red", "Green")

-- Robert

|||I made the change that you suggested. Which eliminates the warning that I was receiving. However, I am still getting inconsistent results with the formatting. For instance, of the following values only 5.09, and the two 0.00's are colored green.

Could it be because I have enabled drill-down in this report? Even if I drill down to the lowest level the colors still are not consistently populating based on the conditions in this formula.

WRAP

5.09 %

2.73 %

0.00 %

0.00 %

1.77 %

2.55 %

3.61 %

6.68 %

3.02 %

3.50 %

2.86 %

Conditional Formatting

Hi all
Does anyone know how I can have conditional formatting in my reports?
What I want to achieve is something like. If the field text = "Green" make
the background green, etc
Thanks alot in advance
Ruse the iif function for the font color. Reporting Services accepts
expressions for most of its properties.
=iif(boolean condition, "red", "black")
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi all
> Does anyone know how I can have conditional formatting in my reports?
> What I want to achieve is something like. If the field text = "Green" make
> the background green, etc
> Thanks alot in advance
> R
>
>|||Hi charles,
Thanks that worked great, just one problem,
There is 4 possibilities. it can either be red, green, yellow or blue..
I tried this:
=iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
else
=iif( Fields!Shift.Value = "Red", "Red", "transparent")
else
=iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
else
=iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
endif
but that don't quite help it. Any ideas?
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
> use the iif function for the font color. Reporting Services accepts
> expressions for most of its properties.
> =iif(boolean condition, "red", "black")
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
>> Hi all
>> Does anyone know how I can have conditional formatting in my reports?
>> What I want to achieve is something like. If the field text = "Green"
>> make
>> the background green, etc
>> Thanks alot in advance
>> R
>>|||IIF is a function, not a procedural statement. Therefore you cannot use the
syntax you are trying to use. Is your logic something like this: if the value
of the Shift textbox is "green", "blue", "red" or "yellow", make the color of
this the same, otherwise make it transparent? if so, you need
=iif(Fields!Shift.Value="green" or Fields!Shift.Value = "blue" or
Fields!Shift.Value = "red" or Fields!Shift.Value = "yellow",
Fields!Shift.Value, "transparent")
or are you trying to make a distinction between green and lawngreen, in
which case you could nest a second iif function like so:
=iif(Fields!Shift.Value = "blue" or Fields!Shift.Value = "red" or
Fields!Shift.Value = "yellow", Fields!Shift.Value, iif(Fields!Shift.Value ="green", "lawngreen", "transparent"))
both of the above should work.
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi charles,
> Thanks that worked great, just one problem,
> There is 4 possibilities. it can either be red, green, yellow or blue..
> I tried this:
> =iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
> else
> =iif( Fields!Shift.Value = "Red", "Red", "transparent")
> else
> =iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
> else
> =iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
> endif
> but that don't quite help it. Any ideas?
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
> news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
> > use the iif function for the font color. Reporting Services accepts
> > expressions for most of its properties.
> >
> > =iif(boolean condition, "red", "black")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Rudi Groenewald" wrote:
> >
> >> Hi all
> >>
> >> Does anyone know how I can have conditional formatting in my reports?
> >>
> >> What I want to achieve is something like. If the field text = "Green"
> >> make
> >> the background green, etc
> >>
> >> Thanks alot in advance
> >>
> >> R
> >>
> >>
> >>
>
>|||Hi Charles,
I figured it out, it was kind of simple really, just had to click. The
field "shift" only displays the words "red", "blue", "green" or "yellow".
So if that is the only words which are displayed, then obviously the
expression to be used as the background is =Fields!Shift.value
but your input made me click, thanks alot
Regards
R
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:CCA10812-58FB-43BD-B8FD-4C5B2BABC5AE@.microsoft.com...
> IIF is a function, not a procedural statement. Therefore you cannot use
> the
> syntax you are trying to use. Is your logic something like this: if the
> value
> of the Shift textbox is "green", "blue", "red" or "yellow", make the color
> of
> this the same, otherwise make it transparent? if so, you need
> =iif(Fields!Shift.Value="green" or Fields!Shift.Value = "blue" or
> Fields!Shift.Value = "red" or Fields!Shift.Value = "yellow",
> Fields!Shift.Value, "transparent")
> or are you trying to make a distinction between green and lawngreen, in
> which case you could nest a second iif function like so:
> =iif(Fields!Shift.Value = "blue" or Fields!Shift.Value = "red" or
> Fields!Shift.Value = "yellow", Fields!Shift.Value, iif(Fields!Shift.Value
> => "green", "lawngreen", "transparent"))
> both of the above should work.
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
>> Hi charles,
>> Thanks that worked great, just one problem,
>> There is 4 possibilities. it can either be red, green, yellow or blue..
>> I tried this:
>> =iif( Fields!Shift.Value = "Green", "LawnGreen", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Red", "Red", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Yellow", "Yellow", "transparent")
>> else
>> =iif( Fields!Shift.Value = "Blue", "Blue", "transparent")
>> endif
>> but that don't quite help it. Any ideas?
>> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in
>> message
>> news:4C2D7CCD-E8CB-4A5C-A7D9-9E2530CABA44@.microsoft.com...
>> > use the iif function for the font color. Reporting Services accepts
>> > expressions for most of its properties.
>> >
>> > =iif(boolean condition, "red", "black")
>> >
>> > HTH
>> >
>> > Charles Kangai, MCT, MCDBA
>> >
>> > "Rudi Groenewald" wrote:
>> >
>> >> Hi all
>> >>
>> >> Does anyone know how I can have conditional formatting in my reports?
>> >>
>> >> What I want to achieve is something like. If the field text = "Green"
>> >> make
>> >> the background green, etc
>> >>
>> >> Thanks alot in advance
>> >>
>> >> R
>> >>
>> >>
>> >>
>>