CODEWITHSUNDEEP
sql-serverreporting-services
Testtest11
Testtest11

Reputation: 367

Switch expression in SSRS

I wanted to fill cell based on value. First expression works only for LimeGreen, but IIF for all cases. Why?

=Switch(
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) > 0, "LimeGreen",
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) <= 0 &
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) >= -0.05, "Yellow",
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) < -0.05, "Red")

=IIF(
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) > 0, "LimeGreen",
    IIF(Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) < 0.05, "Red", "Yellow")
)

Upvotes: 0

Views: 10546

Answers (1)

Wes H
Wes H

Reputation: 4439

=Switch(
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) > 0, "LimeGreen",
Code.Trend(Variables!Sold1.Value, Variables!Sold2.Value) >= -0.05, "Yellow",
True, "Red")

Anything above 0 will be LimeGreen. After that, anything above -.05 will be Yellow. Everything else will be Red.

Since the first test looks for >0, the remainining options are all <= 0. This effectively means the second test is all values between -.05 and 0. The final test is the catch all but will only be chosen for values <-.05.

Upvotes: 1

Related Questions