SQL Reporting Services: Dividing by Zero

You finally finish that report in SQL Reporting Services that you've been slaving over and find that there is a big ugly #Error text in the middle of your data. You can't publish that! But, how do you handle it? Lets cover a few ways of fixing this.

Sample data:

Budgeted	Spent	% Avail
1000	500	50.00%
0	100	#ERR

Your first try at protecting from the divide by zero error might be something like writing an expression in the value field like so:

= IIF( Fields!Budgeted.Value=0, 0, Fields!Spent.Value / Fields!Budgeted.Value)

This wouldn't work. Why? the Reporting Services compiler evaluates the results before the IIF. So, the compiler attempted the division by zero before applying the IIF expression. (Thanks Dataman!)

There are a few ways to handle this.

1. Denominator DeColorization
A quick way to avoid this is to write a little expression for the font color that will turn the font to white if the denominator = zero.

= IIF( Fields!budgeted.Value = 0,"White","Black")

2. Denominator De-Demonization.
This is the "Dataman" approach when you REALLY feel the urge to fix the data because otherwise the universe may be out of alignment. :)

= IIF( Fields!Budgeted.Value=0, 0, Fields!Spent.Value / IIF( Fields!Budgeted.Value=0,1,Fields!.Budgeted.Value))

3. Division SafeCode Routine.
This is pretty cool. In Reporting Services, in the Report Parameters there is a tab for Code. You can put in a custom function and call it in your report. The code would be like so:

Public Function SafeDivide(Numerator as Double, Denominator as Double) as Double
If Denominator = 0 then return 0
Return (Numerator / Denominator)
End Function

In your report you would just set the value like so:

>=Code.SafeDivide(Fields!Spent.Value, Fields!Budgeted.Value)

That's all for pesky #Error messages in your code.

Happy Coding!
P.S. Thanks goes to Charley for your input.
Jason
"We are the musicians. We are the dreamers of dreams." - Willy Wonka