Removing the decimal place from 100.0

Question

Removing the decimal place from 100.0

DSC123

SSChasing Mays

Points: 628
More actions
August 28, 2014 at 1:41 am

#311150

Morning folks
Please can someone help with the format of a field? I have a number field (well I have hundreds on a number of reports) and I need to remove the decimal place when the result is 100. Currently I have 1 decimal place eg 97.3. But when the figure is 100 I am returned with 100.0.
I have tried changing the format to ###.# and this works, but the problem is that if I have 97.0 it removes the 0 and I would like to see it.
Another solution is the expression:
= IIF(Fields!NumOfPupilsU5.Value >=0 and Fields!NumOfPupilsU5.Value < 100, FORMAT(Fields!NumOfPupilsU5.Value,"###0.00"), FORMAT(Fields!NumOfPupilsU5.Value,"#,##0"))
This works perfectly but I have too many fields to apply this individually to each one. Does anyone know of a custom number format where this will work.
Many thanks in advance.
Daz

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

daz.chauhan (8/28/2014)
Morning folks
Please can someone help with the format of a field? I have a number field (well I have hundreds on a number of reports) and I need to remove the decimal place when the result is 100. Currently I have 1 decimal place eg 97.3. But when the figure is 100 I am returned with 100.0.
I have tried changing the format to ###.# and this works, but the problem is that if I have 97.0 it removes the 0 and I would like to see it.
Another solution is the expression:
= IIF(Fields!NumOfPupilsU5.Value >=0 and Fields!NumOfPupilsU5.Value < 100, FORMAT(Fields!NumOfPupilsU5.Value,"###0.00"), FORMAT(Fields!NumOfPupilsU5.Value,"#,##0"))
This works perfectly but I have too many fields to apply this individually to each one. Does anyone know of a custom number format where this will work.
Many thanks in advance.
Daz

Quick thought, use the MOD and ROUND functions, ROUND([VALUE],MOD([VALUE], 1))

😎

Edit: code correction.

DSC123 SSChasing Mays Points: 628 More actions · Answer 2

Hi Erikur

Many thanks for replying. What I am looking for is a way to do this without having to use expressions as it will take me ages to manually edit each one.

I'm hoping for a solution which will allow me to apply a custom number format if it's possible.

Thank you