I have an XLSX report that uses BYTOC on the first sort field, creating a worksheet with a sheet (or tab if you like) per unique sort value. The unfortunate side-effect is that the tabs appear in alphabetical order, while the customer would prefer the sort-of flattened hierarchical order in the original (manually created) document.

They don't consider that a major issue though; the original document started with a sheet with a Table of Contents that selected the desired worksheet - and since there are dozens - that has been the preferred method of operation anyway.

Now I have done such in compound PDF documents, by creating a Document with output format PDF and then inserting a TOC page. I can't seem to get this to work for XLSX though - is that implemented at all?

This is what I got so far:

-* Default Mode: ResourceLayout
-SET &WFFMT = XLSX;
APP PREPENDPATH IBISAMP
SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT &WFFMT
UNITS=IN, $
OBJECT=TOC, NAME='TOC_Text', TEXT='Table of Contents', MARKUP=OFF, TOC-NUMBERING=ON, POSITION=(0.854 0.854), DIMENSION=(7.000 9.500), font='TREBUCHET MS', color=RGB(0 0 0),  size=10, METADATA='left: 0.854in; top: 0.854in; width: 7in; height: 9.5in; font-family: Trebuchet MS; font-size: 10pt; position: absolute; z-index: 1; Justify: center; TOCTITLE: Table of Contents', $
SECTION=section1, LAYOUT=ON, METADATA='prop_with_names^Margins_Left=0.5^Margins_Top=0.5^Margins_Right=0.5^Margins_Bottom=0.5^thumbnailscale=4', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=A4, SHOW_GLOBALFILTER=OFF, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=1, POSITION=(0.854 0.854), DIMENSION=(7.000 9.500), BYTOC=2,  ARREPORTSIZE=DIMENSION, METADATA='left: 0.854in; top: 0.854in; width: 7in; height: 9.5in; position: absolute; z-index: 1;', $
END
SET COMPONENT='report1'
-*component_type report
TABLE FILE CAR
PRINT
     CAR.BODY.SEATS
     CAR.BODY.DEALER_COST
     CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY NOPRINT
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
ON  CAR.ORIGIN.COUNTRY SUBHEAD
"<CAR.ORIGIN.COUNTRY"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE SET BYPANEL ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET COMPOUND 'BYTOC 1'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
COMPOUND END

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

Wep5622

With XLSX the Table Of Contents will only create separate tabs in the spreadsheet for each value of the first BY. You can not make it display the same as you see in PDF.

Thanks for confirming what I feared already, Chuck. We'll have to get a bit creative then...