Search code examples
reporting-servicesssrs-tablixiif

SSRS IIF Filtering on Tablix

I am working with SSRS and have a tablix that needs certain rows excluded if a value in a multi-value parameter is not selected. For example, the multi-value parameter is 'Include Loss' and the values are 'Yes' and 'No'.

So if the user selects 'No', then I want the tablix to exclude rows where Description field is equal to the text "Loss Transaction".

I am trying to write an expression to filter on the tablix as follows, but having no luck.

=IIF(Parameters!IncludeLoss.Value="N", Fields!Description.Value, NOTHING)

and use '<>' for the 'Operator' and then:

="Loss Transaction"

I get the error 'Failed to evaluate the FilterValue of the Tablix'. Any suggestions? Thanks in advance!

Solution

  • Filters have an implicit AND relationship - that is, all the conditions have to be True for the filter to take effect.

    Accordingly, you can have two filter conditions set:

    =Parameters!IncludeLoss.Value is equal to N (add a second condition)

    =Fields!Description.Value is equal to ="Loss Transaction"

    Alternatively, for complex conditions (or conditions involving OR or Null which aren't supported in a standard filter) you can just use one condition and set that condition's expression to something more complex that evaluates to a boolean and test that against True. For example:

    =Parameters!IncludeLoss.Value = "N" AND Fields!Description.Value = "Loss Transaction"