Sometimes it is preferable to filter a tablix on the SSRS side, instead of within the stored procedure. Usually I do it to avoid using sp_executesql. In these cases, I’d rather filter the data post-query than make my query harder to write and read. If it’s low volume, it’s a good opportunity to use filters.
But to accomplish this, it is necessary to do advanced things like filter parameters that allow multiple selections, and to allow NULLs to pass through.
To allow multi-value parameters to be filtered, I did not find the way to use integer values. However, I was able to filter by characters and labels. An alphanumeric DataSet field is IN a multi-value parameter label. It looks something like the following:
In order to allow a NULL value, it is a bit more tricky
1. Edit the tablix data source (the view or stored procedures) to change a NULL to a unique value. Example:
SELECT isnull( [State].StateCode, ‘None’) as StateCode,
2. Edit the parameter’s data source (DataSet) to provide ‘None’ as a value. Example:
SELECT 0 as StateId, ‘None’ as StateCode
SELECT StateId, StateCode
These two values absolutely must be the same. For example, they are both ‘None’ in my two code examples.
As a side, I’d rather say ‘None’ than ‘NULL’ for two good reasons
1. Business users sometimes don’t know what NULL means
2. The string ‘NULL’ is not the same thing as a value of NULL. It gets confusing, so it’s better to avoid that.
The parameter with a None value will look like this:
To allow NULLS, the Tablix filter still says the same as it was for the multi-value parameter.