locked
Case Statement with IIF Condition RRS feed

  • Question

  • User-1499457942 posted

    Hi

         How to write Case Statement with IIF condition.

    Thanks

    Monday, November 27, 2017 4:18 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 27, 2017 5:59 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 28, 2017 1:40 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 27, 2017 5:59 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 28, 2017 1:40 AM