Adding optional filter in SSRS 2008

Question

I am trying to add an optional filter on a text field in a report. I have tried setting it up in the dataset but it treats it as a required filter so if it is not populated, no results are returned. It needs to use the Like operator. Any advice?

Solution 1

As I was typing out a work-around to this problem, I realized an incredibly easy solution (now that I understand better how it works).

Here's what I did: Since Hong pointed out that all filter conditions must be met, I reversed my thinking. I moved my existing "IN" filters to the query and fed the parameter directly to the query. Then I created by "LIKE" text filter on the report which a default value of "*" so it would immediately return everything.

Here's what I could've done: Just the last part. Added the "LIKE" filter with a default value of "*" so it immediately returned everything.

Solution 2

I also agree that most of the time it's best to send the params back to SQL. Since that's not what the OP is asking, here is the best option I have found for doing this. And it is actually quite simple.

• Add your parameter with the appropriate data type. Let's use the example of a "City" in this case (a text/string field).

• Check "Allow Nulls" on the parameter.

• Add a filter to either a tablix, table or dataset.

• In the expression, select the field you want to filter on. Select the appropriate operator, in my example of a data set with Cities, in the Value put in this:

  =IIF((Parameters!City.Value Is Nothing), Fields!City.Value, Parameters!City.Value)

Solution 3

I don't think you can make an optional filter in DataSet Properties/Filters, adding filters there means returning results that match ALL filter contiditions, so it is "AND" logical relation among all filters, not "OR".

My sugguestion is to use filter in query designer of the dataset, where you can define "OR" relations to filter out data. For instance: Your_Text_Field="SomeValue" OR Your_Text_Field is Empty.