Remove NaN in Nested iif in SSRS expression

Question

New to SQL-SSRS

How do I add functionality to the below code to change returned NaN values to 0 or blank? What I think is happening is in situations that the sum of Agreed and Disagreed = 0 I am getting this Nan. I think this would be easier for me if I wasnt dealing with the Agreed and Disagreed categories. Again, new to SQL programming. Any help would be greatly appreciated.

=(Sum(iif(Fields!Response.Value = "Agreed",Fields!Days.Value,0)) + Sum(iif(Fields!Response.Value = "Disagreed",Fields!Days.Value,0)))/(Sum(iif(Fields!Response.Value = "Agreed",Fields!Fq.Value,0)) + Sum(iif(Fields!Response.Value = "Disagreed",Fields!Fq.Value,0)))

Simply replace NaN with 0... Or Use Single.IsNAN – Pedram Sep 13 '16 at 12:52 — Pedram, Sep 13 '16 at 12:52

score 0 · Answer 1 · edited May 23 '17 at 10:29

0

Try something like below,

=Replace((Sum(IIF(Fields!Response.Value = "Agreed",Fields!Days.Value,0)) + 
Sum(IIF(Fields!Response.Value = "Disagreed",Fields!Days.Value,0))),"NaN","0")
/ Replace((Sum(IIF(Fields!Response.Value = "Agreed",Fields!Fq.Value,0)) 
+ Sum(IIF(Fields!Response.Value = "Disagreed",Fields!Fq.Value,0))),"NaN","0")

Also, look at this

edited May 23 '17 at 10:29

Community

1
1

answered Sep 13 '16 at 12:55

Pedram

6,256
10
65
87

What you have written inside expression? – Pedram Sep 13 '16 at 15:23

score 0 · Answer 2 · answered Sep 13 '16 at 18:17

Rather than replacing the text returned from dividing by zero, you should handle the DIV 0 issue in the expression:

=IIF(
    SUM(IIF(Fields!Response.Value = "Agreed" OR Fields!Response.Value = "Disagreed", Fields!Fq.Value, 0)) = 0, 0, 
        SUM(IIF(Fields!Response.Value = "Agreed" OR Fields!Response.Value = "Disagreed", Fields!Days.Value, 0)))
/IIF(
    SUM(IIF(Fields!Response.Value = "Agreed" OR Fields!Response.Value = "Disagreed", Fields!Fq.Value, 0)) = 0, 1, 
        SUM(IIF(Fields!Response.Value = "Agreed" OR Fields!Response.Value = "Disagreed", Fields!Fq.Value, 0)))

If the SUM is zero, the calculation is 0 / 1 otherwise it's the SUM(Days) / SUM(Fq) where the Response is Agreed or Disagreed.

And rather than two sum, just use an OR in the one.

score 0 · Answer 3 · answered Dec 04 '17 at 22:18

Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
If IsNothing(Divisor) Or Divisor = 0
  Return 0
Else
  Return Dividend/Divisor
End If
End Function

this function enables you to write a custom code which fixes all problems with Nan . this can be called by below

=Code.Divider(Fields!FieldA.Value, Fields!FieldB.Value)

your code is as below

=Code.Divider(((Sum(iif(Fields!Response.Value = "Agreed",Fields!Days.Value,0)) + Sum(iif(Fields!Response.Value = "Disagreed",Fields!Days.Value,0))), ((Sum(iif(Fields!Response.Value = "Agreed",Fields!Fq.Value,0)) + Sum(iif(Fields!Response.Value = "Disagreed",Fields!Fq.Value,0)))))

Remove NaN in Nested iif in SSRS expression

3 Answers3