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,

Look into RECAP.

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

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.

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?

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.

Seems to perhaps be related to the format of a field used in the calculation of the field being displayed?

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

It works for me also.

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

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.

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,