Filters in an RDL: To Like or Not To Like

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

[Update: At the bottom of this post, I asked for suggestions/alternatives, and I received a good solution.  Please see this post https://spacefold.com/lisa/2008/09/16/
YAPS-on-Filters-in-an-RDL-Whats-not-to-Like for more information.]

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.