locked
Error at iif with by zero division RRS feed

  • Question

  • Hi everyone.

    I have a division in cell expression. To avoid error by zero division, I set following function:

    =iif(iif(isNothing(sum(Fields!VAL1.Value)), 0, sum(Fields!VAL1.Value)) > 0,iif(iif(isNothing(sum(Fields!VAL2.Value)), 0, sum(Fields!VAL2.Value)) > 0, (sum(Fields!VAL2.Value)/sum(Fields!VAL1.Value)), "error VAL2"), "error VAL1")

    and it gives me error.

    However, if I change division by something else, it works. For example, it works with this:

    =iif(iif(isNothing(sum(Fields!VAL1.Value)), 0, sum(Fields!VAL1.Value)) > 0,iif(iif(isNothing(sum(Fields!VAL2.Value)), 0, sum(Fields!VAL2.Value)) > 0, (sum(Fields!VAL2.Value)), "error VAL2"), "error VAL1")
    

    What can be an issue?

    Regards,

    Yerkhan

    Thursday, October 17, 2019 11:59 AM

All replies

  • You need to check SUM of val1 for 0, and if it is 0 THEN you need set 1

    I cannot test your script right now but see if this helps

    =iif(iif(isNothing(sum(Fields!VAL1.Value)), 1, sum(Fields!VAL1.Value)) > 0,
    iif(iif(isNothing(sum(Fields!VAL2.Value)), 0, sum(Fields!VAL2.Value)) > 0, 
    (sum(Fields!VAL2.Value)/sum(Fields!VAL1.Value)),


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Zoe Gu Friday, October 18, 2019 5:36 AM
    Thursday, October 17, 2019 1:06 PM
  • Hi 

    Your Second code don't have division due to which it is working.

    Coming to the Point:- First one is failing as you are validating the sum is Nothing or Not. But, if the sum is o then its becoming 0/0 or 0/x or x/0 which would be leading to the error.

    Please find the below script which would fix the same.

    =iif(iif(iif(isNothing(sum(Fields!VAL1.Value)), 0, sum(Fields!VAL1.Value)) > 0,iif(iif(isNothing(sum(Fields!VAL2.Value)),0, sum(Fields!VAL2.Value)) > 0, (iif(sum(Fields!VAL2.Value)>0 && sum(Fields!VAL1.Value) >0,sum(Fields!VAL2.Value)/sum(Fields!VAL1.Value),"Zero Exists")), "error VAL2"),"error VAL1")

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, October 17, 2019 1:37 PM