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.
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:
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.
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.
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.
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.
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:
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.
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.
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.
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: