Adding optional filter in SSRS 2008

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 advic开发者_运维知识库e?

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.

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)

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.

Although I agree that most of the time it is best to send the parameters back to the stored procedure or data layer to reduce the amount of data returned, I have a case where it is just as easy to do the parameter handling in the RDL file via a filter. Due to this unique situation I found this solution which gives you a way to create an Optional filter in the RDL file.

http://www.andrewshough.com/development/sqlserver/ssrs/optional-filter-in-ssrs/

It is a great blog post with easy step by step instructions on how to create an optional filter.

Please Note: This is NOT my blog but I though this solution was great for what I needed and I hope it helps someone else when they google for "optional filter in SSRS" like I did.

I found a post which solved my problem setting the filter for a report-consumer to a) all multivalue fields being selected so the user b) could specify his/her selection if necessary.

Kasim 8 Dec 2010 8:55 AM #

In reports when we want to default the multivalue parameter to 'Select All' following are the steps.

1. Open the Report parameter window from the Report menu.

2. Select the Report parameter from the left handside of the window.

3. Select 'Multi-value' checkbox and appropriate 'Available values'.

4. Under default values select 'From Query' radio button'.

5. Select the appropriate 'Dataset'.

6. Select appropriate 'Value Field'.

7. Save the Report and select Preview Tab. You will find all the items selected in the multivalue >parameter list and the result displayed for all the selected items.

found on: http://blogs.msdn.com/b/bimusings/archive/2007/05/07/how-do-you-set-select-all-as-the-default-for-multi-value-parameters-in-reporting-services.aspx

(The Post came up in the comments quite in the middle.)

You can accomplish this by using an expression on the dataset filter. Check this