Déjà Vu

It isn’t very often when I have to build reports for SSRS, it usually happens in bursts. I will write like 2-5 reports (sometimes more) and you always tend to find a bunch of shitty problems along the way, you tell yourself “Argh!!! I keep forgetting that it does that! I need to remember this!” Well I am having one of those moments again and this time I am writing it down. There is plenty of documentation on this, but I want to explicitly state the following:

IIF Is Logically Flawed

The IIF function is logically flawed in that it will evaluate both return conditions regardless of which path is chosen (true vs. false). Therefore if you are trying to prevent an exception from happening, usually arithmetic errors IE: Divide by Zero Errors or using a null value (Nothing in VB) for something that cannot take null, well IIF will screw you in this respect. This is best explained with an example:

Dividing by Zero Example

Let’s say you are trying to compute the following simple equation: (x – y)/x, well right away we can see that if x is zero, then you have just earned yourself a divide by zero error. In SSRS that results in a #Error where you are expecting to see a value instead. Not very helpful, however, if it didn’t pop-up already – your Error List at the bottom of your screen should pop-up and show you a warning.

More useful than #Error

This error reads:

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘LatestCost2.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero.

Well the first thing you are thinking is “@#*&^$ the if statement failed! WTF!”, well that is partially true pejorative remark. It did fail because it didn’t meet your expectations, but it isn’t an if statement, it is IIF statement which will evaluate both outcomes and choose the right path. I am sure there was a use for this at one point, but I would imagine by today’s standards this is just a wasteful statement. It is great for constants! Just not so much on statements that have the potential to throw exceptions.

The Gotcha

Let’s re-use the above equation (x – y)/x and let’s say that expression looks like this:

'This will evaluate both outcomes and therefore will result in an exception
=IIF(Fields!x.Value) = 0, 0, (Fields!x.Value - Fields!y.Value)/Fields!x.Value)

As explained before this will result in an error if x is zero. The most straight forward (and more efficient) fix in my opinion is as follows:

The Fix

Write a function for your report. If you are unfamiliar with how to do so then follow these steps it is easy:

1. Right click on a blank area of your report, NOT the report body (page) and select the Report Properties menu option. (Figure 1)
2. Click on Code (Figure 2)
3. Enter your Function using VB.Net syntax (I am not sure if everything is supported, play around).
4. Press OK to save
5. Go back to the expression that was giving you trouble and utilize your function like shown below:

Figure 1

Figure 2

Code Example

'Writing a function to take care of this annoyance
Public Function MyFunction(x As Decimal, y As Decimal) As Decimal
   If x = 0D Then
      Return 0D
   Else
      Return (x - y)/x
   End If
End Function

'Using the function in an expression
=Code.MyFunction(Fields!x.Value, Fields!y.Value)

Bonus Tip

If after changing your expression, your report generates without seeing #Error, but the stupid warning won’t go away, even after a re-compile, close Visual Studio completely and open it again. That should fix it.

Sources

• Specifically look at Jamie F’s answer on stack overflow
• Look for the heading on this page Suppressing Null or Zero Values at Run Time
• http://msdn.microsoft.com/en-us/library/ms156028.aspx
• http://msdn.microsoft.com/en-us/library/ms155798(v=sql.90).aspx