Sol Web Solutions Blog

Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.

Division By 0 Error

Posted by Sol Web Solutions Blogger
Sol Web Solutions Blogger
Sol Web Solutions Blogger
User is currently offline
on Monday, 28 February 2011
in General Web 0 Comments

Divide by zero errorAfter 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
)

Interactive sorting even works too via the column headers!

Hits: 515
0 votes
Trackback URL for this blog entry

Comments

No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 18 May 2012

Facebook Invite

Please log in through Facebook to invite your friends to this site!

Facebook LiveStream

Newest Customer

Buffalo Tours

Tour the community and peek at Los Alamos National Laboratory with a native Los Alamos guide. Our comfortable van tours are rich in history, archaeology, geology, and beauty. New Mexico is our home.
Buffalo Tours in Los Alamos :: Tour the community and peek at Los Alamos National Laboratory with a native Los Alamos guide

Constant Contact

Facebook Login

Login With Facebook

Like Sol Web

HostGator Hosting

Seussical The Musical

Los Alamos Little Theatre Seussical