NaN and Infinity values in SSRS

Question

I am using Visual Studio to build my report and I have tried to use those three expressions:

=IIF(Fields!A.Value = 0 or Fields!B.Value = 0, 0,SUM(Fields!A.Value)/SUM(Fields!A.Value)

=IIF(SUM(Fields!A.Value) = 0 or SUM(Fields!B.Value =0),0,SUM(Fields!A.Value)/SUM(Fields!A.Value)

=Replace(Round((((SUM(Fields!A.Value)/SUM(Fields!B.Value)))*100),0),"NaN","0")+"%"

The first 2 give me only 0 as % and the last one doesnt get rid of Infinity but does get rid of NaN.

Can someone please help where I can use both these expressions together and not get only 0% and Infinity when I use either the top 2 or the last one?

Answer 1

At first, you need to be sure that the fields you are using to calculate your value are really numbers.

Normally, you get NaN when trying to divide 0 / 0 or Infinity when you are dividing any number by 0.

So, a generic resolution for NaN, Infinity or even #Error (that happens when a null value is passed to a division) is to create a Function that will work on this values for you.

Right click on the background of your report and go to Report Properties as shown:

Then you can go on tab Code and add your custom code:

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

We use IsNothing() to avoid #Error and check whether the divisor or the dividend are 0 to avoid NaN and Infinity.

And then you replace your expression with:

= Code.Divide(Sum(Fields!A.Value), Sum(Fields!B.Value))

To make this looks like a percentage, I strongly recommend you change your Textbox properties. So, right click on it, go on Textbox Properties and over tab Number. Select category Percentage.

By doing this, you make sure that de SSRS will consider this value as percentage and not a text even when it is exported to other platforms such as Excel.

You can also check the Microsoft documentation on adding code to a Report (SSRS)