Design level IIF Statement Divide By Zerro Error - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Design level IIF Statement Divide By Zerro Error

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-06-20 : 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)

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-06-20 : 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

120 Posts

Posted - 2013-06-20 : 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

120 Posts

Posted - 2013-06-20 : 09:31:08

so frustrated!!!!

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-06-20 : 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

120 Posts

Posted - 2013-06-20 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources