Custom tablix filters in SSRS

Question

I have a report which displays a table full of raw data. Prior to entering this report, the parent report asks you to select a 'Service' & 'Department' Depending on which Service/Department you select from the parent report, this RAW data will be filtered to show the relating data.

Straight forward enough, and it works, great.

I have a new requirement now. If the chosen Service is equal to 'Service X' I need the data to be filtered again on that Service, department, but also to add aditional filter, on their 'team'.

so that the data will also be filtered where the team matches the user running the report's team.

I have a dataset already created which returns the user running the reports 'team' And also a new parameter called 'team' which defaults to the user running the reports AD number'

The new requirement is, if the Service = X, then filter the data on the department but also on THAT users 'team', if the Service is not equal to X, do nothing.

I think I need to alter the Filters section of the Tablix Properties but am not sure what I need to put in the Expression, Operator, Value

So far I have tried =IIf(Fields!Service.Value = "Service X", Fields!Team.Value, nothing) in the Expression, set the Operator to In and tried filtering on the 'team' from my new dataset which stores the current users 'team' but it is not working.

Does anyone have any suggestions?

Answer 1

For these sorts of conditional filters I've had best results with using the IIf statement (or whatever) to return a string and filtering based on that, e.g. something like:

=IIf(Parameters!Service.Value <> "Service X" or Parameters!Team.Value = Fields!Team.Value
    , "Include"
    , "Exclude")

Then you can set the Operator to = and the filter value to Include. Just seems to a bit more robust in my experience.

Reading over this, you could even set the IIf statement up as a Calculated Column in the dataset and filter on that.