Hi,
I have a simple report where I'm trying to use the BYTOC option but if my master file RPTDATA doesn't exist as a hold file on disk it doesn't work.

I'm extracting the data from a DB then I hold the result as a FOCUS file (which is RPTDATA). I'm receiving no errors on execution but I never get the result opened in Excel. Excel don't even open after execution.

I also tried to use : ON TABLE PCHOLD FORMAT EXL2K BYTOC instead of : ON TABLE SET COMPOUND 'BYTOC 1' but it doesn't make any differences.

To make it run, I have to do an APP HOLD to keep my RPTDATA on disk. But even that, the creation of the RPTDATA and the report itself cannot be on the same execution. I have to create the data then execute the report part only...

Any ideas to help ?

BTW, I'm on 7.6.11 HF4 gen34 for the whole BI installation.

Tks


Note : &MRKUP_RATE and &AVG_RATE are received parameters.

JOIN BANNER AND STYLE AND SKU_CA IN ADJCOST
TO CL_ODS_PRODUCTS.ODS_PRODUCTS_SKUS.ODS_DIVISION_CODE
AND CL_ODS_PRODUCTS.ODS_PRODUCTS_SKUS.ODS_PROD_STYL_CODE
AND CL_ODS_PRODUCTS.ODS_PRODUCTS_SKUS.ODS_PROD_SKU_CODE IN CL_ODS_PRODUCTS
END

TABLE FILE ADJCOST
PRINT QTY
COST_CA
COST_US
COMPUTE COST_DUTY /D11.3 = (ODS_PROD_STYL_SUPP_COST_AMT * (100 + ODS_PROD_STYL_EXCH_RATE_PCT) / 100); NOPRINT
COMPUTE DUTY_AMT /D11.3 = COST_DUTY * ODS_PROD_STYL_DUTY_COST_PCT / 100; AS 'DUTY_AMT'
COMPUTE MRKUP_RATE /D5.2 = &MRKUP_RATE; AS 'MRKUP_RATE'
COMPUTE MRKUP_AMT /D11.3 = (COST_CA - DUTY_AMT) * MRKUP_RATE / 100; AS 'MRKUP_AMT'
COMPUTE EXCHG_RATE /D18.12 = &AVG_RATE; AS 'EXCHG_RATE'
COMPUTE SELL_AMT /D11.2 = (COST_CA - DUTY_AMT + MRKUP_AMT) * EXCHG_RATE; AS 'SELL_AMT'
COMPUTE VAR_AMT /D11.2 = IF CNTRY EQ 'U' THEN SELL_AMT - COST_US ELSE 0; AS 'VAR_AMT'
BY CNTRY
BY STORE_CD
BY ADJ_DATE
BY STYLE
BY SKU_CA
BY SKU_US
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
END
-RUN

TABLE FILE RPTDATA
PRINT
QTY
COST_US
COST_CA
DUTY_AMT
MRKUP_RATE
MRKUP_AMT
'EXCHG_RATE/D5.3'
SELL_AMT
COMPUTE TOT_SELL /D11.2 = SELL_AMT * QTY;
VAR_AMT
COMPUTE TOT_VAR /D11.2 = VAR_AMT * QTY;
BY HIGHEST CNTRY_DESC
BY ADJ_DATE
BY STYLE
BY SKU_CA
BY SKU_US

ON CNTRY_DESC SUBTOTAL QTY TOT_SELL VAR_AMT TOT_VAR AS 'Total'

ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE SET COMPOUND 'BYTOC 1'
END

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

WF 7.6.11 HF4 gen34, WF 7.7.03 gen 253,
Windows 64 bit
Excel, PDF, HTML

From the manual:

Excel Table of Contents (TOC) enables you to generate a multiple worksheet report in which a separate worksheet is generated for each value of the first BY field in the FOCUS report.

Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.

Syntax: How to Use the Excel Table of Contents Feature

The syntax is identical to that of HTML TOC reports, except that only a single BY field is allowed in EXL2K, while multiple BY fields are allowed in HTML.

ON TABLE {HOLD|PCHOLD} FORMAT EXL2K BYTOC
Since only one level of TOC is allowed for EXL2K reports, the optional number following the BYTOC keyword can only be 1.

As with HTML, the SET COMPOUND syntax, which precedes the TABLE command, may also be used to specify that a TOC be created:

SET COMPOUND=BYTOC
Since a TOC report is burst into worksheets according to the value of the first BY field in the report, the report must contain at least one BY field. The bursting field may be a NOPRINT field.

-----------------------------------------------------------------

It looks like TOC for Excel is simply multiple worksheets in one Excel document, not some kind of interactive TOC like HTML. This is for v7.6.8. I don't know if things have changed for versions after this.

This message has been edited. Last edited by: Francis Mariani,

Things must have changed at least a little, since the following request just work sfine in 77.01:

TABLE FILE CAR
PRINT SEATS DCOST RCOST
COUNTRY CAR MODEL 
ON TABLE HOLD AS XYZZY
END
TABLE FILE XYZZY
PRINT SEATS DEALER_COST RETAIL_COST
BY COUNTRY BY CAR BY MODEL 
ON TABLE PCHOLD FORMAT EXL2K BYTOC 1
END

No need to have the master file XYZZY present before the fact, and also more than 1 BY. I also tried with ON TABLE ST COMPOUND BYTOC 1 - same result.

Thanks all.

I already tried all this and the code I have was generated by DevStudio itself. I also tried to reproduce using the CAR file but it works...

I will have to open a case.

Thanks for your help