×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

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

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:
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:
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

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!