SSRS: change font color based on the expression

colorsfontsreporting-services

I am trying to give the below expression for font color in ssrs:

=IIF(Fields!A.Value<>0 AND Fields!P.Value <>0, IIF(Fields!A.Value >= Fields!P.Value, "Green", "Red"), "Black")

It works fine for all the numbers except when Either of A or P is 0 I am getting both A and P as Black.

Basically, All I want is:

  1. When A = P = 0 Black
  2. When A >= P (for eg. say both are 5) then Green
  3. When A < P Red

The IIF syntax did not work as I expected i.e. When I have 0 in P and 5 in A then the font color should be green and the vice versa should be red.

I have to work a way around to get what I want with Switch statement.

=Switch(Fields!A.Value>=Fields!P.Value and (Fields!P.Value<>0 AND Fields!P.Value <>0), "Green",
(Fields!A.Value = 0 and Fields!P.Value =0), "Black",
(Fields!A.Value=0 AND Fields!A.Value < Fields!P.Value), "Red",
(Fields!P.Value = 0 AND Fields!A.Value > Fields!P.Value), "Green")

Can someone explain to me why my IIF statement did not work please?

Your help is much appreciated.
Thank you.

Best Answer

If your question is

My values are P = 0, A = 5, but colour is not green

(which I think it is)

Then this is because you are using the construct 

=IIF(Fields!A.Value<>0 AND Fields!P.Value <>0 , truepart, falsepart)

You are saying that only do the true part when A is not zero AND P is not zero. Because you are comparing the values 0 and 5, then because one of these is zero, the false part will execute instead.

To implement the rules

  1. When A = P = 0 Black
  2. When A >= P (for eg. say both are 5) then Green
  3. When A < P Red

Then you will need an expression like

=iif(Fields!A.Value = 0 AND Fields!P.Value = 0, "Black",  <-- Set to Black if both zero
   iif(Fields!A.Value >= Fields!P.Value , "Green",        <-- Else, set to Green if A >= P
    "Red"))                                               <-- Otherwise set to Red

Hopefully this is helpful. If I have misunderstood please let me know and I shall try to assist further

Related Topic