INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Switch or IIF

Switch or IIF

(OP)
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

RE: Switch or IIF

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.

CODE

=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

RE: Switch or IIF

(OP)
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

RE: Switch or IIF

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

RE: Switch or IIF

(OP)
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

RE: Switch or IIF

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.

CODE

=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.)

CODE

=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

RE: Switch or IIF

(OP)
Yep - That was the issue - Percentages.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close