reporting-services

Filter a column with multiple option in a data set

I have a long sql query which would take a long long long time to filter thanks to a regex thus i wanted to put a filter on my column I want to have only the value A or B in my column based on what my parameter is. I have 7 choice for my column. Does anyone have an idea that wouldnt require me to play in my SQL query. I would rather only play in the filter of the data set

Solution

  • SSRS's dataset filter is actually kinda lame (can't add ORs...) but you can create complicated logic in the Expression for the filter.

    A SWITCH can evaluate multiple scenarios and flag whichever records need to be shown using a 0/1 value. The last line is identify any remaining records to not display.

    =SWITCH(Parameters!PARAMETER.Value = "Team Alpha" AND Fields!FIELD.Value = "A", 1, 
        Parameters!PARAMETER.Value = "Team Beta" AND Fields!FIELD.Value = "B", 1, 
        Parameters!PARAMETER.Value = "ALL", 1, 
        1 = 1, 0
        )

    Then set the Filters data type to Integer, the Operator to = (equals sign), and the Value to 1 to match the results from the SWITCH.

    enter image description here

    Unfortunately (for you), the dataset filtering is done after the query runs so it's not going to help the query run time part of the report but may help in the render time.