Dealing with Null or "All" Parameters in MS SQL Reporting Services

I finally had something happen to me at work that I can add to this blog. Oh happy day, I feel as if I might faint.

Either way, I've recently become the SSRS "rock star" at my office, where even my bosses are stopping by and asking me questions about how to get their queries/formatting to work. It's not as glamorous as I thought it would be, and the groupies haunt my nightmares, but it's fun...ish.

Anyways, the person who was running the Reporting Services before me had an interesting way of dealing with using parameters to filter results in the SQL Query. I know there's a way to filter just using the wizard, but I'm of the opinion that it is probably faster (or at least more efficient) to do filtering in the query. For our filters, if the user doesn't want to use that filter, the first option says "All", but actually has a value of null. The way my predecessor would handle this would be to have a single query for every combination of null/not null parameters. So, if there was one parameter that could be null, there would be two queries, and IF statements to select which one. If there were two nullable parameters, that number raised to four. Three meant nine separate queries...et cetera. 

This wasn't really acceptable to me (mostly because I didn't want to have to add five more queries), so I looked to see if it was possible to use the IF statements right in the middle of the WHERE clause. No dice. So, what I did instead was use the following style of WHERE filter:

WHERE/AND (@param is null OR object.RelevantValue = @param)

This way, if the parameter the expression would still evaluate to true, and the filter statement would only matter if the parameter was actually specified. Now, the report query is 1/4 of its previous size, and 1/9th the size it would've been if I had done it the old way. Rock star? Maybe, maybe not, I don't care. This is just what I do.

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.