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))
Please follow and like us: