7

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?

joao.arruda
  • 367
  • 2
  • 13
Amit Singh
  • 71
  • 1
  • 1
  • 2

2 Answers2

9

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:

Accessing Report Properties on BID 2008

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)

joao.arruda
  • 367
  • 2
  • 13
  • It is also useful when you need to put `0` instead of `null` values, that would appear in blank on SSRS. you can use `Code.Divide(<>, 1)` so if your Expression is `null`, it returns 0, otherwise it returns the value of the expression itself. – joao.arruda Dec 19 '14 at 01:33
  • Is there a way to return NULL instead of 0? I have a complex expression on a Matrix table and I am trying to return NULL. However, I keep receiving either NaN or 0 no matter what I try. – NonProgrammer Jul 27 '17 at 19:59
  • have you tried to change the Divide function to return `Null` instead of 0? are you trying to divide two numbers? – joao.arruda Aug 15 '17 at 14:34
  • 1
    This really helped me and is the most useful response I have seen anywhere regarding Nan and Infinity results. – Caroline Allen May 07 '19 at 07:24
1

If you're getting 0% and NaN I'd check check your field data types. You should have at least one non-integer, and both need to be numeric. I'd still expect to see some 1s, but this is a start at the 0% and NaN. In terms of your check, you only need to check the denominator to avoid a calculation error. Try:

=IIF(Sum(Fields!B.Value) <> 0 , Sum(Fields!A.Value) / Sum(Fields!B.Value), 0 )

Control your presentation (decimal places, % sign etc.) with the textbox Format properties, or the equation number properties.

Volvox
  • 611
  • 2
  • 7
  • 19