Dan English's BI Blog

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

Archive for January 28th, 2011

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

Posted in Reporting Services | Tagged: | 16 Comments »