SSRS Formula or expression to change NaN to 0

reporting-services expression nan ssrs-expression iif-function
JsonStatham picture JsonStatham · Jan 12, 2012 · Viewed 44.6k times · Source

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

Answer

hurleystylee picture hurleystylee · Dec 11, 2012

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"))

Related questions

Reporting Services Remove Time from DateTime in Expression

I'm trying to populate an expression (default value of a parameter) with an explicit time. How do I remove the time from the the "now" function?

Read More
SSRS Expression for IF, THEN ELSE

I am creating a field from tables with our shoretel phone system and i am intergrating reports via SSRS and i need some assisstance with an expression. =if(Fields!ExitReason.Value 7, then if (Fields!ExitReason.Value 1, else if (Fields!ExitReason.…

Read More
Using 'like' in ssrs expressions

I'm trying to highlight a field when the value has the word 'deadline' in it. I'm trying to use the expression: =IIf(Fields!Notes.Value like "%deadline%","Yellow","Transparent") in the BackgroundColor property. It's not highlighting the field (not changing …

Read More