SSRS Tips : Implementing OR Based Logic for Dataset Filters
https://msdnshared.blob.core.windows.net/media/2016/08/0841.NinjaAwardTinySilver.pngSilver Award Winner
Scenario
There are multiple levels at which filters can be applied to a SSRS report. We can have the filters being implemented at the query level, data set level, container level or even at a row group level of SSRS reports. The choice of where we need to apply depends on our business scenario.
Now consider the case where we want to apply a series of filters at the data set level of a SSRS report. Whilst the best and optimized was to filter the data is to do it in the query if possible we cant always go for the approach. For example consider the case where we have multiple containers within the report with each showing data with different set of filters. In that case we will be compelled to bring data by applying only the common filters at the database level. All the other specific filters would be applied at the corresponding data set level or at container level. Things get little more interesting when we have multiple filters to be applied at the data set level combined using logical OR operator
Illustration
Lets consider the below illustration
There is a table called vehicle which stores details on various makes, models of cars along with its year of manufacture and cost as given below.
create table Vehicles ( Make varchar(50), Model varchar(50), YearOfManuf int, Cost int ) insert Vehicles SELECT 'Acura' AS 'Make', 'TL' AS 'Model', 2014 AS 'Year' ,30000 AS 'Cost' UNION SELECT 'Acura' AS 'Make', 'TL' AS 'Model', 2013 AS 'Year' ,25000 AS 'Cost' UNION SELECT 'BMW' AS 'Make', '328' AS 'Model', 2014 AS 'Year' ,35000 AS 'Cost' UNION SELECT 'BMW' AS 'Make', '528' AS 'Model', 2013 AS 'Year' ,50000 AS 'Cost' UNION SELECT 'BMW' AS 'Make', 'Z4' AS 'Model', 2012 AS 'Year' ,40000 AS 'Cost' UNION SELECT 'BMW' AS 'Make', 'BMW i' AS 'Model', 2014 AS 'Year' ,120000 AS 'Cost' UNION SELECT 'BMW' AS 'Make', 'X3' AS 'Model', 2013 AS 'Year' ,40000 AS 'Cost' UNION SELECT 'Lexus' AS 'Make', 'ES 350' AS 'Model', 2013 AS 'Year' ,45000 AS 'Cost' UNION SELECT 'Lexus' AS 'Make', 'RX' AS 'Model', 2013 AS 'Year' ,50000 AS 'Cost' UNION SELECT 'Lexus' AS 'Make', 'CT' AS 'Model', 2014 AS 'Year' ,32000 AS 'Cost' UNION SELECT 'Ford' AS 'Make', 'F-150' AS 'Model', 2011 AS 'Year' ,25000 AS 'Cost' UNION SELECT 'Ford' AS 'Make', 'Escape' AS 'Model', 2012 AS 'Year' ,25000 AS 'Cost' UNION SELECT 'Ford' AS 'Make', 'Fusion' AS 'Model', 2013 AS 'Year' ,30000 AS 'Cost' SELECT * FROM Vehicles
Now lets create a SSRS report for displaying the above data. For this purpose I'm creating a data source and a single dataset which will return the required data.
Now lets create the report using the above dataset. The report will look like below.
Now we can apply two filters at the dataset level in the report. The intention is to show the vehicle details of BMW make or manufacture year > 2013. The filters can be added as below.
Now execute the report and you will see the below result.
Check the result you will see that this was not what we really expected. On careful analysis what we can see is that its combining the filters using an AND condition whereas what we wanted was to combine them using OR. To make matters worse there's no way by which we can specify OR combination through the Filter screen shown above. So the question is how we can achieve our requirement in that case.
Solution
The solution to the requirement can be achieved by applying a workaround as specified below. Instead of implementing them as separate filters we can make use of logical operators available within SSRS expression for this purpose. So for the above case we will setup a single filter with expression as below.
Expression ----------- =CBool(IIf(Fields!Make.Value = "BMW" Or Fields!YearOfManuf.Value > 2013,True,False)) Operation --------- = Value --------- =CBool(True)
Now when you try rendering the report you will get the below output.
This output matches our expectation indicating that expression was able to perform an OR combination of the filter conditions. Hence we can confirm that its working fine!
Conclusion
From the above illustration we can conclude that we can make use filter expressions based on logical operations to achieve the OR combination at the data set or container level in the report. The same approach can be extended to include multiple combinations inside the filter expressions. Suppose if we want to also include a condition that Cost should be > 30000 we can tweak the filter expression like this.
Expression ----------- =CBool(IIf((Fields!Make.Value = "BMW" Or Fields!YearOfManuf.Value > 2013) And Fields!Cost.Value > 30000,True,False)) Operation --------- = Value --------- =CBool(True)
See Also
Another important place to find an extensive amount of SQL Server related articles is the TechNet Wiki itself. The best entry point is SQL Server General & Database Engine Resources on the TechNet Wiki.