How to filter a tablix based on paramater but no filter when paramater is null?

Question

I've a tablix with data that comes from a dataset , there is a column called Productgroup and there might be some users that would like to apply a filter on it.

I'm trying to build an ssrs expression that will filter my tablix on that specific column but only when the parameter called @Filter is not null , if null , it does not need to apply filter

I've tried Expression: Productgroup Operator: like Value: =IIF(IsNothing(Parameters!Filter.Value), false, Fields!ProductGroup=Parameters!Filter.Value)

Nothing seems to works :(

Answer 1

First of all, it's best to send your parameters to your dataset and filter on the server, rather than bring back ALL of the data and filter on the client side. So that means parameterizing your stored procedure, or using a WHERE clause to filter the table/view you're connecting to.

If you stick with this approach:

You want your Expression to be "ProductGroup".

You want your Operator to be "=".

You want your Value to be =IIF(IsNothing(Parameters!paramFilter.Value), Fields!Productgroup.Value, Parameters!paramFilter.Value)

What you're doing is saying if the parameter is empty, I need the ProductGroup to be equal to the ProductGroup. That's always true. But if the parameter is not empty, the ProductGroup has to be equal to the parameter value.