How to remove NaN and Infinity from a SSRS table

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:

picture showing user where to click in the reportThen 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:

SUM(Fields!A.Value)/SUM(Fields!B.Value)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s