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

Division by Zero Error Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:24 PM
Points: 266, Visits: 692
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")


Post #1468315
Posted Friday, June 28, 2013 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:24 PM
Points: 266, Visits: 692
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")))

Post #1468630
Posted Sunday, June 30, 2013 4:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 1,787, Visits: 5,695
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1468899
    Posted Monday, July 1, 2013 10:12 AM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Wednesday, August 20, 2014 3:24 PM
    Points: 266, Visits: 692
    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.
    Post #1469145
    Posted Monday, July 1, 2013 2:16 PM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Wednesday, August 20, 2014 3:24 PM
    Points: 266, Visits: 692
    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.
    Post #1469214
    Posted Tuesday, July 2, 2013 1:09 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:42 PM
    Points: 1,787, Visits: 5,695
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1469319
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse