Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Compound Excel for BYTOC plus a couple other worksheets


Jess Hines
Go to solution Solved by Martin Yergeau,

Recommended Posts

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
Link to comment
Share on other sites

  • Solution

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
Link to comment
Share on other sites

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...?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...