Compound Excel for BYTOC plus a couple other worksheets

[Jess Hines]

In a nutshell, I'm building a compound Excel report where the first few tabs need data from one table, split to separate worksheets by a certain field, call it [MY_CATEGORY].

I can do this I found using

SET COMPOUND='BYTOC 1'

But that then closes the file at the end, and I need to add on a couple more tabs (with data from different tables).

Is there any way to do a BYTOC but leave the file open for more writing?

Here's my starting sample code

DEFINE FILE T_DC_AUDIT_BY_CATEGORYNICE_TITLE/A10= IF AUDIT_CATEGORY EQ 'A' THEN 'Alpha' ELSE IF AUDIT_CATEGORY EQ 'B' THEN 'Bravo' ELSE IF AUDIT_CATEGORY EQ 'C' THEN 'Charlie' ELSE 'Misc';ENDTABLE FILE T_DC_AUDIT_BY_CATEGORYPRINT FIELD1 FIELD2 FIELD3BY LOWEST NICE_TITLEON TABLE PCHOLD FORMAT XLSXON TABLE SET XLSXPAGESETS ONON TABLE SET COMPOUND 'BYTOC 1'END -* 2 more worksheets needed here

[Martin Yergeau]

Using OPEN & CLOSE feature

And also pay attention that I use BYTOC and not 'BYTOC 1'

DEFINE FILE CARCOUNTRYN /A10 = LCWORD(10, COUNTRY, 'A10');ENDTABLE FILE CARSUM SEATS AS 'Nb,Seats'BY COUNTRYN AS 'Country'BY CAR AS 'Car'BY MODEL AS 'Model'ON TABLE PCHOLD FORMAT XLSX OPENON TABLE SET COMPOUND BYTOCON TABLE SET BYDISPLAY ONON TABLE SET LINES 999999ON TABLE SET PAGE-NUM NOLEADON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$ENDSTYLEEND-RUN TABLE FILE CARSUM DEALER_COST AS 'Dealer,Cost'BY COUNTRYN AS 'Country'BY CAR AS 'Car'BY MODEL AS 'Model'ON TABLE PCHOLD FORMAT XLSXON TABLE SET BYDISPLAY ONON TABLE SET LINES 999999ON TABLE SET PAGE-NUM NOLEADON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$TYPE=REPORT, TITLETEXT='Dealer Cost',$ENDSTYLEEND-RUN TABLE FILE CARSUM RETAIL_COST AS 'Retail,Cost'BY COUNTRYN AS 'Country'BY CAR AS 'Car'BY MODEL AS 'Model'ON TABLE PCHOLD FORMAT XLSX CLOSEON TABLE SET BYDISPLAY ONON TABLE SET LINES 999999ON TABLE SET PAGE-NUM NOLEADON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$TYPE=REPORT, TITLETEXT='Retail Cost',$ENDSTYLEEND-RUN

[Jess Hines]

Thanks, I'll give this a try. I didn't know what "BYTOC 1" meant and I couldn't find any example in the documentation, but that's what was created when I first tried using the report designer. Can you tell me the difference between BYTOC and BYTOC 1?

UPDATE:

I was able to apply this to my code and it runs!

However, the BYTOC orders things alphabetically, but the user requirements are for a specific order. Do you know of any way to have a custom order without changing the tab titles to something like 1...2... or A...B...?

[Martin Yergeau]

I don't think so.

The only way I can think of it's by creating each tab manually (not use the BYTOC feature) such as in my sample for the last two tabs.

Or you can append 1...2....3 in front of each possible BYTOC value using a DEFINE and use that new DEFINEd field as your first BY field.

But it's going to be a pain for maintenance.

DEFINE FILE CARCOUNTRYM /A10 = LCWORD(10, COUNTRY, 'A10');COUNTRYN /A15 = DECODE COUNTRY ('ENGLAND' '2 - ' 'JAPAN' '1 - ' 'FRANCE' '3 - ' 'ITALY' '5 - ' ELSE '4 - ') | COUNTRYM;ENDTABLE FILE CARSUM SEATS AS 'Nb,Seats'BY COUNTRYN AS 'Country'BY CAR AS 'Car'BY MODEL AS 'Model'ON TABLE PCHOLD FORMAT XLSX OPENON TABLE SET COMPOUND BYTOCON TABLE SET BYDISPLAY ONON TABLE SET LINES 999999ON TABLE SET PAGE-NUM NOLEADON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$ENDSTYLEEND-RUN......followed by other previously provided sample code

Have a look at page 712 and following for COMPOUND BYTOC Excel document :

TIB_wfwf_8207.28.0_cr_language.pdf (tibco.com)

[Jess Hines]

Thank you so much. That DECODE was a bonus too; I wasn't aware of that function.

I was also able to get the users to agree to prepending the tab names with a number, so looks like I get to use BYTOC!

Thanks for all the help!