Last post Aug 10, 2015 10:40 PM by Krunal Parekh
Aug 08, 2015 09:00 AM|dlchase|LINK
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.
Aug 10, 2015 10:40 PM|Krunal Parekh|LINK
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
Now in the Expression call the code like this.
Hope this helps.