Last week I wrote about my inability to use the "Like" filter on a table in an RDL.  I wasn't able to use "Like" and devised another solution that uses "Equals" instead.  I went on to show how you could make it more re-usable, etc, but asked whether readers had any ideas.

I'm happy to say that one of you did. Abdallah Elhawary wrote in to explain that the wildcard character is actually supposed to be a "*" rather than the standard SQL ones.  I'm still scratching my head over where this is written in the docs, because I swear I've looked.

At any rate it does work; you can do this successfully:

... so "Like" really does work.

Or... we could talk some more.

(That's a quote from Serenity -- I'm seriously behind quota. )

If you want to write something that ports to multiple reports more easily, and isolates the set of fields to be compared for each report table, you're still going to write a function something like what I showed in the other post.

What's more, it turns out that while my weird workaround provides a way to handle case-sensitivity in the comparison at the whim of the user, the Like filter is not going to give you the same ability. 

In fact, looks to me (not testing!) as though if the database does a case-insensitive comparison, that's what you get, and if the database is case-sensitive, that's what you get.

Since this probably means you're doing the filter before dragging the data, it's probably more performant (note again: I haven't tested this assumption about how it works!). So sometimes Like is just what the doctor ordered.

Other times, though... and here I'll quote from another great movie... you just gotta learn to be more flexible. When you're searching free-form text, the user wants to specify case-sensitivity, whole word only, etc.  And with just a little extra work, we can do those things.

Isn't it wonderful that we have both choices?

Thanks again to Abdallah; I have always been a believer in Love but he has restored my faith in Like.

This is YAPS (yet another post on a subject) on fun with Reporting Services parameters. 

I'm writing this entry in hopes that either somebody will straighten me out about "like" filters in Report Services table properties, or that, if you can't, you'll be able to use my workaround instead. 

Here's the scenario:

I have a requirement to add a "free form text search" in an easy way, potentially to many different presentations of data from a time-logging database. 

The database allows free-form descriptions of time being logged, as many do, and we want to allow people to explore their report results according to whatever personal method of categorizing time they may have developed as part of the descriptions.  We might want to restrict the free text search to the specific description field or expand it to include additional fields (such as time category labels) in some cases. Either way, we want to be able to add it easily to any existing report on the database, so we don't want to add a parameter that goes back into every stored procedure for every RDL.

So we want a basic substring or "contains" type of search of string data, and I thought this is what the "Like" filter type should provide. No?

Well, I couldn't get it to work.

So I went after something that would work. Just for good measure, I decided to include an extra requirement: the user should be able to specify case-insensitivity.

Here's what I did; I'll use a sample report from AdventureWorks, with the expectation is that I can show this to my team and they can add the feature into any existing report without touching the SQL statements.

First, I added some parameters to the report, to allow the user to specify a filter.  It's important that the "Contains" value be completely optional, so I used blank as the default value and then planned to take care of a blank value as a special case in my filter expressions:

The screenshot below shows the filter condition I used.  The operator is "equals", not "like". On the right side, you can see that I am comparing to a filter value of =True. To clarify: the expression True (boolean), you need the = sign, not the string value True. On the left side, I'm first determining whether the user has provided a filter value and, if not, returning the expression True as well.  If the user has provided a filter expression, I'm concatenating the desired fields for search together and checking for the filter value in the full string.  There is a nested IIF() to allow me to do this twice, depending on case-sensitivity:

... I can tell the team to substitute their own set of fields for the concatenated expression in each report, as required, in the three instances they appear in this expression -- or, more likely, to substitute a quick function as custom code in the report. 

Please note the way that I am passing the values to the custom code in this screenshot, and then supplying their types in the function arguments list in the subsequent screens. Please also notice the Intellisense warning in this dialog; you won't be able to get Report Designer to "understand" and confirm your custom function name, but that doesn't prevent it from working:

... and the team could use the following function code, requiring only one internal line to be changed on a per-report basis:

Et voilà, here are the results in my sample report:

Just for grins I added a label into the sample report to express the filter selection as well; you can see it above, in red.  For completeness' sake, here's the label expression:

... a lot of effort, you say? Well, maybe.

Is there a better solution?  You tell me.