Monday, 26 November 2007

Reporting Services Divide by Zero Error in Report Expression

It’s a real pain – if I have a field that I want to calculate as one field divided by another, I have found that I get a divide by zero error when one of the fields is 0.

Normally, I would do a quick check on the field I am dividing by (the divisor) before using it in a calculation. In a field expression, the only way to include this kind of check is with an inline if statement. So I would have something like this in my report expression:

=IIF(Fields!Budget.Value = 0, "", Fields!Budget.Actual / Fields!Budget.Value)

But there is a problem with this. The False part of the IIF function still gets evaluated and your field shows #Error instead of a blank as expected.

There is a really simple way around this – create your own VB.NET function and call this from within the expression. Creating a VB.NET function is really easy as long as you are playing nicely in the sandbox (i.e. not trying to access any of the machine’s resources.) Here is a sample function that you can paste into the Code property of the report:

Public Shared Function VarPercent(ByVal Actual As Decimal, ByVal Budget As Decimal) As Decimal
If Budget = 0 Then
Return 0
End If
Return (Actual / Budget)
End Function


To use the function, just put the following in your expression:

=code.VarPercent(Fields!Actual.Value,Fields!Budget.Value)

That’s it!

If the divisor is 0, we return 0. We could of course return an empty string – although you can just as easily do this by formatting the field using a formatting expression that shows blank for zeros.

7 comments:

Anonymous said...

Thanks for your post, this helped me out of a jam...

Anonymous said...

Very nice. A good example is worth 1000 useless words!

Unknown said...

Another way that I figured out is as stupid as follows:

=IIF(Fields!Budget.Value = 0, "", Fields!Budget.Actual / IIF(Fields!Budget.Value = 0,1,Fields!Budget.Value))

Of course, in place of 1, you can put whatever number you like - it's there just to get a valid value instead of an error in the denominator.
But it's really a pain.

It realy looks like RS has lots of loose ends: this one (no error inspection formulas!), the list-only MDX, the hardly configurable and a littele buggy too charts...
It's like the budget has been suddenly cut out on a good team?

Anonymous said...

Great, thanks for this... I was stuck for an hour trying to figure out a work-around. I ended up using the method described in ggaggo's comment, thanks for that!

Dave Lowerre said...

Why is this not treated as a bug in SRS? Conditional evaluation has been around for a long time.

Just because there is a work-around doesn't mean it should not be fixed.

Dave Lowerre said...

I will say this: Your solution works.

It may help someone else if I point out that finding the custom code page in SRS is tricky. You need to be viewing the report properties in the properties window (which is not the same as the properties accessible from the Project menu: those are the project properties of course!) Then there is a little icon for 'property pages' that has a 'code' tab on it. The code property is not listed categorically or alphabetically.

So easy! But lets see a non-programmer figure it out!

Thank goodness MS keeps building junk like this to keep us all employed!

khao_lek said...

It goods solution.