SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Division by Zero Error


Division by Zero Error

Author
Message
bpowers
bpowers
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1247 Visits: 960
Can anyone help me with the following? I am simply trying to add the three fields then divide by the last field. I have added a check for zero, where I want it to simply return Null if the field is zero and not perform the calculations. Any help will be greatly appreciated.


=
(
iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber")
,Sum(Fields!TotalInv.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber"))
)
/

iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0,"Null"
,iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")>0
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber")
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")



bpowers
bpowers
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1247 Visits: 960
I changed up the expression to test for zero at the beginning. The expression looks good and does not reflect any errors, but I still get the #Error in the field when the report is executed. Any ideas?


=iif(iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber")
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber"))=0,"Null",
(
iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber")
,Sum(Fields!TotalInv.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber"))
)
/
iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber")
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")))


mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10863 Visits: 7891
I can't see anything obviously wrong with the logic.

What I would do next is put two textboxes on the report, one for

=(
iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber")
,Sum(Fields!TotalInv.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))
+
iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber")
,Sum(Fields!TotalQTYonPMR.Value, "PartNumber"))
)


and one for

 = iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber")
,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber"))



Just to check they are returning the correct values.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • bpowers
    bpowers
    Ten Centuries
    Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

    Group: General Forum Members
    Points: 1247 Visits: 960
    I placed the logic in two new textbox expressions and they both returned the correct data. I am stumped as to why I am still getting the #Error. Thanks for the reply.
    bpowers
    bpowers
    Ten Centuries
    Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

    Group: General Forum Members
    Points: 1247 Visits: 960
    I got it to work using the following logic:


    =IIF(a > 0, 200/IIF(a = 0, 1, a), 0)



    Thanks for the help. Much appreciated.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10863 Visits: 7891
    bpowers (7/1/2013)
    I got it to work using the following logic:


    =IIF(a > 0, 200/IIF(a = 0, 1, a), 0)



    Thanks for the help. Much appreciated.


    My next question was going to be if you had any tiny values for "a", but glad you found something that works. And thanks for sharing that - could be helpful for someone else later.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search