locked
Calc percent gives #Error RRS feed

  • Question

  • User1510859543 posted

    We have an SSRS report with totals comparing current month to prior month and showing a percentage (+ or -) of change from prior month.  It is occurring in the total rows summarizing group and grand totals.  I have tried both of the expressions below and both give #Error when the prior month is zero. It works perfect if totals are > 0 or < 0 but not zero.

    =IIf(ReportItems!Textbox145.Value <> 0,((ReportItems!Textbox142.Value - ReportItems!Textbox145.Value) / ReportItems!Textbox145.Value),1)
    
    =IIf(Sum(Fields!PriorAmount.Value) <> 0,((Sum(Fields!CurrentAmount.Value) - Sum(Fields!PriorAmount.Value)) / Sum(Fields!PriorAmount.Value)),1)

    Textbox 145 is the prior total and Textbox142 is the current total.

    Saturday, August 8, 2015 9:00 AM

Answers

  • User1644755831 posted

    Hello dlchase,

    Please see: Add Code to a Report (SSRS)

    Can you please try this.

    Public Function Percentage(ByVal txt145Value As Decimal,ByVal txt142Value As Decimal) As Decimal
       Dim ReturnValue As Decimal
       If txt145Value > 0 OrElse txt145Value < 0 Then
           ReturnValue = (txt142Value - txt145Value) / txt145Value     
       Else 
    ReturnValue = 1 End If
    Return ReturnValue End Function

    Now in the Expression call the code like this.

    =Code.Percentage(ReportItems!Textbox145.Value,ReportItems!Textbox142.Value)
    

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 10, 2015 10:40 PM