Hi community,

I am pretty sure that's a easy one but cannot figure it out. I may think too much.
I know and found many options on how to accomplish this for measure (SUM/PRINT) data or based on a variable but it needs to be accomplished at data read where BYTOC can be used.

Using below code it result in an Excel file where each country has its own sheet (SET COMPOUND BYTOC).
Up to there, all good.
But I have a request for when COUNTRY EQ 'ENGLAND' (in this case it's the first sheet) the MODEL column (BY MODEL) must not be displayed in that sheet but in others yes.

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
-* BY MODEL must not be displayed (NOPRINT) when COUNTRY EQ 'ENGLAND'
BY MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
END

Any idea ?

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

Hey martin ,
i guess this will work for you

-SET &A = IF COUNTRY EQ 'ENGLAND' THEN NOPRINT ELSE '';
TABLE FILE CAR1
SUM RETAIL_COST
DEALER_COST
SEATS
BY COUNTRY
BY CAR
-* BY MODEL must not be displayed (NOPRINT) when COUNTRY EQ 'ENGLAND'
BY MODEL &A
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
END

I will love it if that would be so simple.

You cannot test a column value (field from a master file) outside the TABLE FILE...END
And you cannot (AFAIK) use a master file field in a -SET since the field is unknown in the -SET syntax

I often use variables in TABLE FILE syntax but they are predefined outside the TABLE FILE ...END and not according the data read within the TABLE FILE...END
Such as

-SET &BY01 = 'COUNTRY';
-SET &SUM1 = 'SEATS';
TABLE FILE CAR
SUM &SUM1
BY &BY01
END

But now it has to be defined (or displayed) according to data read. And in my case based on first BY field since this is the field used by the BYTOC command.

Couple of thoughts:

* If your workbook is looking good, except for the one 'extra' column in the one sheet, then maybe create an Excel macro to delete the one column in the one sheet?

* If your input file has already been extracted from your database, and contains the data needed for the workbook, then maybe create one sheet at a time?

-DEFAULTH &COUNTRY = ' '
-DEFAULTH &COUNTER = 0
-*
-* Pull Countries to be reported.
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD 
END
-RUN
-SET &NUM_OF_COUNTRYS = &LINES;
-*
-* Create Excel workbook, each country on its own sheet.   
-*
-REPEAT END_OF_LOOP &NUM_OF_COUNTRYS TIMES 
-SET &COUNTER = &COUNTER + 1;
-READFILE HOLD 
-TYPE Creating a sheet for &COUNTRY
-SET &OPEN_OR_CLOSE = IF &COUNTER EQ 1 THEN 'OPEN' ELSE 
-                      IF &COUNTER EQ &NUM_OF_COUNTRYS THEN 'CLOSE' 
-                       ELSE ' ';
-TYPE Open or Close => &OPEN_OR_CLOSE
-SET &DASHASTER = IF &COUNTRY EQ 'ENGLAND' THEN '-*' ELSE ' ';
-*
TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
-* MODEL commented out when COUNTRY EQ 'ENGLAND'
&DASHASTER.EVAL BY MODEL
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE PCHOLD AS MYREPT FORMAT XLSX &OPEN_OR_CLOSE 
ON TABLE SET STYLE *
 INCLUDE=jellybean_combo.sty, $
 TYPE = REPORT, TITLETEXT = '&COUNTRY',$
ENDSTYLE
END
-RUN
-END_OF_LOOP  

This message has been edited. Last edited by: David Briars,

Thanks for the suggestions David.

Macro is not an option. I have this already in a report and I have nightmares due to my users because they can't manage that...even with all properly setup for them.
You know.. kind of users that still use flip phone :-)

I already have tried using the loop option to create each tab "manually" but it was always returning me errors (invalid format in...) when trying to use the TITLETEXT option the same way as you did and the text put there doesn't contain "invalid" characters.
So it was the reason why I have retuned to the BYTOC format that accept the TITLETEXT that I want.

I will try back and let you know the result. Maybe there is something that I didn't made the same way as it seems to work with your sample.

This loop seemed to work for me:

 
-DEFAULTH COUNTRY = '';

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS COUNTRIES
END
-RUN

-SET &COUNT = &LINES;
-SET &IDX = 0;

-REPEAT REPORTLOOP &COUNT TIMES

-READFILE COUNTRIES
-SET &IDX = &IDX + 1;
-SET &STATUS = IF &IDX.EVAL EQ 1 THEN 'OPEN' ELSE IF &IDX.EVAL EQ &COUNT.EVAL THEN 'CLOSE' ELSE '';
-SET &NOPRINT = IF &COUNTRY EQ 'ENGLAND' THEN 'NOPRINT' ELSE '';

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
BY MODEL &NOPRINT
WHERE COUNTRY EQ &COUNTRY.QUOTEDSTRING;
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX &STATUS
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=&COUNTRY, $
ENDSTYLE
END

-REPORTLOOP

-EXIT
 

Thanks all.

I solved it.

The loop was working since the beginning.
The styling error that I have was due to a included style sheet that also have the TITLETEXT= line in it.
Since I was adding the TITLETEXT= again in the report it result in a bad style sheet error.
Once removed the one from the style sheet, all good.

Once again thanks