THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Working with Reporting Services Filters–Part 1

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:

SNAGHTMLae7e7a9

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!

Published Tuesday, December 21, 2010 4:09 PM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ian Yates said:

Great series.  Just curious about your remarks with the comma requirement for the split statement.  If I was splitting a string like ABC|DEF|XYZ using delimiter | I should get the exact same array as if I split ABC,DEF,XYZ using delimiter ,.  

So the bit in yellow in your post could also be

=Split(

CStr(Parameters!Year.Value) + ”|” + CStr(Parameters!Year.Value-1)

, “|”)

I tend to use pipes or other "strange" characters (as far as the user is concerned) as they're less likely to be in the source data against which I'm filtering.

February 10, 2011 8:54 PM
 

smisner said:

Yes, you're right, Ian. I usually prefer odd characters too for the same reason. Not sure why I used a comma for this example, but I did!

February 10, 2011 9:02 PM
 

SarahR said:

Thank you, thank you, thank you!!! I have spent WAY too much time (weeks!) searching for a solution to my simple problem of how to use the IN operator with an Expression instead of a constant in a dataset filter. This solution worked like a charm and has been a godsend!

November 15, 2011 8:52 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement