NaN and the number format

To get the average for records that contain a value greater than 0, the below expression was created. If the average produces a “NaN” it displays “---“ instead on the report.

=IIf((Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001 > 0, 1, Nothing)))="NaN", "---", (Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing))))
I do not know how to format the result to display just two decimal places. I have placed “0.00” in the Number Format property, but this does not work.

SSRS

Last Comment

nulad

8/22/2022 - Mon

sammySeltzer

5/16/2011

Just out of curiosity, why not put "0.00" in place of "--"

like:

Question: To get the average for records that contain a value greater than 0, the below expression was created. If the average produces a “NaN” it displays “---“ instead on the report.

=IIf((Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001 > 0, 1, Nothing)))="NaN", "0.00", (Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing))))

nulad

5/16/2011

ASKER

I am sorry. I copied the incorrect expression. The below expression is being used.

=Replace(Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing))
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing)),"NaN", "---")

I can not use the "0.00" in place of "---", because that would indicate a 0 average. I want to make sure the user knows there were no entries to average.

Nico Bontenbal

5/17/2011

Because you use the replace function, the expression returns a string. So the format property will have no effect. You can use this expression to format the number with 2 decimals.

=Replace(formatnumber(Sum(IIf(Fields!DX_001.Value>0,Fields!DX_001.Value,Nothing)) 
/ Sum(IIf(Fields!DX_001.Value > 0, 1, Nothing)),2,false,false,true),"NaN", "---")

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

ASKER CERTIFIED SOLUTION

Nico Bontenbal

5/17/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION