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.
10 thoughts on “Filters in an RDL: To Like or Not To Like”
instead you can use like =”*”+parameters!Contains.Value+”*”
That works *perfectly* Abdallah. Thank you so much for writing! Where did you find this documented? I looked all over for some indication that the escape character was something different and couldn’t find it.
Again thank you and I will write a followup post!
Thank you so much for writing! Where did you find this documented? I looked all over for some indication that the escape character was something different and couldn’t find it.
Good tool. I am able to read in everything in the SemanticQuery node into a new XPathNavigator object and then apply XSL to this fragment to produce a simple xml file with which i can work with to build my SQL query
Very nice tool for us
I looked all over for some indication that the escape character was something different and couldn’t find it.
I had a question from our Reporting Services book website that lead me to create a sample application to illustrate how to deal with filter arguments. IMHO, the mechanism used by the Report Designer is still not that great as it requires too much (non-VB-like) type coercion. That is, one constantly has to force the type of the TextBox values as well as the type of the left and right-hand side of expressions to match instead of having the report processor’s RDL interpreter just figure it out. I have attached a ZIP file that has the project used to illustrate several filtering techniques. Of course, it makes a lot of sense to filter the initial DataSet but you might want to give the user the ability to filter the fetched rowset so the query does not (necessarily) need to re-execute.
Link added. Thanks — good idea 🙂
Hi I have the same problem with an application development asp net, I would understand it but how do I pass a variable from the form? Great thanks and bye.
You pass a variable from a form to a report by creating an appropriate parameter in the report. Here is an example from something I happen to be working on today:
‘ load the correct report,
‘ set parameters:
Dim rp(2) As ReportParameter
rp.SetValue(New ReportParameter(“BroadcastSql”, pSql), 0)
rp.SetValue(New ReportParameter(“MessageBody”, pBody), 1)
rp.SetValue(New ReportParameter(“PrintType”, pType), 2)
.ReportPath = theRDL
… does this help?