Musings of a technophile

Many people could tell you that when a piece of software doesn’t work correctly, or rather doesn’t do all the it should be capable of doing, I’ll be one of the first to point that out.  Being a programmer and a DBA certainly helps with this as since I have such a good knowledge of how stuff works from a back end perspective, even perhaps if I can’t see the actual code. Because of this, it’s often easy to know when something just wasn’t done right, even though making it work right should have been easy.

One case in point is the evaluations of expressions in SQL Server Reporting Services 2005.  To me the evaluations always seem backward, with the evaluation of a conversion before the evaluation of what is returned from the variable.

For instance, let’s say you have a parameter list for a parameter called StartDate

Nothing
1/1/2008
2/1/2008
3/1/2008

Now, if you know SSRS you may know that you can use a Nothing in order to do some special stuff with cascading parameters like using some parameters if a condition is true, otherwise use a different set of parameters (hence why you would want a Nothing in a list like this).  Now, for the eval of the function, you need to convert the values to a date by using CDate().  Let’s say you pick 2/1/2008 from our list above, the expression might read

= iif(Parameters!StartDate.Value = nothing,  “Use Custom Start Date”, CDate(Parameters!StartDate.Value))

Basically stating that if our parameter = nothing, we want to use a different parameter value, otherwise just display the start date in a datetime parameter.   Seems like it should work right?  I mean the evaluation of the entire expression relies on the StartDate and whether or not it is = nothing.  WRONG The problem is that SSRS converts ALL the Parameters!StartDate.Value first, and THEN evaluates which one was picked, and THEN runs the initial eval of the if statement.  This means that when it tries to convert the value which is nothing, it throws an error (even though we picked the 2/1/2008 ).

Before I tell you how to fix this, I just have to say that this is an incredibly stupid approach to evaluations in my opinion.  Why on Earth would you EVER want to convert all the values BEFORE evaluating which one was picked?  More over, as I said before the entire if statement relies on the fact that the StartDate should never equal nothing when it gets to the false condition.   Hopefully at some point (it didn’t happen in SSRS 2008 btw – it’s still like this) somebody at Microsoft will have a half a sense of proper coding and fix this.

Anyways – so how do you fix this?  Well, believe it or not the solution is rather simple – you just have to code the expression like you learned in the book “Programming for Beginners” Page. 1 .  The real expression for this is

= iif(Parameters!StartDate.Value = nothing, “Use Custom Start Date”,CDate(iif(Parameters!StartDate.Value = nothing,”1/1/1900″,Parameters!StartDate.Value)))

So basically we have to force feed the CDate function a date so it doesn’t error.  And no, it doesn’t matter what that date is – because it will never be used.  That makes perfect sense doesn’t it?

In any case, this is actually just the simple example of how SSRS 2005 evals are screwed up, I’ll make a post later on how it will REALLY screw you up if you try to aggregate certain records from different groups.