SSRS Formula or expression to change NaN to 0

Question

I am using the following expression to work out a percentage:

=Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name")

Days.Value is showing as 0 however in a few of my results instead of reading 0% in my percentage column it is actually reading NaN (Not a Number).

Does anyone know the exact expression forumla i need and where I should paste it in my current expression to say "Where NaN is showing, put a '0' instead?"

(See image) enter image description here

score 22 · Answer 1 · answered Dec 11 '12 at 15:15

22

I didn't have luck with the above answers. Here's what worked for me:

=IIF(Single.IsNAN(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name")), 0, Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"))

answered Dec 11 '12 at 15:15

hurleystylee

602
1
10
18

score 16 · Accepted Answer · edited Jan 14 '16 at 05:51

16

How about

=IIF(Fields!Days.Value > 0,Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),0)

edited Jan 14 '16 at 05:51

Pedram

6,256
10
65
87

answered Jan 13 '12 at 09:23

general exception

4,202
9
54
82

1

I tried something similar to this: =IIF(Sum(Fields!TotalPrice.Value)/Sum(Fields!Orders.Value) = 0, 0, Sum(Fields!TotalPrice.Value)/Sum(Fields!Orders.Value) netted me some weird results in a text box I deemed as currency. The answer above worked perfectly. Thanks! – John Waclawski Jun 13 '14 at 13:06
This solution still gave me the same error. I've answered this question with what worked for me. – hurleystylee Jul 06 '16 at 14:12

score 10 · Answer 3 · answered Nov 12 '14 at 05:16

10

I used this for similar case,

=REPLACE(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),"NaN","0")

answered Nov 12 '14 at 05:16

Sreedhar Chintakunta

145
2
15

score 5 · Answer 4 · edited Jul 13 '17 at 01:23

5

Here's another option. It should solve the problem, and also get rid of Infinite responses:

=val(replace(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),"NaN","0"))

edited Jul 13 '17 at 01:23

Rohit Poudel

1,793
2
20
24

answered Jul 12 '17 at 10:36

Ratsnake

51
1
1

score 3 · Answer 5 · answered May 27 '14 at 11:43

This is the simplest & best, I think,

=Switch(
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "NaN",Nothing,
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "Infinity",Nothing,
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "-Infinity",Nothing
)

You can also put a 0 instead of nothing.

score 3 · Answer 6 · edited Jun 16 '16 at 06:52

3

Try

=IIf(Fields!Days.Value Is Nothing Or Sum(Fields!Days.Value, "Date_month_name") Is Nothing, 0, Fields!Days.Value / Sum(Fields!Days.Value, "Date_month_name"))

edited Jun 16 '16 at 06:52

Pedram

6,256
10
65
87

answered Jan 13 '12 at 03:31

Cody Konior

727
1
7
19

score 0 · Answer 7 · answered Sep 04 '17 at 15:14

I had a similar issue to this and found that the following was easiest to do.

=Iif(
Fields!Days.Value.Value <> 0 AND Sum(Fields!Days.Value, "Date_month_name") <> 0
, Fields!Days.Value.Value/Sum(Fields!Days.Value, "Date_month_name")
, 0
)

Probably not the best solution, but works.

SSRS Formula or expression to change NaN to 0

7 Answers7

Linked