Setting Up Complex Conditions for Tablix Filters in SSRS

Scenario

Imagine you need a report to display records based on a casemanager field that might contain null values or specific identifiers. The report should show records where the casemanager field is either unset (null) or matches any of the identifiers selected by the user from a multi-value parameter.

Implementing the Filter

1. Add a Multi-value Parameter:

   • Create a parameter that allows users to select multiple casemanager identifiers. Each identifier corresponds to a unique casemanager.

2. Configure the Filter on the Tablix:

   • Access the tablix properties in your report.
   • Go to the "Filters" section and add a new filter to apply your conditions.

3. Set Up the Filter Expression:

   • Expression: This will check for null values or match against selected parameter values.
   • Operator: Use the "Equal" operator.
   • Value: The expression should evaluate to True for included records, otherwise False.

Example Expression

Here’s the corrected SSRS expression for the filter:

=IIF(

    IsNothing(Fields!casemanager.Value),

    True,

    IIF(

        InStr(

            "," + Join(Parameters!CaseManager.Label, ",") + ",", 

            "," + Fields!casemanager.Value + ","

        ) > 0,

        True,

        False

    )

)

Or, you can add the filter to Dataset.

Detailed Explanation:

• IsNothing(Fields!casemanager.Value): This function checks if the casemanager field is null.
• InStr(...): This function searches for the casemanager value within a comma-separated string of the selected parameter labels, ensuring it matches as a whole value (thanks to commas added on both ends).
• Join(Parameters!CaseManager.Label, ","): Concatenates the labels of all selected parameter values into a single string, separated by commas.

By using this approach, the report filters data based on whether the casemanager is unspecified or matches one of the selected options, enhancing the report's responsiveness and relevance to user inputs. This method is particularly useful in reports where user selections directly influence the data displayed, ensuring users see only the data pertinent to their interests or query parameters.