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.
9 Comments
[…] 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 […]
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
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.
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
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
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!
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
[…] Prefiero hacer el filtrado en el backend. He escrito una serie sobre los fundamentos de SSRS 2005 (y aún está en curso). Para el parámetro básico, ver mi último post sobre la serie (Usando el Parámetro): dbalink.wordpress.com/2009/05/02/… Trabajo con los Informes de los Servicios de Filtros – Parte 2: El Operador LIKE […]
[…] Je ferais mieux de faire du filtrage sur le backend. J'ai écrit une série d'articles sur les bases de SSRS 2005 (et il est encore en cours). Pour le paramétrage de base, voir mon dernier post sur la série (à l'Aide du Paramètre): dbalink.wordpress.com/2009/05/02/… Travaillant de concert avec les Services de Filtres – Partie 2: L'Opérateur LIKE […]