CODEWITHSUNDEEP
sql-serverreporting-services
jw11432
jw11432

Reputation: 593

SSRS: IIF statement to change fill color erroring out

In every example I could find to facilitate this, the logic is precisely what I'm using, but I can't get it to do what I need it to. It appears that I definitely can't use an AND condition inside the IIF expression, but even trying to account for that I don't get the correct results.

Attempt #1:

=iif(Fields!Days_to_Bill.Value >= 5, "Yellow", iif(Fields!Days_to_Bill.Value >= 10, "Red", "Transparent"))

This results in all numbers, including 10 and greater, being Yellow. enter image description here

Attempt 2:

=iif(Fields!Days_to_Bill.Value >= 5 and < 10, "Yellow", iif(Fields!Days_to_Bill.Value >= 10, "Red", "Transparent"))

This results in the following error:

enter image description here

I really thought this was going to be a super simple expression, but I must be missing something.

Upvotes: 0

Views: 372

Answers (2)

Derrick Moeller
Derrick Moeller

Reputation: 4960

You can avoid the and if you simply change the order of your expression. Simply check for values that are >= 10 first. Also I made a guess at the desired highlighting logic, as I suspect you made some mistakes in your example.

=iif(Fields!Days_to_Bill.Value >= 10, "Transparent", iif(Fields!Days_to_Bill.Value >= 5 "Yellow", "Red"))

Upvotes: 0

BJones
BJones

Reputation: 2460

For starters, your second IIF() needs to repeat Fields!Days_to_Bill.Value for each condition you're checking.

So you need =iif(Fields!Days_to_Bill.Value >= 5 and Fields!Days_to_Bill.Value < 10...

Second, the first equation is correct and you are getting expected results. In an IIF() statement it will stop at the first 'True' value. Everything in your result set is >= 5, therefore they will all be yellow.

Upvotes: 1

Related Questions