Reason for #Error in Dynamics NAV RoleTailored Client Reports
A Common Reason for the #Error in Microsoft Dynamics NAV RoleTailored Client (RTC) Reports
One of the common errors in RoleTailored Client (RTC) reports in Microsoft Dynamics NAV is #Error, and there are many reasons for it, but in this post I want to discuss one of the scenarios, which is the “Divide By Zero” exception.
If you have an expression which results in the Divide By Zero Exception, then the result you see is #Error. Typically, to avoid this, you can write an IIF statement in expression.
For example, to display the value as zero if the denominator is zero, then I would write something like this:
=IIF(Sum(Fields!LineAmt.Value)=0,0,Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value))
I am doing a quick check to see if Sum(Fields!LineAmt.Value) is zero, then I want to display zero; otherwise I evaluate my expression. It’s pretty simple. But here is the problem: The reporting processing engine will still evaluate the FALSE part—in this case, Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value), and the result will be #Error if Sum(Fields!LineAmt.Value) is zero.
There is quick work around to resolve this issue. You can write your own VB function or use an expression like the ones below:
1st Option:
=IIF(Sum(Fields!LineAmt.Value)=0, 0,Sum(Fields!NetGP.Value)/iif(Sum(Fields!LineAmt.Value)=0,1,Sum(Fields!LineAmt.Value)))
2nd Option:
VB Function:
Public Shared Function DivideByZero(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal
If Denominator = 0 Then
Return 0
End If
Return (Numerator / Denominator)
End Function
And use the following expression to call the function:
Code.DivideByZero(Sum(Fields!NetGP.Value),Sum(Fields!LineAmt.Value))
Using one of these options, you can quickly and easily resolve #Errors in the RTC caused by Divide By Zero exceptions.
ArcherPoint is delighted to offer tips to developers throughout the NAV community, but code samples are not warranted, as all installations are different. It is advised that the developer verify that the solutions work for his or her installation of Dynamics NAV before implementing the code.
For more information on topics related to Microsoft Dynamics NAV development, read the ArcherPoint Developer Blog, written specifically for Dynamics NAV developers, or contact ArcherPoint directly.