0

In the below expression I am wanting to evaluate several things. I want to turn the value red if the material % is greater than 89.9 %. If it is not then look at the following. is the invoice amount less than 75 dollars and the material % is greater than 20% then turn orange.

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE", Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE", Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",  Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",  Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",  Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",  Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",  Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",  Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",  Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE")

The way i have it now looks like it is stopping after looking at just this:

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE"

Any help is very appreciated!

Tyler

3

I would recommend you read the expression you have written very carefully because your logic is flawed and that is why your SWITCH statement is not working as you believe it should.

=SWITCH
    (Fields!Matl_.Value > 89.9, "RED", 
        Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE", 
        Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE", 
        Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",  
        Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE"
    )

When you format the code, it's easy to see you have written clauses like:

Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 

The value of IHDAR_AMT1.Value cannot be both less than 76 and greater than 150, so it immediately evaluates to FALSE.

  • Cant believe i didn't catch that. Got so into writing stating missing simple logic! Thanks so much for the help! – Tyler G May 30 '14 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.