There are two ways that you can filter data in Reporting Services. The first way, which usually provides a faster performance, is to use query parameters to apply a filter using the WHERE clause in a SQL statement. In that case, the structure of the filter depends upon the syntax recognized by the source database. Another way to filter data in Reporting Services is to apply a filter to a dataset, data region, or a group. Using this latter method, you can even apply multiple filters. However, the use of filter operators or the setup of multiple filters is not always obvious, so in this series of posts, I’ll provide some more information about the configuration of filters.
First, why not use query parameters exclusively for filtering? Here are a few reasons:
- You might want to apply a filter to part of the report, but not all of the report.
- Your dataset might retrieve data from a stored procedure, and doesn’t allow you to pass a query parameter for filtering purposes.
- Your report might be set up as a snapshot on the report server and, in that case, cannot be dynamically filtered based on a query parameter.
Next, let’s look at how to set up a report filter in general. The process is the same whether you are applying the filter to a dataset, data region, or a group. When you go to the Filters page in the Properties dialog box for whichever of these items you selected (dataset, data region, group), you click the Add button to create a new filter. The interface looks like this:
The Expression field is usually a field in the dataset, so to make it easier for you to make a selection,the drop-down list displays all of the current dataset fields. But notice the expression button to the right, which means that you can set up any type of expression-not just a dataset field. To the right of the expression button, you’ll find a data type drop-down list. It’s important to specify the correct data type for the field or expression you’re using.
Now for the operators. Here’s a list of the options that you have:
This Operator | Performs This Action |
=, <>, >, >=, <, <=, Like | Compares expression to value |
Top N, Bottom N | Compares expression to Top (Bottom) set of N values (N = integer) |
Top %, Bottom % | Compares expression to Top (Bottom) N percent of values (N = integer or float) |
Between | Determines whether expression is between two values, inclusive |
In | Determines whether expression is found in list of values |
Last, the Value is what you’re comparing to the expression using the operator. The construction of a filter using some operators (=, <>, >, etc.) is fairly simple. If my dataset (for AdventureWorks data) has a Category field, and I have a parameter that prompts the user for a single category, I can set up a filter like this:
Expression | Data Type | Operator | Value |
[Category] | Text | = | [@Category] |
But if I set the parameter to accept multiple values, I need to change the operator from = to In, just as I would have to do if I were using a query parameter. The parameter expression, [@Category], which translates to =Parameters!Category.Value, doesn’t need to change because it represents an array as soon as I change the parameter to allow multiple values. The “In” operator requires an array.
With that in mind, let’s consider a variation on Value. Let’s say that I have a parameter that prompts the user for a particular year – and for simplicity’s sake, this parameter only allows a single value, and I have an expression that evaluates the previous year based on the user’s selection. Then I want to use these two values in two separate filters with an OR condition. That is, I want to filter either by the year selected OR by the year that was computed. If I create two filters, one for each year (as shown below), then the report will only display results if BOTH filter conditions are met – which would never be true.
Expression | Data Type | Operator | Value |
[CalendarYear] | Integer | = | [@Year] |
[CalendarYear] | Integer | = | =Parameters!Year.Value-1 |
To handle this scenario, we need to create a single filter that uses the “In” operator, and then set up the Value expression as an array. To create an array, we use the Split function after creating a string that concatenates the two values (highlighted in yellow) as shown below.
Expression | Data Type | Operator | Value |
=Cstr(Fields!CalendarYear.Value) | Text | In | =Split( CStr(Parameters!Year.Value) + ”,” + CStr(Parameters!Year.Value-1) , “,”) |
Note that in this case, I had to apply a string conversion on the year integer so that I could concatenate the parameter selection with the calculated year. Pay attention to the second argument of the Split function—you must use a comma delimiter for the result to work correctly with the In operator.
I also had to change the Expression value from [CalendarYear] (or =Fields!CalendarYear.Value) so that the expression would return a string that I could compare with the values in the string array.
More fun with filter expressions in future posts!
11 Comments
[…] the first post of this series, I introduced the use of filters within the report rather than in the query. I included a list of […]
[…] far in this series, I have described using the IN operator and the LIKE operator. Today, I’ll continue the series by reviewing the TOP and BOTTOM […]
[…] far in this series, I have described using the IN operator and the LIKE operator. Today, I’ll continue the series by reviewing the TOP and BOTTOM […]
Thanx you helped me a bit further with a problem about filtering reports.
Happy to help! 🙂
[…] in terms of rows returned, that would be filtered datasets and you’ll already find plenty of references on the internet. This article is about a varying number of columns in the dataset, which is a […]
This is the scenario I am trying to do: (In VS 2010 local reportviewer)
I have a table that has filter based on year (say 2012)..I am retrieving all data across all years from the DB and applying this year filter in reportviewer to display data specific to the year(say 2014). My report is embedded in a WinForm.
Table has around 15 columns however one column has to display prior year’s data (i.e., 2013). Since the table filter is set to 2014, I am unable to find a way to retrieve 2013’s data to display.
How do I achieve this? Please help!
Thank you
Without knowing your data source and report layout etc., it’s hard to give a specific answer. In general terms, though, I would suggest resetting the table filter to return both the year you want to show plus the prior year’s data – so in this case, 2013 and 2014. Another option might be to structure your data so that you get what you need for the prior year but “associate” it with 2014 so that it looks like a 2014 record even though the value is from the prior year.
I am filtering tablix. I set
Expression: [Country]
Operator: IN
Value: =”United Kingdom” & “,” & “Ireland”
and it returns nothing.
Thank you.
The IN operator is expecting a list of values stored in an array.
Thank you for having shared this topic