Typically this occurs when a field uses an expression in a report table were the expression tries to divide a number by zero (Or the field can populate with #Error when there is a NULL involved). The best solution to resolve this problem is to create a custom function.
Right click on the background of your report (i.e. just below where it says Design) and go to Report Properties as shown:
Then you can left click on Code and add enter the custom code below in the window provided:
Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then Return 0 Else Return dividend / divisor End If End Function
IsNothing() is used to avoid #Error and check whether the divisor or the dividend are 0 to avoid NaN and Infinity.
Now change the field expression from, for example:
To the expression below using the newly defined function Divide:
= Code.Divide(Sum(Fields!A.Value), Sum(Fields!B.Value))
Next you can update the fields textbox properties to make the number display as a percentage.
NOTE: Inserting a user defined function into a table will mean that that table will no longer be able to be copied and pasted as this throws an error. To copy and past the table you will need to look at the code of the report by right clicking on the report and choosing View Code. Search for “
Code.Divide” and comment it out with an apostrophe ( ‘ ). You will now be able to copy the table.
3 thoughts on “How to remove NaN and Infinity from a SSRS table”
THANK YOU for this! I’m new to the Code part and this helped me clean up a LOT of my expressions.
For my particular needs, I modified the function a bit to return Nothing if the result of the division was zero. Note to return NULL I had to make the function return Object…
Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Object
Dim Result as Double
If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
Result = CDbl(dividend / divisor)
If Result = 0 Then Return Nothing Else Return Result
Hi Barry, Glad I could help.
Thanks for contributing your work.
Thank you to both of you. I needed to return Nothing. This was very helpful. Works like a charm.