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%'
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_'
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.