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.

Why I Hate Flash

I could apologize for this being the first post in two months, but I won't. This is my third rewrite, mostly due to my incompetence, but frankly it's beginning to wear thinly on me.

I'm currently taking a game design course at my university along with two of my friends. Woo and yay. A class that actually may have some bearing on what I might actually do with my life. Don't get me wrong, but I don't see any time in my future when explaining if the character in John Updike's A&P will be required of me in the line of work. Anyways, I love gaming, I love game design, I love talking about game design, and I like several people in the class. However, after our first meeting, I feel a little down. Why? This sentence:

"2D Games will be done in Flash."

Well, there's the chandelier made of rabbit droppings. I despise Flash, and apparently most people cannot understand why, so I'm going to lay it out here, as much for me as it is for the half-dozen other people who will read it.

First and foremost, I don't like Flash because I have to pay to program it. This, if you didn't know, is a bit of an anomaly in the programming world. With the exception of Flash, there isn't a single other programming language that you have to pay to use. Even Microsoft's .NET languages have an open framework, and if you'll really itching to do some Mac-Based VBscript, you can use the (open-source, no less) Mono project headed by Miguel de Icaza.

As a student, the cheapest I could get flash for is around $250. If I wasn't a student, this price point goes up to $700. I have no problem with Macromedia Adobe wanting money for their code, that's all well and good. However, there is NO WAY to do anything in flash without using their IDE. Microsoft's Visual Studio is a great IDE. It comes with lots of awesome tools built in, and that's effing sweet. It also costs around the same amount as Flash. I couldn't find the exact price point, mostly because I was bombarded with offers for trial and free versions. There is a free edition of almost every Microsoft Developers product. They're called the "Express" editions, and though they lack the bells and whistles of the paid versions, they still get the job done. In fact, if you're a student, you CAN get free versions of the full product. Yes, that's right, you can get Microsoft products for FREE, if you're a student.

The reason for this is that Microsoft wants to get students used to using their products and their languages. It's a little sinister when you think about it, them baiting you with freebies when you're a poor college student, but Adobe doesn't even attempt to do the same thing, and basically just flips you the middle finger.

When I bring this point up, most people say "Why don't you just pirate it? Problem solved!" No, you vacuum-craniumed dolt, I'm a programmer. I want to get paid for the programs I write. If someone wants to charge for their program, that's fine by me. I love open source, but hey, someone's got to make a living somehow. If I don't want to pay for their development environment, then that should be their problem, not mine. My problem is that I cannot do Flash development without buying their product. Stealing it would be similar to a wheat farmer stealing potatoes. It's just wrong. Hell, I'm sure that half the reason Flash is so expensive is to offset the lost profits from people pirating it.

The other big pro-flash reason I get is this: artists use it. Well you know what, screw the artists. Last time I checked, a game artist should be doing artsy things, like drawing or modeling or whatnot. My job is programming, not theirs. If some beret-topped punter is going to tell me that he likes using Flash because he can program in it, that let me get my paintbrush out, cram it up my bum, and start "composing assets." Images are all the same. A JPEG in Flash is a JPEG to PyGame, or OpenGL, or DirectX. You want vector graphics? Awesome. Fireworks does SVG. I checked. If artists want to start doing programming, then I might as well go fly helicopters, because apparently I'm not needed anymore.

As vitriolic as I'm being, there would be one simple thing that Adobe could do that would make them a-ok in my book. Don't sell the platform, sell the IDE. If I can program Flash using a text editor, I'd go for it. Eventually, once I'd made some cash, I'd see the benefits to using the IDE, and purchase it. Having an up-front cost is nothing but an unnecessary barrier that is a relic of a bygone era.

Also, I'm hoping my artist friends take my slurs against them as being all in good fun. ;-P

Open-ness: The Business Practice of...Tomorrow

I could explain why I haven't posted in a while, but I'm not going to. Anyways...

I still recieve the dead-tree version of Wired magazine, even though with how long it takes to get to me all the content is online, anda few of their stories really got me thinking. In one camp, there are the articles about how open-ness an freedom is the "new wave" and how businesses are doing well by "opening up." See this article for what I mean. However, this month's cover story (not to mention a slew of similar articles throughout the internet and traditional media) is on Apple's successes in the last couple of years, despite the fact that they're bucking the trend by being as "closed", or as I prefer "evil" as a tech company can be. Sure they're realeasing an iPhone SDK, but with enough stipulations that you basically have to be having your hand held by Apple the whole time if you ever want to do anything.

While I was thinking about that (my rants on Apple are usually more bile-filled, and much longer, consider yourself lucky,) I began thinkning about another tech company that the mention of doesn't fill me with seas of bloodcurdling rage: Nintendo. Not only are they completely locked down in terms of hardware and software, but their adherents and fanatics closely mirror each other when it comes to the amount of devotion said groups express. Everyone gives Sony a bad rap for being so proprietary (which is justly deserved) , but when you think about it, Nintendo is even worse. Their newest-gen console doesn't even play DVDs, for christ's sake. Imagine how (much more) awesome the DS would be if it took SD cards, played Mp3s/Oggs, and had an active and egaging (and supported) dev community. Yeah, there are "hacker" groups that do some cool stuff with it, but when you come down to brass tacks, if you want to make anything for the Wii, or the DS, or the PS3, or anything uncrippled for the 360, be ready to pay through the nose.

My point is thus: Open technologies are awesome, I love them, I can't imagine making anything nowadays without assuming it would be open somehow. HOWEVER, you can still make big bucks by staying closed, and until someone (I'm looking at you Google/Android,) can prove that being "closed" is going to lose to being "open," there isn't going to be a major shakeup anytime soon.