# Rounding Annual_rt to the nearest 1,000th

Status
Not open for further replies.

#### scotton

##### Technical User
Okay, I saw one reply to an earlier post regarding rounding to the 1,000th but I haven't been able to get it to work:
1. Add 999 to your original field.
2. Truncate to a derived 2 digit integer field.
3. Multiply by 1000 with result in an appropriate size derived field to accomodate your largest possible answer.

What I did was: round( SUM("SYSADM"."PV_JOB"."ANNUAL_RT"+999) ,-3.5)

This works for most employees but some it doesn't round correctly. Ex: \$21,166.00 rounds to \$22,000 great. Another employee making \$23,670.00 rounds to \$25,000 bad, I need it to round to \$24,000.

What am I doing wrong?
TIA,

Sarah

In Oracle/PLSQL, the round function returns a number rounded to a certain number of decimal places.

The syntax for the round function is:

round( number, [ decimal_places ] )

number is the number to round.

decimal_places is the number of decimal places rounded to. This value must be an integer. If this parameter is omitted, the round function will round the number to 0 decimal places.

For example:

round(125.315) would return 125
round(125.315, 0) would return 125
round(125.315, 1) would return 125.3
round(125.315, 2) would return 125.32
round(125.315, 3) would return 125.315
round(-125.315, 2) would return -125.32

Copied from:

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

Ex: \$21,166.00 rounds to \$22,000 great. Another employee making \$23,670.00 rounds to \$25,000 bad, I need it to round to \$24,000.
How can I solve this rounding issue
I am using reportsmith ...

SCott

I am having the fallowwing problem ..can someone help ...

kay, I saw one reply to an earlier post regarding rounding to the 1,000th but I haven't been able to get it to work:
1. Add 999 to your original field.
2. Truncate to a derived 2 digit integer field.
3. Multiply by 1000 with result in an appropriate size derived field to accomodate your largest possible answer.

What I did was: round( SUM("SYSADM"."PV_JOB"."ANNUAL_RT"+999) ,-3.5)

This works for most employees but some it doesn't round correctly. Ex: \$21,166.00 rounds to \$22,000 great. Another employee making \$23,670.00 rounds to \$25,000 bad, I need it to round to \$24,000.

What am I doing wrong?

SCott'

From:
ROUND(n,m)

ROUND returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

All you should need is ROUND("JOB"."ANNUAL_RT",-3)

Why are you using the SUM and +999? If you using SUM because you are getting many records because of linking tables together you prob need SUM(DISTINCT ). Otherwise you rate will be overstated by the number of records returned by the table link.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

I am using report smith on sql , this is the result that I want

rounded up to the next \$1000.

Example
88,200 = the report should print this 89,000.

44,000.21 = should print this 45000
44.000.00 = should print this 44000
45,200.00 = should print this 46000
44,700.00 = should print this 45000

Thank you for taking the time to respond to my fist question

This will do it but FYI it is not really "rounded up to the next \$1000."

CASE
WHEN ("JOB"."ANNUAL_RT" - TRUNC("JOB"."ANNUAL_RT",-3)) <> 0 THEN
(TRUNC("JOB"."ANNUAL_RT",-3) + 1000.00)
ELSE "JOB"."ANNUAL_RT"
END

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

HO!

Thank you for the support ... But something is missing ..I am not on Oracle .. I am in sql database the function TRUNC does not exist .. But I tried to replace it with @INT function but it did not work, try it using an sql database running report smith ... I think the key to this problem will be to find a function in reportsmith sql that can beheave like TRUNC(number, -3)...

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

You are going to need a macro derived field. I can write it but it would be beyond the educational scope of this fourm. You really can't teach macros in a fourm.

It's at this point you may want to look for a consultant to help.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

I had this issue too and I saved the file into excel and used the round function in excel to get the correct amount. If you use =round(a3,-3) it will round correctly for you. A3 being the field with the \$23,670.00.
I know it would be nice to get this to work in Reportsmith but if you don't have the time to hassle with it and you can save it into excel I would do that.
Good Luck,

Sarah

This Derived Field should do it for SQLBase users:

@ROUND(field_name_here + 500,-3)

Tim

This Derived Field should do it for Oracle users:

ROUND(field_name_here + 500,-3)

Tim

I know nothing about MS SQL but it should be similar to one of these.

Status
Not open for further replies.

Replies
0
Views
93
Replies
0
Views
83
Replies
0
Views
98
Replies
0
Views
77
Replies
0
Views
83