Standard T-SQL escape characters don’t work in reporting services filtering
If you’ve ever filtered the data within the report using reporting services GUI, you may have realized that “%” as an escape character (wildcard) won’t work and you need to replace it with asterisk(“*”) instead.Let’s say you want to filter a field of the dataset called “Fields!Employees_Name.Value” to only contain employees whose first names start with “Jo”. Obviously there are two ways to do so:
- Return the filtered data from the data source and just show it in the report
- Return the raw (non-filtered) data from the data source and just filter it out either on data source level or the container bound to the data source (In this case a Table)
Well, I usually go for the first option, but there are some cases that your data source is shared between couple of containers in your report and you cannot filter it from the scratch. In these circumstances you got to define custom filters on container you wish to show data in it.If you right click on the table object in the report designer, select properties and choose the “Filter Tab” ,you can define the filters there using different operators ( = ,<>,Like and etc) . In our example we expect something like
Expression Operator Value
Fields!Employees_Name.Value Like Jo% (WRONG)
Amazingly this doesn’t work.I don’t understand why the standard T-SQL escape characters are changed in reporting services for Like operator!? , but the right way of doing that is as follow:
Expression Operator Value
Fields!Employees_Name.Value Like “Jo*” (RIGHT)
Now the question is what if I want to filter all the names starting with “Jo” OR “Te”?.First thing you may do is to add another line, and then you are tapped because GUI by default “AND” the conditions instead of “OR” and the GUI doesn’t let you change “AND” to “OR”, because the operator “LIKE” requires exactly 1 and only 1 filter value.If you still want to have a wildcard in both statements, then I guess you cannot do it in that way (you’d better change your data source to return the filtered data), but if you know exactly what you are looking for then there is a workaround which is directly changing the RDL (XML –Formatted) code. You need to use operator “IN” instead of “Like” as follows:
<Filter>
<FilterExpression>=Fields!Employees_Name.Value</FilterExpression>
<Operator>In</Operator>
<FilterValues>
<FilterValue>John</FilterValue>
<FilterValue>Johan</FilterValue>
<FilterValue>Johnoton</FilterValue>
</FilterValues>
</Filter>
</Filters>
