SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Design level IIF Statement Divide By Zerro Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

113 Posts

Posted - 06/20/2013 :  08:01:33  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 06/20/2013 :  09:15:00  Show Profile  Reply with Quote
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)
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 06/20/2013 :  09:31:08  Show Profile  Reply with Quote
so frustrated!!!!
Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 06/20/2013 :  10:00:38  Show Profile  Reply with Quote
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))


Go to Top of Page

hbadministrator
Posting Yak Master

113 Posts

Posted - 06/20/2013 :  10:38:00  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000