Changing NaN to 0

Question

This is my current expression below which works fine until the answer is zero and then I am presented with NaN in my report.

=sum(IIF(Fields!RestrictedTo.value = "Not applicable" or Fields!RestrictedTo.value = "Limited to item" or Fields!RestrictedTo.value = "room of origin" , 1, 0),"ADF") / sum(fields!total.value,"ADF")

I have tried using 'Is Nothing' in various ways, one example below, but can't get it work, would like some help please.

=IIF(IsNothing(sum(Fields!RestrictedTo.value = "Not applicable" or Fields!RestrictedTo.value = "Limited to item" or Fields!RestrictedTo.value = "room of origin" , 1, 0),"ADF")) / sum(fields!total.value,"ADF") , 0, sum(Fields!RestrictedTo.value = "Not applicable" or Fields!RestrictedTo.value = "Limited to item" or Fields!RestrictedTo.value = "room of origin" , 1, 0),"ADF") / sum(fields!total.value,"ADF")

score 0 · Answer 1 · answered Jan 16 '20 at 12:46

0

Try below expression

 = IIF(IsNothing(Fields!RestrictedTo.value) and IsNothing(sum(fields!total.value,"ADF")) ,
    0,sum(IIF(Fields!RestrictedTo.value = "Not applicable" or Fields!RestrictedTo.value = "Limited to item" or Fields!RestrictedTo.value = "room of origin" , 1, 0),"ADF")/ sum(fields!total.value,"ADF")
    )

answered Jan 16 '20 at 12:46

AnkUser

5,421
2
9
25

Thanks, unfortunately this didn't work, telling me the field 'RestrictedTo' isn't specifying a dataset aggregate – Clem_Fandango Jan 20 '20 at 16:19
I would say try one expression at a time meaning something like this, `IF(IsNothing(Fields!RestrictedTo.value) and and IsNothing(sum(fields!total.value,"ADF"), true,false)` – AnkUser Jan 21 '20 at 12:07

score 0 · Answer 2 · answered Feb 28 '20 at 01:57

Go to report properties, and in Code tab type in:

Function Divide(Numerator as Double, Denominator as Double)

If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator End If End Function

Then in your report enter the following expression:

=Code.Divide(sum(IIF(Fields!RestrictedTo.value = "Not applicable" or Fields!RestrictedTo.value = "Limited to item" or Fields!RestrictedTo.value = "room of origin" , 1, 0),"ADF") / sum(fields!total.value,"ADF"))

Changing NaN to 0

2 Answers2