Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] format numeric field in a subfoot
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] format numeric field in a subfoot
 Login/Join
 
Silver Member
posted
Hi,

If I am subtotaling a field in a subfoot, is there a way to control the numeric display options for that field? For example if I have a field that is formatted as D3% or D12!D and I use the prefix operator ST in my subfoot is there a way to force it to have that same formatting?

Note that I did not use a SUBTOTAL line as I do not want to display the value of the field associated with the subtotal -- I just want it to say "Subtotal Other".

I am running WF8105M on Windows.

Thanks in advance for any suggestions.


Here is sample output:

% of
Amount All
Item 1 $8,921,500 40%

Item 2 $38,441,000 47%
Item 3 $600,000 1%
Item 4 $3,525,000 12%

Subtotal Other 42,566,000.00 59.93


And here is sample code:
DEFINE FILE MYITEMS
PROJECT/A3 = EDIT(PROJECT_CD,'999');
A_OR_C/A1 = IF PROJECT IN ('WFC','WFD','WFM') THEN 'A' ELSE 'C';
PURPOSE/A15 = IF PROJECT IN ('CCE','CPF','CPO','FCT','ISH','SCH','SLP') THEN 'Item 2' ELSE IF PROJECT EQ 'CAF' THEN 'Item 2'
ELSE IF PROJECT EQ 'FAI' THEN 'Item 3' ELSE IF PROJECT EQ 'STL' THEN 'Item 4'
ELSE IF PROJECT IN ('WFC','WFD','WFM') THEN 'Item 1' ELSE 'Other';
TOT_ALL/D12!D=PRICE;
END
TABLE FILE MYITEMS
SUM
PROJ_TARGET/D12!D AS 'Amount'
PCT.PRICE/D3% AS '% of,All'
BY A_OR_C NOPRINT
BY PURPOSE AS ''

ON A_OR_C SUBFOOT
" "
"Subtotal Other WHEN A_OR_C NE 'A';
ON A_OR_C SKIP-LINE
ON TABLE SUBHEAD
"ALL ITEMS"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = mystylesheet,
$
TYPE=DATA,
COLUMN=N2,
STYLE=BOLD,
WHEN=N2 EQ 'Item 1',
$
TYPE=TITLE,
COLUMN=N2,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=10,
BACKCOLOR='SILVER',
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=SUBFOOT,
STYLE=BOLD,
$
TYPE=SUBFOOT,
BY=1,
LINE=2,
OBJECT=FIELD,
ITEM=1,
POSITION='PROJ_TARGET',
$
TYPE=SUBFOOT,
BY=1,
LINE=2,
OBJECT=FIELD,
ITEM=1,
POSITION='PRICE',
JUSTIFY=LEFT,
$
TYPE=SUBTOTAL,
BY=1,
OBJECT=TAG,
STYLE=BOLD,
$
ENDSTYLE
END

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 37 | Registered: March 02, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Look into RECAP.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1080 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This is what dhagen's suggestion might look like using RECAP syntax:
  
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY CAR.ORIGIN.COUNTRY RECAP RETAIL_M/D12.4M=RETAIL;
ON CAR.ORIGIN.COUNTRY SUBFOOT
"Reformatted Retail with Dollar sign and 4 decimals: <RETAIL_M"

BY CAR.COMP.CAR
ON TABLE PCHOLD FORMAT HTML
END


WebFOCUS 8204, Unix, Windows
 
Posts: 1457 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks for the suggestions. I'm sure the RECAP example will come in handy for a future project. I ended up creating a dummy (empty) sort field (thank you, FOCWIZARDS) and did a subtotal on that.
 
Posts: 37 | Registered: March 02, 2005Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by BabakNYC:
This is what dhagen's suggestion might look like using RECAP syntax:
  
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY CAR.ORIGIN.COUNTRY RECAP RETAIL_M/D12.4M=RETAIL;
ON CAR.ORIGIN.COUNTRY SUBFOOT
"Reformatted Retail with Dollar sign and 4 decimals: <RETAIL_M"

BY CAR.COMP.CAR
ON TABLE PCHOLD FORMAT HTML
END



I cannot make this work in Excel. I have fields in a column that show decimals, the recap field in the subfoot calculates the value to two decimals (correctly) but the display in EXL07 shows just the integer part. Same column in the spreadsheet, different format.

Bug?



 
Posts: 870 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Yeah, further work on this indicates that subfoot formatting appears to be a bit of a mess. I can kind of make this work using format EXL2K, but not perfectly. When I subfoot a recap field (format D12.2 or P12.2 or any other P or D as near I can tell) it sends the correct value, but the format in Excel is set to #0. So it crops the decimal part in spite of it being in the field. Pretty misleading. But I can warn my users since there's only about five of them for this particular need.

When I use EXL07 . . . ho boy, what a mess. As best I can tell I have about zero control of the result, including the actual value. I can't get a correct number to appear and I don't mean the decimal part. I'm getting crazy results, like 34.6 expected and 22137 delivered. Way crazy errors.

And you know . . . you know, this is why this frikkin' thread should not be marked as SOLVED, because it isn't. So I can open this issue up as a new thread and people will get nasty about it, or I can add to this thread (where I can't get SOLVED off of the subject line) and no one will read it. I just detest that SOLVED marker in subject lines.



 
Posts: 870 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Seems to perhaps be related to the format of a field used in the calculation of the field being displayed?



 
Posts: 870 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Hi John,

Happy New Year!

What version of WF are you using where you get these issues? Also what version of MS Eggshell are you using? A mixture or a specific version across your user base?

If I run the example above, making the change to 2 decimal places, I receive correct results in both EXL2K and XLSX (EXL07) using WF 8.1.05 and MS Eggshell 2010.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.05 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.05 standalone on Windows 10 
 
Posts: 5501 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
It works for me also.

Office 365 using XLSX and in WF 8.1.05M or 8.2.03.


WebFOCUS 8.1.05M - Production
WebFOCUS 8.2.03 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
 
Posts: 106 | Location: Seattle, WA | Registered: April 07, 2015Reply With QuoteReport This Post
Master
posted Hide Post
Thank you for tuning in!

I'm on 8.1.04 WebFOCUS. IE 11.0.9600.

The fields I'm printing are calculated from other fields, and making changes to the format of the printed field doesn't appear to have any effect on the format of the output.

I've discovered that changing the format of the fields the final field is calculated from can have an effect. Unfortunately I've only been able to make that change happen on a field that is an integer, that I can turn into a real. Doh! I need to have other fields go the same direction. Here's a snippet --

ON SORT_ORDER RECAP 
   ALL_SERVICES_COUNTER_TOTAL/I9CS = ALL_SERVICES_COUNTER;
   ALL_SERVICES_COUNTER_RECAP/I9CS = ALL_SERVICES_COUNTER / ALL_SERVICES_OFFICES_WITH_SERVICE_COUNTER + 0.5;  -* The 0.5 on the end turns truncating integer math into round-off math.
   AVG_WAIT_RECAP/P12.2 = (ALL_SERVICES_WAIT_TIME/ALL_SERVICES_COUNTER);
   AVG_WAIT_HOURS_RECAP/I9 = INT(AVG_WAIT_RECAP/3600);
   AVG_WAIT_MINUTES_RECAP/I9 = INT(AVG_WAIT_RECAP - AVG_WAIT_HOURS_RECAP*3600)/60;
   AVG_WAIT_SECONDS_RECAP/P5.0 = INT((AVG_WAIT_RECAP - AVG_WAIT_HOURS_RECAP*3600 - AVG_WAIT_MINUTES_RECAP*60));
   AVG_WAIT_FORMATTED_RECAP/D12.2 = AVG_WAIT_RECAP / 60;

ON SORT_ORDER SUBFOOT
  "Average<+20><ALL_SERVICES_COUNTER_RECAP<+0><AVG_WAIT_FORMATTED_RECAP<+0><AVG_SERVICE_FORMATTED_RECAP<+0><DRIVER_SERVICES_COUNTER_RECAP<+0><DRIVER_AVG_WAIT_FORMATTED_RECAP<+0><DRIVER_AVG_SERVICE_FORMATTED_RECAP<ICD_SERVICES_COUNTER_RECAP<+0><ICD_AVG_WAIT_FORMATTED_RECAP<+0><ICD_AVG_SERVICE_FORMATTED_RECAP<VEHICLE_SERVICES_COUNTER_RECAP<+0><VEHICLE_AVG_WAIT_FORMATTED_RECAP<+0><VEHICLE_AVG_SERVICE_FORMATTED_RECAP<+0><KIOSK_AVERAGE"
  "Sum<+20><ALL_SERVICES_COUNTER_TOTAL<+0> <+0> <+0><DRIVER_SERVICES_COUNTER_TOTAL<+0> <+0> <+0><ICD_SERVICES_COUNTER_TOTAL<+0> <+0> <+0><VEHICLE_SERVICES_COUNTER_TOTAL<+0> <+0> <+0><KIOSK_TOTAL"


The AVG_WAIT_FORMATTED_RECAP field at the end of the set of values above formats as an integer in EXL2K (Office 365). The value is a real, e.g. 27.65, and is loaded into Excel. But the display format is #0 which displays a 27.



 
Posts: 870 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Oh my.

Found something similar happening farther down the report, but with a percentage.

17.1% for wait time in minutes. That got me to looking . . .

. . . at the format of the field defined just before it in the recap set. It was inheriting the format of the line above it. I added a line to my code:


ON SORT_ORDER RECAP
ALL_SERVICES_COUNTER_TOTAL/I9CS = ALL_SERVICES_COUNTER;
ALL_SERVICES_COUNTER_RECAP/I9CS = ALL_SERVICES_COUNTER / ALL_SERVICES_OFFICES_WITH_SERVICE_COUNTER + 0.5; -* The 0.5 on the end turns truncating integer math into round-off math.
AVG_WAIT_RECAP/P12.2 = (ALL_SERVICES_WAIT_TIME/ALL_SERVICES_COUNTER);
AVG_WAIT_HOURS_RECAP/I9 = INT(AVG_WAIT_RECAP/3600);
AVG_WAIT_MINUTES_RECAP/I9 = INT(AVG_WAIT_RECAP - AVG_WAIT_HOURS_RECAP*3600)/60;
AVG_WAIT_SECONDS_RECAP/P5.0 = INT((AVG_WAIT_RECAP - AVG_WAIT_HOURS_RECAP*3600 - AVG_WAIT_MINUTES_RECAP*60));
AVG_WAIT_FORMAT_SETTER_SW/P12.2 = 0.0;
AVG_WAIT_FORMATTED_RECAP/D12.2 = AVG_WAIT_RECAP / 60;

Now I can control the format of the field that is displayed.

This message has been edited. Last edited by: John_Edwards,



 
Posts: 870 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] format numeric field in a subfoot

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.