none
divided by zero error in ssrs RRS feed

  • Question

  • Dears,

    I am using below expression in my ssrs report.here i am dividing one value with another ,first expression has some value and second expression has zero,here i am getting the result as #ERROR but i wanted to show it as 0,could anyone help me here.

    =(Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))/((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+((Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2")))

    Regards,

    Suresh

    Thursday, October 26, 2017 6:24 AM

Answers

  • Hi,

    May I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer.  This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

    Regards,

    Henry 

    • Marked as answer by ds_999 Monday, January 1, 2018 7:19 AM
    Tuesday, October 31, 2017 1:22 AM

All replies

  • you need to make it like below

    =IIF (((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+((Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2")))>0,(Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))/IIF(((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+((Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2")))>0,((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+((Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))),1),0)
    Assuming all the sum expressions coming after /  are part of same calculation



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, October 26, 2017 6:50 AM
    Thursday, October 26, 2017 6:49 AM
  • Hi ds_999,

    How is your original dataset like? Also, as you mentioned, basically the expression is one value dividing another value. If the second expression

    ((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+((Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2")))

    is 0, it is not allowed to be the denominator, so it will show #ERROR

    You could try with expression below: 

    =IIF(

    ((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+(Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    (Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))) =0,0,(Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))/((Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2")+Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))+(Sum(Fields!Planned_3rd_Part_Cost.Value, "DataSet2")-Sum(Fields!Actual_3rdParty_Cost.Value, "DataSet2"))+
    (Sum(Fields!Planned_Resource_Cost.Value, "DataSet2")-Sum(Fields!Actual_Resource_cost.Value, "DataSet2"))) 

    )

    Best Regards,

    Henry 

    • Proposed as answer by Henry Jiang Monday, October 30, 2017 7:58 AM
    Friday, October 27, 2017 2:30 AM
  • Hi,

    May I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer.  This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

    Regards,

    Henry 

    • Marked as answer by ds_999 Monday, January 1, 2018 7:19 AM
    Tuesday, October 31, 2017 1:22 AM