SQL Server Reporting Services (SSRS) – Multiple IIf statements in an expression behind a textbox

Instead of using a code behind for simple decisions, you can use unlimited multiple IIf statements in an expression behind a textbox.

For example, let’s say you have a parameter called HoldType that can have 3 different values, (1, 2, or 3). Depending on the value, you may want to make an entire row of a report visible or not depending on these values.

To do this, use an IIf statement to interrogate a value and then assign an action to that value. In the case of only 2 different scenarios, you would simply type a comma and the alternative action. But what if like in this example there is more than 2 scenarios. You simply begin another IIf statement after the comma. You can keep adding multiple IIf statements until all scenarios are handled, and then simply add the default action after the comma of the last IIf statement as you would normal do with a single IIf statement.

The statement below demonstrates the visibility of a textbox on the report.

If HoldType is “1” then don’t hide the row, ELSE if HoldType is “2” then hide the row, ELSE don’t hide the row for any other value.

=IIf(Parameters!HoldType.Value = "1", FALSE, IIf(Parameters!HoldType.Value = "2", TRUE, FALSE))

3 thoughts on “SQL Server Reporting Services (SSRS) – Multiple IIf statements in an expression behind a textbox”

  1. very nice.
    Thanks but how can we put our customized text instead of TRUE and FLASE.
    If have 0 value I need to set “Ideal”, 2 for “not ideal” and so on

  2. Hello,

    Thanks for the question. You can replace TRUE and FALSE with your text by wrapping your text in quotes like this:

    =IIf(Parameters!Gender.Value = “m”, “male”, IIf(Parameters!Gender.Value = “f”, “female”, “other”))

Leave a Reply

Your email address will not be published. Required fields are marked *