none
Expression displaying Error RRS feed

  • Question

  • Hi All, hope someone can help me with this.  I have SSRS 2008 and using BIDS 2005 to write a report.  I have a report that I'm calculating a column.  The calculation is (InvoicedAmount - Total Costs)/InvoicedAmount.  Sometimes InvoicedAmount = $0.  In those cases, my column was returning #Error because it was trying to divide by 0.  So, I tried both expressions below and neither work.  When entering the expression, neither show a syntax error and the report will run, but the column will show #Error and the output shows "The Value expression for the textbox 'Margin' contains an error: attempted to divide by zero." If InvoicedAmount is $0, I want to display $0, otherwise I want to run the calculation.  What am I doing wrong?

    =IIF(Fields!InvoicedAmount.Value=0,Fields!InvoicedAmount.Value,

    ((Fields!InvoicedAmount.Value-Fields!TotalCosts.Value)/Fields!InvoicedAmount.Value))

    OR

    =SWITCH(Fields!InvoicedAmount.Value=0,Fields!InvoicedAmount.Value,Fields!InvoicedAmount.Value>0,

    ((Fields!InvoicedAmount.Value-Fields!TotalCosts.Value)/Fields!InvoicedAmount.Value))


    Milissa Hartwell

    Monday, August 26, 2013 2:52 PM

Answers

All replies

  • Try this way:

    =IIF(Fields!InvoicedAmount.Value IS NOTHING, 0, ((Fields!InvoicedAmount.Value-Fields!TotalCosts.Value)/Fields!InvoicedAmount.Value)))

    Davide

    Monday, August 26, 2013 3:32 PM
  • Hi ,

    Try like this ,

    =IIF(Fields!InvoicedAmount.Value = 0, 0, 
    (Fields!InvoicedAmount.Value-Fields!TotalCosts.Value) / 
    IIF(Fields!InvoicedAmount.Value = 0, 1,Fields!InvoicedAmount.Value))

    The reason is here with similar example - http://www.allaboutmssql.com/2013/05/ssrs-rsruntimeerrorinexpression-value.html

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Proposed as answer by Alisa TangModerator Tuesday, August 27, 2013 1:56 AM
    • Marked as answer by milissa Tuesday, August 27, 2013 1:45 PM
    Monday, August 26, 2013 5:38 PM
    Moderator
  • Thank you!  This worked.  I read the explanation in the link and I would have never thought of that.  I spent a couple hours trying to figure this out.

    Milissa Hartwell

    Tuesday, August 27, 2013 1:55 PM