Sol Web Solutions Blog
Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.
Division By 0 Error
After much searching to try to get around the division by zero issue, I had to create a custom code function for both the report rendering and interactive column sorting to work.
First, I created the following code function in the Report>>Code tab:
Public Shared Function VarPercent(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal
If Denominator = 0 Then
Return 0
End If
Return (Numerator / Denominator)
End Function
Using this function I could call different areas that I needed to report on...i.e. CPI and SPI (cost and schedule performance indexes).
Next, the difference between the current month and the previous month was needed to bring about areas that might need special attention.
The usual formula such as the one below works great as long as you always have values greater than 0 for the denominator (ACWP for CPI and BCWS for SPI).
=(
Fields!BCWP.Value / Fields!ACWP.Value
/
Fields!PreviousBCWP.Value / Fields!PreviousACWP.Value
)-1
Of course this did not work because of the division by 0 possibilities. So, the next option was to perform the following checks in every portion of the IIF function because every area is evaluated no matter what the boolean result:
=iif(
Fields!ACWP.Value = 0,1,
Fields!BCWP.Value / iif(Fields!ACWP.Value = 0,1, Fields!ACWP.Value)
)
/
iif(
iif(
Fields!PreviousACWP.Value = 0,1 ,
Fields!PreviousBCWP.Value / iif(Fields!PreviousACWP.Value = 0,1, Fields!PreviousACWP.Value)
) is Nothing, 1,iif(
Fields!PreviousACWP.Value = 0,1 ,
Fields!PreviousBCWP.Value / iif(Fields!PreviousACWP.Value = 0,1, Fields!PreviousACWP.Value)
)
)-1
...still, no luck.
Finally, I used the code which defined my function (as shown above) to get to the resolution I needed >> no errors when performing the difference calculation, and at least show 0 when appropriate. Hence:
=iif(
code.VarPercent(Fields!PreviousBCWP.Value,Fields!PreviousACWP.Value) = 0, 0,
(
code.VarPercent(Fields!BCWP.Value,Fields!ACWP.Value) /
iif(
code.VarPercent(Fields!PreviousBCWP.Value,Fields!PreviousACWP.Value) = 0, 1,
code.VarPercent(Fields!PreviousBCWP.Value,Fields!PreviousACWP.Value)
)
) -1
)





Leave your comment