I have a report which needs to run either as a PDF or EXL2K based on user selection. I noticed that the SUBFOOT and SUBHEAD format doesn't translate well in EXL2K. The SUBFOOT values do not allign with respective columns as in PDF. Instead, it would all be clubbed in to single cell. We can not use SUBTOTAL because the valuse need to be RECAPped.
I have created a sample report using CAR file.
It runs fine as PDF, but if you change ON TABLE PCHOLD FORMAT to EXL2K, then it doesn't retain the SUBHEAD/SUBFOOT format.

 
-* File car-format.fex
DEFINE FILE CAR
DUMY1/A1 = '';
END

TABLE FILE CAR
SUM
	 DUMY1 AS ''
     DEALER_COST AS 'Dealer Cost'
     RETAIL_COST AS 'Retail Cost'
     SALES AS 'Sales'
	 COMPUTE COMP1/D10 = RETAIL_COST - DEALER_COST; AS 'Comp1'
 	 COMPUTE COMP2/D6% = IF SALES EQ 0 THEN 0 ELSE ((RETAIL_COST - DEALER_COST) / SALES) *100; AS 'Comp2'
BY COUNTRY NOPRINT
BY CAR NOPRINT

ON COUNTRY SUBHEAD
"<COUNTRY "

ON COUNTRY RECAP
 COUNTRY_COMP1/D10.2 = DEALER_COST - RETAIL_COST;
 COUNTRY_COMP2/D6% = IF SALES EQ 0 THEN 0 ELSE ((DEALER_COST - RETAIL_COST) / SALES) *100;

ON COUNTRY SUBFOOT
"Subtotal <COUNTRY <ST.DEALER_COST <ST.RETAIL_COST <ST.SALES <COUNTRY_COMP1 <COUNTRY_COMP2"
" "

ON CAR SUBHEAD
"<CAR"
-*
ON CAR RECAP
 CAR_COMP1/D10 = RETAIL_COST - DEALER_COST;
 CAR_COMP2/D6% = IF SALES EQ 0 THEN 0 ELSE ((RETAIL_COST - DEALER_COST) / SALES) *100;

ON CAR SUBFOOT
"Subtotal <CAR <ST.DEALER_COST <ST.RETAIL_COST <ST.SALES <CAR_COMP1 <CAR_COMP2"

HEADING
"Country"
"Car<+0>Details"

ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE-NUM OFF

ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Letter', SQUEEZE=ON, $

TYPE=REPORT, LEFTGAP=0.0, FONT='ARIAL', SIZE=8, GRID=OFF, $
TYPE=TITLE, COLOR='WHITE',BACKCOLOR=RGB(0 0 139), JUSTIFY=RIGHT, STYLE=-UNDERLINE+BOLD, $
TYPE=HEADING, COLOR=RGB(0 0 139), $
TYPE=DATA, BACKCOLOR=( 'WHITE' 'WHITE' ),$
TYPE=SUBFOOT, BACKCOLOR= RGB(234 234 255), $
TYPE=REPORT, COLUMN=N4, SQUEEZE=1.5,$

TYPE=HEADING, LINE=1, ITEM=1, OBJECT=TEXT, POSITION=0, JUSTIFY=LEFT, STYLE=BOLD, COLOR='WHITE', BACKCOLOR=RGB(0 0 139), $
TYPE=HEADING, LINE=2, ITEM=1, OBJECT=TEXT, POSITION=0.2, JUSTIFY=LEFT, STYLE=BOLD, COLOR='WHITE', BACKCOLOR=RGB(0 0 139), $
TYPE=HEADING, LINE=2, ITEM=2, OBJECT=TEXT, POSITION=N5, JUSTIFY=LEFT, STYLE=BOLD, COLOR='WHITE', BACKCOLOR=RGB(0 0 139), $

TYPE=SUBHEAD, BY=COUNTRY, POSITION=0,$
TYPE=SUBHEAD, BY=CAR, POSITION=0.1,$

TYPE=SUBFOOT, BY=COUNTRY, OBJECT=TEXT,  ITEM=1,  POSITION=0, $
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD,  ITEM=1,  POSITION=0.6, $
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=2,  POSITION=N4,$
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=3,  POSITION=N5,$
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=4,  POSITION=N6,$
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=5,  POSITION=N7,$
TYPE=SUBFOOT, BY=COUNTRY, OBJECT=FIELD, ITEM=6,  POSITION=N8,$

TYPE=SUBFOOT, BY=CAR, OBJECT=TEXT,  ITEM=1,  POSITION=0.1, $
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD,  ITEM=1,  POSITION=0.6, $
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=2,  POSITION=N4,$
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=3,  POSITION=N5,$
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=4,  POSITION=N6,$
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=5,  POSITION=N7,$
TYPE=SUBFOOT, BY=CAR, OBJECT=FIELD, ITEM=6,  POSITION=N8,$

ENDSTYLE
END

Infact, this is a requirement for all our reports to be able to run in PDF or EXL2K based on user selection and we don't want to maintain two files for each of the formats. Any suggestions? Thanks in advance!

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

POSITION doesn't do anything in Excel. Search on HEADALIGN

Prime example for FML, FRL, EMR or whatever it's called now-a-days.

T

I tried by adding HEADALIGN. With that, SUBFOOT items are not clubbed in to single cell, but at the same time they are not alligned with data columns either. I get an empty cell before each SUBFOOT item.
I added these two line to the above code

 TYPE=REPORT, LEFTGAP=0.0, HEADALIGN=BODY, FONT='ARIAL', SIZE=8, GRID=OFF, $
TYPE=SUBFOOT, HEADALIGN=BODY, $
 

Thanks for the help!

Instead of:

quote:

ON CAR SUBFOOT
"Subtotal <CAR <ST.DEALER_COST <ST.RETAIL_COST <ST.SALES <CAR_COMP1 <CAR_COMP2"

try:

ON CAR SUBFOOT
"Subtotal<CAR<ST.DEALER_COST<ST.RETAIL_COST<ST.SALES<CAR_COMP1<CAR_COMP2"  

Thanks Pat!
I made the change you suggested and that takes care of the empty cell in between the columns. I noticed one more thing. The detail columns and ST. items in SUBFOOT are right justified in the cell, but the RECAP items are left justified. Since we can not use JUSTIFY for EXL2K format, I am not sure how to control the allignment within a cell.

quote:

Since we can not use JUSTIFY for EXL2K format, I am not sure how to control the allignment within a cell.

JUSTIFY=RIGHT,$
worked fine for me in EXL2K.

That worked great!! I thought, since POSITION doesn't work in EXL2K, JUSTIFY might not work either. Thank you so much.
I really appreciate your help!!