Monday, October 13, 2008

Using A LIKE Operator With A String Parameter In SSRS

Hey-o,


Just something quick that I figured out today, that I felt warranted sharing. I'm doing some work with SQL Server Reporting Services (SSRS) and the Business Intelligence Development Studio (BIDS), creating reports for work. Today, I was asked to create one that used a LIKE statement in a WHERE clause where the argument passed to the LIKE statement is a string parameter. The challenge here is that concatenating "%"s to the parameter didn't work in the query, nor did the same process in the Dataset -> Filter By tab. The only possible answer I could find was on experts exchange,  which requires a subscription, so I had to figure it out myself. Here's what I did:

  1. Create the String parameter you actually want to search with. Make sure that it can be blank, but don't let it be null. For the sake of the example, I'm calling it Filter.
  2. Create another, hidden parameter that is also a string. Make sure it is hidden! Then, set the default value to be non-queried ="%" & Parameters!Filter.Value & "%". I gave this parameter the name FilterFormatted. Now, hit okay and close out your Report Parameters.
  3. Now, in the query, all you need to do is write "WHERE columntofilter LIKE @FilterFormatted". If you're testing the report, make sure you put the "%" before and after your search string, but going to the "Preview" tab will let you test it the way it'll be deployed (though you should probably know that already).
That's it! Interesting problem, simple fix, solution posted.

2 comments:

Anonymous said...

Or you could go:
WHERE columntofilter LIKE '%' + @Filter + '%'

NigelG said...

Yay! LIKE '%' + @Filter + '%' worked a treat. Thanks