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:


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


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

Like I said before, this has been solved and posted several times before. I just wanted to give a more in depth answer and explicit instructions on how to embed code in your report.
Enjoy

Leave a Reply

Your email address will not be published. Required fields are marked *