Switch or IIF 1

[Tetol]

I am trying to contruct a switch or IIf statement compare values in two fields to higlight cells on a report to eith White, Red or Yellow.

The part I has the issue is if field FieldA is within +-10 of the Target field, to make the cell yellow. I have also tried using the field option but it's not working.

=IIF(Fields!FieldA.Value = 0.00, "White",
IIF(Fields!FieldA.Value < Fields!Target.Value, "Red",
IIF(Fields!FieldA.Value > Fields!Target.Value, "White",
IIF(Fields!FieldA.Value - Fields!Target.Value <= 10 And Fields!FieldA.Value - Fields!Target.Value > 0, "Yellow",
IIF(Fields!Target.Value - Fields!FieldA.Value <= 10 And Fields!Target.Value - Fields!FieldA.Value > 0, "Yellow", "White" )))) )


Thanks so much for your help

 

[SgtJarrow]

If I understand the logic, the below code should be close. It is typed, not tested, so please check to see if it fits your business requirements completely. And I typed this into my SQL Server instLogic I used was as follows:

If FieldA Is 0, Then white
Else If FieldA < Target, Then red
Else If The Difference Between FieldA And Target Is 10 Or Less, Then Yellow
Default Anything Else To White.

=IIF(Fields!FieldA.Value = 0.00, "White", IIF(Fields!FieldA.Value < Fields!Target.Value, "Red", IIF(ABS(@FieldA - @TargetValue) <= 10, "Yellow", "White")))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Tetol]

Plesae see below what I tried but it still did not work. All values of FieldA greater than Target turned yellow but I want FieldA to turn yellow only when the difference between FieldA and Target is <= 10.

=IIF(Fields!FieldA.Value = 0.00, "White", IIF(Fields!FieldA.Value < Fields!Target.Value, "Red",
IIF(ABS(Fields!FieldA.Value - Fields!Target.Value) <= 10, "Yellow", "White")))

Any Thoughts...Thanks

 

[SgtJarrow]

while not tested, that should have worked. Here's why:

FieldA = 2
Target = 5

FieldA-Target = -3
Absolute that to 3

Target-FieldA = 3
Absolute that to 3

Either way, the value is less than 10, per your requirements. This would hold true for any basic number combination.

Can you provide some sample data based on your actual values and the expected results?
That will help determine why the above code didn't work for you....


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Tetol]

Ok so with this code

=IIF(Fields!FieldA.Value = 0.00, "White",
IIF(Fields!FieldA.Value < Fields!Target.Value, "Red",
IIF(ABS(Fields!FieldA.Value - Fields!Target.Value) <= 10, "Yellow","White" )))



Target FieldA Actual Result Desired Result
60% 35.16% Red Red
60% Blank White White
60% 89.99% Yellow White
60% 68.79% Yellow Yellow

Your thoughts

 

[SgtJarrow]

Sure....I can see your issue. You never mentioned that you are working with percentages (which matters in this case).

Quick note: A percentage value is actually handled like a decimal of 1. So ALL your values are less than 1.

Therefore, based on the math you need to perform, you will never have a difference between FieldA and Target that is greater than 1.

To compensate for that, you have to multiply the difference value by 100 (or divide the comparison value of 10 by 100) in order to bring the two values on the same level for comparison.

Try this code (in which I multiply the difference by 100) that seems to produce the results based on the sample provided.

=IIF(Fields!FieldA.Value = 0.00, "White", 
IIF(Fields!FieldA.Value < Fields!Target.Value, "Red", 
IIF((ABS(Fields!FieldA.Value - Fields!Target.Value) * 100) <= 10, "Yellow","White" )))

Another quick note: Since I don't know your actual data source, your sample shows at least one BLANK value for one of your comparison values. Depending on how your data is stored, that might throw results unless you handle them in the formula. Ensure you do a good check for anywhere that BLANKS exist in your values. The BLANK in the sample was handled sufficietly by the above code but if you need a more detailed error handling, then the below might be better. (I'd personally make that evaluation in my SQL code if at all possible because the report gets really ugly quick, as you should be able to see.)

=IIF(IIF(IsNothing(Fields!FieldA.Value), 0.00, Fields!FieldA.Value) = 0.00, "White", 
IIF(IIF(IsNothing(Fields!FieldA.Value), 0.00, Fields!FieldA.Value) < IIF(IsNothing(Fields!Target.Value), 0.00, Fields!Target.Value), "Red", 
IIF((ABS(IIF(IsNothing(Fields!FieldA.Value), 0.00, Fields!FieldA.Value) - IIF(IsNothing(Fields!Target.Value), 0.00, Fields!Target.Value)) * 100) <= 10, "Yellow","White" )))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Tetol]

Yep - That was the issue - Percentages.

It worked this time. Thanks for your patience and help.