Should you want to create your own custom sort sequence for a report with output format EXL2K BYTOC, you would run into an interesting situation:
First logical approach:
TABLE FILE CAR
PRINT DC RC
BY COUNTRY
ROWS 'JAPAN' OVER
'ENGLAND' OVER
'ITALY' OVER
'FRANCE'
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

The result is unfortunately not what you would expect. Yes it has correctly sorted the results in your sort order, but the tabs in Excel are labeled 1, 2, 3, etc.
I suspect that in the internal matrix FOCUS generates another "defined" sort that precedes COUNTRY, therefore the COUNTRY value is not used when the TOC is created (It uses the 1st BY column).

Solution:
In order to populate your Table Of Contents with the sort values of choice (in this case COUNTRY) you could use spaces concatenated to the front of your sort value as in the following example:

DEFINE FILE CAR
MYSORT/A100 =
IF COUNTRY EQ 'JAPAN' THEN ' '|COUNTRY ELSE
IF COUNTRY EQ 'ENGLAND' THEN ' '|COUNTRY ELSE
IF COUNTRY EQ 'ITALY' THEN ' '|COUNTRY ELSE
IF COUNTRY EQ 'FRANCE' THEN ' '|COUNTRY ELSE ' '|COUNTRY;
END
TABLE FILE CAR
PRINT
DC AS DC
RC AS RC
BY MYSORT
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

And voila!

Hope this helps someone out there oneday
;-)

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

Since the product is called WebFOCUS, there are more ways to skin a cat.
The following example produces entirely correct results:

TABLE FILE CAR
PRINT DC RC
BY COUNTRY
ROWS 'JAPAN' OVER 'ENGLAND' OVER 'ITALY' OVER 'FRANCE'
ON TABLE HOLD FORMAT ALPHA
END

TABLEF FILE HOLD
PRINT DEALER_COST RETAIL_COST
BY E01 AS COUNTRY
ON TABLE PCHOLD FORMAT HTML BYTOC
END

No tricks with spaces needed .... just using the unability of TABLEF to sort but by using BY it stil generated the correct custom sorted TOC.

You beauty you reminded me of how wholesome our product is.

Thx GamP

I wish there was a way to see what is in the Internal Matrix (without taking the red pill).

I would assume that using

ROWS 'JAPAN' OVER 'ENGLAND' OVER 'ITALY' OVER 'FRANCE'

Produces a first-order sort column in the Internal Matrix the same as if you did something like

DEFINE FILE CAR
CNTRY_SORT/I2 = IF(COUNTRY EQ 'JAPAN') THEN 1 ELSE IF(COUNTRY EQ 'ENGLAND') THEN 2 ELSE IF(COUNTRY EQ 'ITALY') THEN 3 ELSE IF(COUNTRY EQ 'FRANCE') THEN 4 ELSE 999;
END
TABLE FILE CAR
PRINT DC RC
BY CNTRY_SORT NOPRINT
BY COUNTRY
WHERE CNTRY_SORT GT 0;
END

This would explain where the digits come from in the Excel tabs because BYTOC is using the left-most sort column values. GamP's simple solution creates a HOLD table where the left-most sort column contains the actual country names in the desired order. Then, BYTOC can populate the Excel tabs with meaningful information.

I don't think I ever would have realized what is really going on there had I not seen this thread. If only you could look at the Internal Matrix I think it would save a lot of troubleshooting time and get you to that "Aha!" moment a lot faster.

Where's Morpheus when you need him?

Dan

Dan, I think you are spot-on, that is how I understand it too.

Good thing we don't have to rely on pills all the time, sometimes it needs a little more faith than normal.

It seems like trickery, but when you get down close to the insides of how the FOCUS engine processes our code (it is still the same old stuff). Good old reliable FOCUS.

Cheers

I have a solution that allows you to determine the sort order and replicates BYTOC ... as follows :
Using the car file :

-* THIS IS THE FIELD YOU WANT TO SORT
-SET &F = 'COUNTRY';
-* THIS NEEDS TO SET MANUALLY --- You could just Parser the Master File to get this field length
-SET &FL = 10;
-* SORT ORDER
-SET &STR = 'EJIF';
-SET &STRL = &STR.LENGTH;
-SET &STRLE = &STRL + 1;
-SET &C = 1;

-******
SET HOLDLIST = PRINTONLY
SET COMPOUND = OPEN
-**

DEFINE FILE CAR
FCHAR/A1=SUBSTR(&FL.EVAL, &F.EVAL, 1, 1, 1, FCHAR);
Z/I5=
-REPEAT :L1 &STRL TIMES
-SET &LIN = SUBSTR(&STRL.EVAL,&STR,&C.EVAL,&C.EVAL,1,'A1');
IF FCHAR EQ '&LIN.EVAL' THEN &C.EVAL ELSE
-SET &C = &C + 1;
-:L1
&C.EVAL;
END

-*** REPEAT THE PROCESS FOR EACH OF THE SORT FIELDS AND HAVE THE LAST 1 BEING ALL THE REST OF THE FILE
-SET &C = 1;
-REPEAT :L2 &STRLE TIMES

-* GET THE CONTENTS OF THE 1ST COUNTRY FIELD FOR THE TAB NAME
TABLE FILE CAR
BY COUNTRY
WHERE Z EQ '&C.EVAL';
ON TABLE SAVE
END
-RUN

-READ SAVE &CON.&FL.EVAL
-RUN

-* TABLE OFF THE FIELDS
TABLE FILE CAR
PRINT CAR MODEL SEATS DCOST RCOST
BY COUNTRY NOPRINT
WHERE Z EQ '&C.EVAL';
-** DECIDE IF THIS IS THE LAST RUN
-SET &TAB = IF &C.EVAL NE &STRLE.EVAL THEN 'ON TABLE PCHOLD FORMAT EXL2K' ELSE
- 'ON TABLE PCHOLD FORMAT EXL2K CLOSE'
&TAB

-*** SET THE TAB TITLE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=&CON, $
END
-RUN
-SET &C = &C + 1;
-:L2

-EXIT

Let me know if that was of any use ..

Paul