Case Statement with IIF Condition

Question

User-1499457942 posted

Hi

     How to write Case Statement with IIF condition.

Thanks

Answer 1

User991499041 posted

Hi JagjitSingh,

How to write Case Statement with IIF condition.

The IIF function returns one of two values depending on whether the expression is true or not.

Syntax

IIF ( boolean_expression, true_value, false_value )

Arguments

boolean_expression
A valid Boolean expression.
If this argument is not a Boolean expression, then a syntax error is raised.

true_value
Value to return if boolean_expression evaluates to true.

false_value
Value to return if boolean_expression evaluates to false.

The following expression uses the IIF function to return a Boolean value of True if the value of LineTotal exceeds 100. Otherwise it returns False:

=IIF(Fields!LineTotal.Value > 100, True, False)  

Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on the value of PctComplete.

The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))

When you have three or more conditions to test, you can use the Switch function.

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/expression-examples-report-builder-and-ssrs#DecisionFunctions

Regards,

zxj

Answer 2

User347430248 posted

Hi JagjitSingh,

below is an example for Switch statement.

=SWITCH(Fields!CurrentRiskLevel.Value = "Low", "Green",
Fields!CurrentRiskLevel.Value = "Moderate", "Blue",
Fields!CurrentRiskLevel.Value = "Medium", "Yellow",
Fields!CurrentRiskLevel.Value = "High", "Orange",
Fields!CurrentRiskLevel.Value = "Very High", "Red"
)

now if you want to use it inside If condition then you can try to write it in True part or write it in else part of the if condition.

Reference:

SSRS Case Statement

SQL Reporting Services Switch Statement

Regards

Deepak