SSRS- Complex Iff statements in changing background color

0
votes

I am formatting the bottom rows of my report using the following expression

=IIf(Fields!EndPoint.Value="Total Sales Division"  or 
Fields!EndPoint.Value="Total End Point" or 
Fields!EndPoint.Value="Total IP-Relay" or 
Fields!EndPoint.Value="Total ClearCaptions","CornflowerBlue",
iif(Fields!Projection.Value="Actual","LimeGreen","LightBlue"

It works great until it hits columns with zero in it and formats the cell with the false resault being lightblue. I did use =iif(isnothing(Sum(Fields!MnthSum.Value)),0,Sum(Fields!MnthSum.Value)) to insert zers's where I had blanks. I am assuming I must build that into my formula?

I have tried playing with this 

=IIf(Fields!EndPoint.Value="Total Sales Division" and Sum(Fields!MnthSum.Value)=0 or 
Fields!EndPoint.Value="Total End Point" or 
Fields!EndPoint.Value="Total IP-Relay" or 
Fields!EndPoint.Value="Total ClearCaptions","CornflowerBlue",
iif(Fields!Projection.Value="Actual","LimeGreen","LightBlue"

this turns everything in that section lightblue and scipping the CornflowerBlue.

Any suggestions would be great and Thanks in advance.

1
reporting-services

1 Answers

0
votes

I think you need to add brackets to get your logic to evaluate correctly - probably like this:

=IIf( ( Fields!EndPoint.Value="Total Sales Division" and Sum(Fields!MnthSum.Value)=0 ) or Fields!EndPoint.Value="Total End Point" or Fields!EndPoint.Value="Total IP-Relay" or Fields!EndPoint.Value="Total ClearCaptions","CornflowerBlue", iif(Fields!Projection.Value="Actual","LimeGreen","LightBlue"