Author 
Topic 

hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 08:01:33

I am trying to do a calculation that gives be GP % but when I do it on some it comes up error. My guess to this is even though some values are blank "Null" that's when it is bombing those numbers. Does the expression level give you the ability to use the code just like I did when in the query builder? SUM(ISNULL(Totalamt, 0) + ISNULL(Totalcost, 0)) AS TotalCostPL OR (ISNULL(HomeCurrAmt, 0)  ISNULL(TotalCostPL, 0)) / NULLIF (HomeCurrAmt, 0) AS GP
ISNULL((Sum(Fields!HomeCurrAmt.Value,0)) ISNULL(Sum(Fields!TotalCostPL.Value,0)))/ISNULL(Sum(Fields!HomeCurrAmt.Value,0) 
Edited by  hbadministrator on 06/20/2013 10:38:34


hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 09:09:09

let me break it down a bit more. I am trying to take Fields!HomeCurrAmt.Value  Fields!TotalCostPL.Value now each one of those could have NULL Values or 0 for them. Then once they are subtracted then take to total and \ by Fields!HomeCurrAmt.Value 


hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 09:15:00

I just tested this and this part works. =(Sum(Fields!HomeCurrAmt.Value) Sum(Fields!TotalCostPL.Value)) but as soon as I put the Divide thats when I end up getting the errors. Because I know it has 0's in it. /Sum(Fields!HomeCurrAmt.Value) 


hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 09:31:08

so frustrated!!!! 


hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 10:00:38

Ok while digging on line someone posted this and it is extremely close to what I am looking for but it is broke any help would be great full.
The expression in your comment doesn't look complete, so I can't tell what you tried next that didn't work. You definitely want to test for zero before performing division.
You can try this:
=iif( Sum(Fields!HomeCurrAmt.Value) = 0, 0, (Sum(Fields!HomeCurrAmt.Value)  Sum(Fields!TotalCostPL.Value)) / Sum(Fields!HomeCurrAmt.Value)) I tried this one above but still got the #error
Or to check for an empty value also, you can try this:
=iif( (Sum(Fields!HomeCurrAmt.Value) = 0 or IsNothing(Sum(Fields!HomeCurrAmt.Value)), 0,Sum(Fields!HomeCurrAmt.Value)  Sum(Fields!TotalCostPL.Value)) / Sum(Fields!HomeCurrAmt.Value))
This one sounds like it will work but there seems to be an issue when I get past the IsNothing. Right after the ,0 "," Second comma to execute the next part is underlined.
Sum(Fields!HomeCurrAmt.Value)  Sum(Fields!TotalCostPL.Value)) / Sum(Fields!HomeCurrAmt.Value))



hbadministrator
Posting Yak Master
113 Posts 
Posted  06/20/2013 : 10:38:00

I got it =iif(sum(Fields!HomeCurrAmt.Value)=0,0,iif(Sum(Fields!HomeCurrAmt.Value)=0,1, (Sum(Fields!HomeCurrAmt.Value)  Sum(Fields!TotalCostPL.Value))/iif(Sum(Fields!HomeCurrAmt.Value)=0,1,Sum(Fields!HomeCurrAmt.Value))))
This code will let you get a zero value for Null division.




Topic 
