How to change NaN value with 0 in SSRS

1
votes

I've got the below expression and even though I thought I was handling 'NaN' values properly I still get them when I click preview. Does anybody know how to change it, so that NaN values get replaced with 0. Thanks.

=0 + Sum(IIF(IsNothing(Fields!value.Value) = True, 0, Fields!value.Value)) / IIF(Information.IsNothing(Fields!value.Value) = True, 0, CountDistinct(Fields!Introducer.Value))

1
sql-serverreporting-servicesssrs-2012

1 Answers

0
votes

NaN means that the SSRS couldn't calculate the value. Looking at your expression, looks like it's because you could very easily end up with a divide by 0. If I understand your expression correct, I would do:

IIf(IsNothing(Fields!value.Value) OR (CountDistinct(Fields!Introducer.Value) = 0), 0,  Fields!value.Value / CountDistinct(Fields!Introducer.Value))