Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPEN] BYTOC doesn't work if master not hold on disk

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPEN] BYTOC doesn't work if master not hold on disk
 Login/Join
 
Member
posted
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
 
Posts: 18 | Registered: December 06, 2007Report This Post
Expert
posted Hide Post
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
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


WF 7.6.11 HF4 gen34, WF 7.7.03 gen 253,
Windows 64 bit
Excel, PDF, HTML
 
Posts: 18 | Registered: December 06, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPEN] BYTOC doesn't work if master not hold on disk

Copyright © 1996-2020 Information Builders