Have a field that is calculating the scrap percentage of a part over a date range and is working fine except for when scrap is 0. I wrote an if statement to say if scrap is 0 display 0. Still shows Error
=iif(Sum(Fields!LaborDtl_ScrapQty.Value) > 0.00, (Sum(Fields!LaborDtl_ScrapQty.Value) + Sum(Fields!LaborDtl_LaborQty.Value)) / Sum(Fields!LaborDtl_ScrapQty.Value) * .1, 0.00)
where am I going wrong?
What is the error for this?
=iif(Sum(Fields!LaborDtl_ScrapQty.Value) > 0.00, (Sum(Fields!LaborDtl_ScrapQty.Value) + Sum(Fields!LaborDtl_LaborQty.Value)) / Sum(Fields!LaborDtl_ScrapQty.Value) * .1, 0.00)
I would try:
=IIF(IsNothing(Sum(Fields!LaborDtl_ScrapQty.Value)) OR (Sum(Fields!LaborDtl_ScrapQty.Value) = 0),0, (Sum(Fields!LaborDtl_ScrapQty.Value) + Sum(Fields!LaborDtl_LaborQty.Value)) / Sum(Fields!LaborDtl_ScrapQty.Value) * .1)still the same result.
The line with the error is the only line where scrap is 0.
Unfortunately SSRS does this great thing where it evaluates both the true and false conditions, which leads to errors when guarding against divide-by-zero.
The following tries an extra conditional and sets the divisor to 1 instead of 0 if it is 0, bypassing the divide-by-zero error. (I think I typed it in right)
=iif(Sum(Fields!LaborDtl_ScrapQty.Value) > 0.00, (Sum(Fields!LaborDtl_ScrapQty.Value) + Sum(Fields!LaborDtl_LaborQty.Value)) / iif(Sum(Fields!LaborDtl_ScrapQty.Value) = 0, 1, Sum(Fields!LaborDtl_ScrapQty.Value) * .1), 0.00)
That did solve the error and the output was 0 but the other fields were no longer correct being 100x larger. Much easier to solve that problem! Thank you.
EDIT:
Now that I am no longer trying to figure out why the error is happening I realize how awfully wrong my equation also was.