Rounding Annual_rt to the nearest 1,000th

[scotton]

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

 

[CharlesCook]

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:

http://www.techonthenet.com/oracle/functions/round_nbr.php

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

 

[sco2222t]

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

 

[sco2222t]

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'

 

[CharlesCook]

From:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#78635

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

 

[sco2222t]

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

 

[CharlesCook]

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

 

[sco2222t]

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]

Let me think about this .....

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

 

[CharlesCook]

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

 

[scotton]

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

 

[RSHelp]

This Derived Field should do it for SQLBase users:

@ROUND(field_name_here + 500,-3)

Tim

 

[RSHelp]

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.