Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Custom code for division throws an #Error message for NON EXISTING record. Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:25 AM
Points: 8, Visits: 69
Hi,

I get numerator from dataset and denominator from function and pass them to the custom code function which gives division result.

Something like below code:

=IIF(Sum(Fields!Value1.Value)<>0,Code.GetPerc(Sum(Fields! Value1.Value),(IIF(IsNothing(Code.GetValue2()),0,Code.Get_Value()))),0)
If record for Value1 does not exist then it gives #Error else it works fine.

How to handle non existing record in such calculation?

Thanks,
JM

Post #1487723
Posted Friday, August 23, 2013 6:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
I'm guessing you are seeing the division by zero error type error here?

You may be able to modify something like this to get what you are after. Swap out the 0 for is nothing.

=Iif(Sum(Fields!EXT_SALE_AMT.Value) = "0.00", "-100", Sum(Fields!MARGIN_DOLLARS.Value) / Iif(Sum(Fields!EXT_SALE_AMT.Value) = "0.00", "1", <<<<<<<<<<< If the value is 0 this makes it 1.
Sum(Fields!EXT_SALE_AMT.Value)))
Post #1487798
Posted Friday, August 23, 2013 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:25 AM
Points: 8, Visits: 69
Hi,

Thanks for your reply.

But it’s not division by zero error. I have handled that in code as follows:

Public Function DivisionFunction(ByVal Value_1 AS Decimal, Value_2 AS Decimal) AS Decimal
Dim bal As Decimal

if ((IsNothing(Value_1) Or Value_1 = 0) Or (IsNothing(Value_2) Or Value_2 = 0))
bal=0
else
bal=Value_1/Value_2
end if

return bal

End Function

Here, I get an error when I divide non existing value which is value1 (numerator) / Value2 (Denominator).

Value1 = sum of Value1 on child group (Child1, Child2,…….)

Value2 = sum of Value2 on parent group (Parent1)

I fetch Value2 from Custom Code function and send Value1 and Value2 for division.

Regards,
JM

Post #1487817
Posted Friday, August 23, 2013 1:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, August 16, 2014 9:15 AM
Points: 2,818, Visits: 2,557
I am not sure it will work with non existent values but you might want to check out the divide by zero code I posted here:
http://www.bidn.com/blogs/Daniel/ssas/1245/divide-by-zero-tweak
It might help.
Post #1488006
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse