

Old Hand
Group: General Forum Members
Last Login: Tuesday, May 17, 2016 12:10 PM
Points: 313,
Visits: 949


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")




Old Hand
Group: General Forum Members
Last Login: Tuesday, May 17, 2016 12:10 PM
Points: 313,
Visits: 949


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")))




SSCrazy
Group: General Forum Members
Last Login: Thursday, June 23, 2016 11:33 AM
Points: 2,137,
Visits: 7,463


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 problemsForum Etiquette: How to post data/code on a forum to get the best help  by Jeff ModenHow to Post Performance Problems  by Gail Shaw




Old Hand
Group: General Forum Members
Last Login: Tuesday, May 17, 2016 12:10 PM
Points: 313,
Visits: 949


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.




Old Hand
Group: General Forum Members
Last Login: Tuesday, May 17, 2016 12:10 PM
Points: 313,
Visits: 949


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.




SSCrazy
Group: General Forum Members
Last Login: Thursday, June 23, 2016 11:33 AM
Points: 2,137,
Visits: 7,463


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 problemsForum Etiquette: How to post data/code on a forum to get the best help  by Jeff ModenHow to Post Performance Problems  by Gail Shaw



