×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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'

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

Let me think about this .....

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

RE: Rounding Annual_rt to the nearest 1,000th

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

RE: Rounding Annual_rt to the nearest 1,000th

(OP)
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

RE: Rounding Annual_rt to the nearest 1,000th

This Derived Field should do it for SQLBase users:

 @ROUND(field_name_here + 500,-3)

Tim

RE: Rounding Annual_rt to the nearest 1,000th

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close