Rounding off Zeros in An Amount

Question

Post reply

Rounding off Zeros in An Amount

Stylez

SSCrazy

Points: 2195
More actions
April 30, 2012 at 10:21 am

#255598

Good Morning/Evening,
i would like to ask if you have tried rounding off the decimal values in SSRS? i have tried the round function but everytime i tried to run the report, the report says to me it has an error, the sample values are below
67448800.000000000000
21629640.000000000000
91857180.000000000000
15043000.000000000000
6193383.015000000535
18985958.000000008168
4228000.000000000000
please note that these values came from a column in an SQL table that has a Datatype numeric(28,12) and would love if you guys could help me in rounding off the decimals and making them appear as 2 decimal places in the report. could i achieve this using the built in functions in SSRS? if so please advise
Thank you and God Bless.

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

ROUND isn't changing the data type, so the numeric(28,12) still applies (which is padding the 0's back in).

Two choices: either handle the truncation within the report OR add a cast in to a shorter NUMERIC. Here's an example:

;with m as (

select cast(67448800.000000000000 as numeric(28,12)) n union all

select 21629640.000000000000 union all

select 91857180.000000000000 union all

select 15043000.000000000000 union all

select 6193383.015000000535 union all

select 18985958.000000008168 union all

select 4228000.000000000000)

select ROUND(n,2), -- the old result

CAST(ROUND(n,2) as numeric(28,2)) -- the newer formatted result.

from m

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Stylez SSCrazy Points: 2195 More actions · Answer 2

Thanks Matt,

I was able to do the Data manipulation in SSRS and worked fine for me. i also tried Your Sample Solution and this also worked perfectly to my report as well.

Much Thanks

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

In SSRS go to the properties for the field and look for a format property and put in N2. You could also go to the TextBox properties dialog on the Numeric tab and set it to use 2 decimal places.