I am astonished that this is not a feature in SSRS. How can you expect business users to create expressions to handle an optional filter.
So for those looking how to do an optional filter on a parameter, here is how to do it:
If I have a text parameter called instanceNumber that is blank by default but can contain an instance number, and I want to use that to filter.
- In the filters section of the DataSet Properties window of the dataset you want to filter, click add.
- We want to set the expression for the field we want to filter on so hit the expression button (Fx), as in the picture:
- in the main text area you want to create the following expression:
What the expression means is this: If the IncidenNumber has nothing in it, then filter on an empty string, otherwise filter on the field Incident Number. returning the empty string in this expression means that when it tries to apply the filter it won’t have a field to apply the filter on, and will therefore ignore the filter.
- You then need to set the Value field to the parameter you want to filter by, which should be the same as the one you used in the function IsNothing in the above expression.
- Now you are done, and your filter fields should look something like this:
So in 4 very simple steps we have used a parameter as an optional filter. It is not a very “business” user friendly method, but since Microsoft refuses to cater for optional parameters/filters in SSRS this is the way to do it.
There are other methods, this is just the way I chose since I didn’t like the other ones I came across, they all required SSRS to pass the parameter to the Stored Procedure, which means calling the Stored Procedure every time a filter was applied.