Number Format

Question

Post reply

Number Format

David McKinney

SSChampion

Points: 10469
More actions
August 8, 2008 at 2:46 am

#190796

I've got a numeric(21,20) field in SQL. e.g. 1.23456000000000000000
I'd like this to display in Reporting Services as 1.23456 i.e. not showing the trailing zeroes.
As far as I can see, there is no number format that will do this for me....is there?
I could do it by converting to a string, and trimming it....but it's not too elegant.
Anything better?
Thanks,
David McKinney.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ravi -644861 SSCommitted Points: 1768 More actions · Answer 1

you can try formatnumber function

e.g. =FormatNumber(Field,2)

where 2 is the number of digits u want after decimal

David McKinney SSChampion Points: 10469 More actions · Answer 2

The number of digits after the decimal depends on the data itself i.e. is not known in advance.

Thus 1.234560000 -> 1.23456

while 2.340000000 -> 2.34

jais.ghan-823166 Old Hand Points: 322 More actions · Answer 3

Hi,

If u want to trim all the zero's,ie if the value of feild is 1.23450000,select in the properties>> FORMAT as Number.

then this will return values with out zero..zz...ie 1.2345

toolman-352714 SSCrazy Points: 2495 More actions · Answer 4

Here's info about SSRS' numbering formats along with links to MSDN's VB formatting info.

Using 123456789 as an example:

Currency - C or c = $123,456,789.00; Using precision specifier, C0 = $123,456,789 (that’s C number zero not C letter O); C1 = $…9.0; C3 = $…9.000; etc.

Percentage - P or p = 12,345,678,900.00% (Better example is 0.8976 with format code P = 89.76%)

Numeric - N or n = 123,456,789.00

Decimal - D or d = 123456789.00

Hexadecimal – X = 75BCD15; x = 75bcd15

Custom formatting: http://msdn2.microsoft.com/en-us/library/7x5bacwt(VS.71).aspx

Standard formatting: http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.71).aspx

HTH

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

David McKinney SSChampion Points: 10469 More actions · Answer 5

jais.ghan,

Thanks but that doesn't work with a numeric or decimal sql datatype.

toolman,

Thanks but I've already tried all of these, and they all have a fixed precision e.g. I can't have a different number of decimals in different rows.

I ended up with the following which converts the number to a string and then trims off the zeroes from the string. Not very pretty, but it does the job.

=rtrim(replace((replace(rtrim(replace(cstr(Fields!ManagementFee.Value),"0"," "))," ","0")+" "),". ",""))