I'm puzzled with rounding in WebFOCUS. For example: I need to show totals rounded to thousands. I'm doing it in such way:
TABLE FILE CAR
BY COUNTRY
SUM
COMPUTE SALES_TH/I10 = SALES/1000; NOPRINT
COMPUTE SALES_RND/I10 = SALES_TH * 1000; NOPRINT
PRINT SALES
ON COUNTRY SUBFOOT
"Sales: <ST.SALES"
"Sales rounded to thousands: <ST.SALES_RND"
END
I have strong suspicion that this can be done more brief. I do not like creating two non-necesary fields just for something like: ROUND(ST.SALES/1000,0)*1000
I suppose that I miss something very simple.
Is it possible to make more brief?
Thanks in advance
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
November 01, 2007, 07:33 AM
GamP
Ingas, If there is a ROUND function it would only work for D fields, since integers do not need rounding. Thus, you're on the right track for your problem. The code below is a little bit more transparent, and works just as well. One addition: for the first temp field you would have to add 500 to ensure a proper sounding. Converting to an I field will not do any rounding....
TABLE FILE CAR
PRINT SALES
BY COUNTRY
ON COUNTRY RECAP
SALES_TH/I10 = (SALES+500)/1000;
SALES_RND/I10 = SALES_TH * 1000;
ON COUNTRY SUBFOOT
"Sales: <ST.SALES"
"Sales rounded to thousands: <SALES_RND"
END
Let me know what you think....
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 01, 2007, 08:11 AM
Ingas
Hi GamP,
Thanks for RECAP, I do not learn it yet.
But main issue remains: It seems strange to me that rounding totals are made through two intermediate fields.
I have 10 fields in report that I need to round to thousands. So I have 20 additional NOPRINT fields.
I like FOCUS - in many tasks it is more brief than SQL. But sometimes I'm stuck in multiple holds, intermediate fields, MATCHes, etc.This message has been edited. Last edited by: Ingas,
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
November 01, 2007, 10:14 AM
Tony A
Alex,
You should be able to -
ON COUNTRY RECAP
SALES_RND/I10 = INT((SALES+500)/1000) * 1000;
ON COUNTRY SUBFOOT
"Sales: <ST.SALES"
"Sales rounded to thousands: <SALES_RND"
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
November 01, 2007, 11:31 AM
Darin Lee
Sadly, there is no "magical function" in the language to round to tens, hundreds, thousands, etc. The code GamP and Tony provide would get you where you need to go. However, (as always) there is a little shortcut when you have a function that you commonly use that isn't available as a stock function. You can find documentation for creating your own user-written subroutines in the "Using Subroutines" manual available online.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
November 02, 2007, 07:15 AM
Ingas
Tony,
Thanks, that's much better.
Darin, Functions library : yes, I'm thinking about own function library. One thing I do not like much in FOCUS - too much parameters in functions.
E.g. SUBSTR(inlength, parent, start, end, sublength, outfield)
I never saw before substring function that have 6 arguments.
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
November 02, 2007, 08:15 AM
FrankDutch
Ingas
how about this function
Syntax: How to Replace Character Strings
STRREP (inlength, instring, searchlength, searchstring, replength, repstring, outlength, outstring)
....uspechow wam!
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 02, 2007, 08:32 AM
Ingas
Frank,
Yes! I always forgot params of this function also. Sometimes I feel like I'm programming in ASM =)
I think about own function library written on top of "official".
STRREP - must have 3 params not 8 =)
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
November 02, 2007, 09:04 AM
GinnyJakes
Look up DEFINE FUNCTION. Without having to use a compiler, you can string together a series of commands, give them a name, pass it a variable, and get a result.
This feature has been around for awhile and is great for often-used calculations. You could -INCLUDE it in your focexec.
TABLE FILE CAR
BY COUNTRY
SUM SALES
COMPUTE MYSALES_RND/I10 =(INT((SALES/1000)))* 1000 ;
PRINT SALES
ON COUNTRY SUBFOOT
"SALES: <ST.SALES"
"SALES ROUNDED TO THOUSANDS ONE FIELD: <ST.MYSALES_RND"
END
Hope this helps. Many thanks to everyone's input!
Cheers,
Kerry
Kerry Zhan Focal Point Moderator Information Builders, Inc.
November 02, 2007, 01:20 PM
Ingas
Thanks all!
Kerry, Tony - for samples how to use 1 COMPUTE field instead of two.
Darin, Ginny, Frank - for strengthing my intention to write own function library.