Working with Reporting Services Filters – Part 2: The LIKE Operator

December 30, 2010
Tags: ,

In 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 filter operators, and then focused on the use of the IN operator. As I mentioned in the previous post, the use of some of these operators is not obvious, so I’m going to spend some time explaining them as well as describing ways that you can use report filters in Reporting Services in this series of blog posts.

Now let’s look at the LIKE operator. If you write T-SQL queries, you’ve undoubtedly used the LIKE operator to produce a query using the % symbol as a wildcard for multiple characters like this:

select * from DimProduct where EnglishProductName like ‘%Silver%’

SNAGHTML91bd74b

And you know that you can use the _ symbol as a wildcard for a single character like this:

select * from DimProduct
where EnglishProductName like ‘_L Mountain Frame – Black, 4_’

SNAGHTML92af546

So when you encounter the LIKE operator in a Reporting Services filter, you probably expect it to work the same way. But it doesn’t. You use the * symbol as a wildcard for multiple characters as shown here:

Expression Data Type Operator Value
[EnglishProductName] Text Like *Silver*

Note that you don’t have to include quotes around the string that you use for comparison.

Books Online has an example of using the % symbol as a wildcard for a single character, but I have not been able to successfully use this wildcard. If anyone has a working example, I’d love to see it!

UPDATE: Thanks to loyal reader Pravin, I can confirm that the ? character is the wildcard for the single character.

7 Responses to “Working with Reporting Services Filters – Part 2: The LIKE Operator”

  1. [...] 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 [...]

  2. Hi Stacia,

    I’m having trouble with report filters and specifically the like and in operators. My problem is that I want to apply two filters to two different parts of the report that are complementary (i.e. together they cover all of the data), but because there is no “not in” or “not like” I can’t ensure that, for example, if one of my filters is a list of values that the other filter will return everything excluded by the first filter. I’ve had to hard code all the values into two “in” statements, but theres the risk that if new values are added they will be excluded by these filters. Do you have any thoughts on this?

    Thanks,

    Mat

  3. Hi Mat,

    A very good question and deserved a whole blog post of its own: http://blog.datainspirations.com/2011/01/20/working-with-reporting-services-filters-part-4-creating-a-not-in-filter/. I hope that helps.

  4. Hi Stacia,

    I think there is mistake in Books online.

    You can use ? symbol as a wildcard for a signle character, I have been using these options… let me know if you still face problem with single character wildcard.

    I have been regular reader of your books and blogs. I really impressed by your great work towards community,

    Cheers,
    Pravin Patel.
    Mumbai, India

  5. Hi Pravin,

    Yes, I think Books online is definitely in error. Thanks for the tip on using the ? symbol! And thanks for being a loyal reader, too!

    Cheers,
    Stacia

  6. Thanks – I think I am close, but I have still having one issue. I am needing to use a LIKE statement that also includes a report parameter. So, users will enter a text string, or strings, as the parameter and I want to display anything that includes their strings. For example, the users would enter:

    Curd
    Skewer
    Lunch

    This would be entered in the parameter when they run the report. The parameter is setup to allow multiple items.

    In the query I am trying to call this parameter. By WHERE statement includes
    WHERE tblMenuItems.Description LIKE ‘*’ + @Description + ‘*’

    Any thoughts/suggestions?

    Thank you!

  7. Hi Chris,

    I think your best bet for that scenario is to create a user-defined function to split the value of @Description – which will be a comma-delimited string – into a table and then you can evaluate each row in the table individually with the LIKE operator.

    Cheers,
    Stacia

Leave a Reply