Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Reporting Services (SSRS) OR Filter Logic

Posted by denglishbi on January 28, 2011

In designing a report one of the requirements is typically to add parameters and be able to filter the results displayed in the report.  For the most part these types of requests are to filter the data so that the reports include data where the values are inclusive to what is selected. 

Scenario

Let’s say that I am working on having a sales meeting and want to include the local sales people along with some of my top performers.  I would create a report with two parameters, one to filter the report by state and the other based on the sales amount like the following:

image

In the example above I am using the ever famous Adventure Works 2008 R2 database pulling data from the Sales.vSalesPerson view.  I am referencing the same view for the additional dataset that I use to provide available values for the StateProvince parameter performing an aggregate ‘Grouped by’ on the Sales.vSalesPerson.StateProvinceName column.

I then setup the report to display the list of sales people that I would like to send an invite to for the meeting.  My criteria will be to include the sales people that live in Minnesota because that is where I am planning on having the sales meeting and the second criteria is to also include people that have had sales of at least $1 million.  I go ahead setup the parameters with the dataset, create the report, and select the criteria.

image

image

One thing to point out here is that I am placing the filtering criteria on the Filters portion of dataset and it is not embedded in the Query.  In this case I am using a database view that I could embed this logic, but there might be certain scenarios when you are referencing an item that you cannot do this like against a stored procedure or SharePoint list.

Issue

The report returned the results based on my criteria, but in this case what was returned was only a single sales person.  Not exactly what I wanted.  What I was looking for was to include the sales people that lived in Minnesota along with the people that had at least $1 million in sales.  The way that the report was setup it only returned results for people that met both criteria, not one or the other.

If I go back into the Filters section of the dataset properties I do not see an option to change the relationship of the filters from an ‘And’ to an ‘Or’ relationship.  Where is the logical operator selection?  Hmmm….  If I travel back in time a few years I believe that Report Builder 1.0 had an option to specify the logical operator between the parameters that I would use.

image

Working with a Filter Condition (Report Builder 1.0)

Logical Operators

As you add additional filter conditions to the filter area, a logical operator is displayed between each filter condition. Report Builder supports the And and Or logical operators. By default, the logical operator is And, but you can easily change it by clicking And and selecting Or.

Interesting.  Looks like this what kind of left out with the newer versions of Report Builder.  So where do we go from here?  How about we head over to the Reporting Services forums and see what we can come up with?  After a few searches we come across this thread – How to filter a table with an “OR” condition.  We find out that you would run into this same type of issue with data regions on the report if you try and add filters to them as well.  The solution for this issues is not as obvious as one would think, but there is a workaround based on the answer to the thread.

Solution

In order to resolve the fact that there is no option to select the logical operator between filters they must be combined into a single filter option.  This mean that the second filter will be removed and the OR statement will be placed into an expression in a single filter.  This same technique can be utilized on a data region as well within the report.  In the configuration of the filter we will set the expression like such:

image

And the Filters portion will look like the following now with the value being set to True and the expression type have a data type of Boolean.

image

Now when we go and run the report the results contain not only the sales person from Minnesota, but also the other sales people that had sales of at least $1 million.

image

Now we have the complete list of 14 sales people that we can send an invite to attend the sales meeting.  The one person that lives in Minnesota and the other 13 that had at least $1 million in sales.

Conclusion

Coming to solutions is not as always as straight forward as one would assume.  There is typically always a workaround to get at the desired results.  You might not necessarily have to go to the lengths that we did here if you are going against the database directly, but in some cases you might be using a stored procedure that you cannot modify or using a SharePoint data source that you might not be able to incorporate this logic against directly.

If you are interested in taking a look at the RDL file, feel free to download it from my SkyDrive account here:

image

About these ads

15 Responses to “Reporting Services (SSRS) OR Filter Logic”

  1. Andrea said

    What are the pros and cons of setting this scenario up as a Filter versus parameters being passed to a query’s Where clause? I tend to only use a Filter when the criteria is static.

    • denglishbi said

      I would only use this type of a setup if I was unable to incorporate the logic in the dataset query or source. So for sources where you cannot incorporate the logic say in a SharePoint list or a stored procedure or possibly a multidimensional source you could implement this type of logic in a Filter. This can also be used in a data region if you want to breakout the data on the report possibly and display a subset of the data in a section of the report. Not everyone will have the ability to modify or include this logic in the source, so it is simply an alternative, but if you can incorporate this type of logic in the source than that would be the route to go. That is not always an option or available though.

  2. HKM said

    Very good article.

  3. Daniel said

    Thank you!

  4. erjoell said

    Excellent article but did not work for me. I am trying check whether the filtered field is examined to see if = result of an IIF expression OR is NULL. I used IsNothing in expression generator but return generated 0 rows.

    ==Fields!User_s_UA_Status.Value =IIF(Parameters!Users_UA_Status.Value = “ALL”, Fields!User_s_UA_Status.Value, Parameters!Users_UA_Status.Value) Or IsNothing(Fields!User_s_UA_Status.Value)

    and the value field would not accept the value of True. (returning a “The FilterValue expression for the tablix ‘Tablix1’ has the value “True”, which is not a valid Boolean value” error)

    Sodo you have any advice?

  5. erjoell said

    I finally gave up trying to do a filter and instead put a CASE statement in the query for the report.

  6. Tim said

    …still it’s not perfect. If you need both operators to be an “IN” operator, then this would not work at all. This is because the “IN” operator is NOT available for the expression.
    This is one of the weakness of SSRS.

    • denglishbi said

      Should work fine with IN operator as long as you have the parameter setup as a multi value parameter. I guess I would need more details as to what is not working for you and how you have it currently setup.

      • The IN operator is indeed not available from within the expression. When you need it this is a problem.

        In my case I want:

        =(Fields!AfmeldMaand.Value = Parameters!AfmeldMaand.Value(0)) And (Fields!Jaar.Value = Parameters!Jaar.Value(0)) And (Fields!Type.Value = Parameters!IncidentType.Value(0)) And (Fields!naam.Value = Parameters!Klant.Value(0)) And (Fields!Engagement.Value = Parameters!Engagement.Value(0))

        to look something like this:

        =(Fields!AfmeldMaand.Value IN Parameters!AfmeldMaand.Value(0)) And (Fields!Jaar.Value IN Parameters!Jaar.Value(0)) And (Fields!Type.Value IN Parameters!IncidentType.Value(0)) And (Fields!naam.Value IN Parameters!Klant.Value(0)) And (Fields!Engagement.Value IN Parameters!Engagement.Value(0))

        to be able to use all multi parameter values (OR-ed together) in the filter.

        Any ideas?

      • Solution for the above was straight forward but tricky: When you start using the ‘IN’ operator in your filters, make sure you also remove the ‘(0)’ part in the Parameters!.Value(0). This section indicates the first value of a set of parametervalues and will be automatically added when adding a filter containing the ‘=’ operator on a multivalued parameter! When changing the filter to use the ‘IN’ operator the (0) will have to be removed manually.This way the report will use all of the selcted Multi value parameters when executing.

  7. st mnmn said

    work perfectly!!!! Thanks a lot!!!

  8. Alice said

    muy buen articulo, Yo necesito poner un filtro donde pongo el codigo y la descripción (yo ya cargo los valores del combo con una query) pero que el usuario elija por descripción y yo pueda mapear por el código, muchas gracias!.

  9. [...] between the parameters. In the following post Dan explains about the OR Filter logic, please click here to know the details about how to do it. I hope that it helps [...]

  10. Nate said

    Nicely done sir. Thanks!

  11. Mansoor Khan said

    Works perfect. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: