## Rounding Annual_rt to the nearest 1,000th

## Rounding Annual_rt to the nearest 1,000th

(OP)

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

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

## RE: Rounding Annual_rt to the nearest 1,000th

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:

http://w

CharlesCook.com

ADP - PeopleSoft - SAP

ReportSmith - Crystal Reports - SQR - Query - Access

Reporting - Interfaces - Data Mining

## RE: Rounding Annual_rt to the nearest 1,000th

How can I solve this rounding issue

I am using reportsmith ...

SCott

## RE: Rounding Annual_rt to the nearest 1,000th

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'

## RE: Rounding Annual_rt to the nearest 1,000th

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

## RE: Rounding Annual_rt to the nearest 1,000th

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

## RE: Rounding Annual_rt to the nearest 1,000th

roundedup 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

## RE: Rounding Annual_rt to the nearest 1,000th

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)...

## RE: Rounding Annual_rt to the nearest 1,000th

ADP - PeopleSoft - SAP

ReportSmith - Crystal Reports - SQR - Query - Access

Reporting - Interfaces - Data Mining

## RE: Rounding Annual_rt to the nearest 1,000th

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

ADP - PeopleSoft - SAP

ReportSmith - Crystal Reports - SQR - Query - Access

Reporting - Interfaces - Data Mining

## RE: Rounding Annual_rt to the nearest 1,000th

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

## RE: Rounding Annual_rt to the nearest 1,000th

@ROUND(field_name_here + 500,-3)

Tim

## RE: Rounding Annual_rt to the nearest 1,000th

ROUND(field_name_here + 500,-3)

Tim

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