need to truncate to 2 decimals but not rounding

Question

Post reply

need to truncate to 2 decimals but not rounding

SatishAyyar

SSCrazy

Points: 2825
More actions
June 18, 2010 at 1:13 pm

#223986

Hi All,
I have a number 20.34545. I need to format to 20.34 in SSRS. Please suggest.
Thank You,
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ankur_libra Hall of Fame Points: 3999 More actions · Answer 1

Hi,

You can proceed as follows in the query, which is being used to raise the report:-

select CAST(20.34545 AS decimal(10,2))

Result: 20.35

Hint:

Decimal (p,s)

where p (precision):- Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.

s (scale):- Specifies the maximum number of decimal digits that can be stored to the right of the decimal point.

Cheers:-)

With regards,

Ankur

Pham Trung SSC Enthusiast Points: 173 More actions · Answer 2

satishseth143 (6/18/2010)
Hi All,
I have a number 20.34545. I need to format to 20.34 in SSRS. Please suggest.
Thank You,

SELECT CAST(FLOOR(20.34545 * 100)/100 AS Decimal(5, 2))

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Hi,

Either you can follow the above method in the code in the DB side,

OR

In the rdl, right click the field-->Property-->Format , then select the required format.

Thanks & Regards,
MC

SatishAyyar SSCrazy Points: 2825 More actions · Answer 4

Actually I do not want on the DB side. I can explain it to you clearly. Here it goes - For example I have the data on DB side as 9.3456. Then I want this to display(just to display ,i.e format) in the report as 9.34, but the underlying data should be 9.3456(No change in the data that we get from database). Now when I export the report to excel the display is 9.34 but the underlying data should be 9.3456. I think you guys got it. In excel for calculations purpose I need the exact data what I get from the database. Please suggest.

Thanks in Advance

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Hi ,

Did you try the above method I have mentioned (formatting in the report side..? )

It will display the data in the report as well as in the excel as 2 decimal, but the underlying data will be as it is in the data base. So that if you do any calculation in the excel it will be on the exact data from the DB rather than the displaying data.

I hope this is what you want.

Thanks & Regards,
MC

SatishAyyar SSCrazy Points: 2825 More actions · Answer 6

Thanks for the reply. I did try all the options in the the format tab, but all the options in the format tab will do rounding of the number to two decimals. I need truncating to two decimals. So I need to write some expression in the format to get this done.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 7

You can do pretty much as Pham Trung suggested in an expression in the report as well...

=(INT(100*Fields!Value.Value)/100)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SatishAyyar SSCrazy Points: 2825 More actions · Answer 8

It did not work. I need the formating but the underlying data should be as it is. If I use the query you suggested in the format tab, I am not seeing any difference.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 9

My mistake....

Here is the correct way to do what you want....

Put this in the format for the field...

="""" & INT(Fields!Number.Value*100)/100 & """"

It will retain the original number but produce a custom format that is limited to 2 dec places with no rounding...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SatishAyyar SSCrazy Points: 2825 More actions · Answer 10

Man..this is superb.....it worked great. But whats the logic in this ?

SatishAyyar SSCrazy Points: 2825 More actions · Answer 11

Man ....this is superb....it worked great, but I could not get the logic. Can you please explain? Anyways thanks for that

murdakillemall Mr or Mrs. 500 Points: 527 More actions · Answer 12

satishseth143 (6/23/2010)
Man ....this is superb....it worked great, but I could not get the logic. Can you please explain? Anyways thanks for that

It looks like it's taking the original number - 20.34545

and multiplying it by 100 to move the decimal two places to the right - 2034.545

Then INT command is showing only the integer value, leaving a whole number - 2034

Then it's dividing that whole number by 100 to move the decimal two places to the left - 20.34

mister.magoo SSC-Forever Points: 47068 More actions · Answer 13

murdakillemall (6/23/2010)
satishseth143 (6/23/2010)
Man ....this is superb....it worked great, but I could not get the logic. Can you please explain? Anyways thanks for that
It looks like it's taking the original number - 20.34545
and multiplying it by 100 to move the decimal two places to the right - 2034.545
Then INT command is showing only the integer value, leaving a whole number - 2034
Then it's dividing that whole number by 100 to move the decimal two places to the left - 20.34

yes, that's right. this value is then wrapped in quotes to make a custom format specification e.g. "20.34"

it is a bit of a cheat because the formatting hides the underlying number to the extent that the value in the cell could be anything and it would still display as 20.34

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SatishAyyar SSCrazy Points: 2825 More actions · Answer 14

SatishAyyar

SSCrazy

Points: 2825

June 23, 2010 at 9:12 pm

#1184302

Thanks I got it. Really appreciated your help