Is it possible to do the following...

-* 
File BYTOCtest.fex
-SET &BY_CNTRY = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN 'FOC_NONE' ELSE '';

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
&BY_CNTRY     'CAR.BODY.BODYTYPE'
&BY_CNTRY     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END  

What I am attempting to do is if the Country EQ 'ENGLAND' then 'FOC_NONE' the (2) two fields for BODYTYPE and SEATS so that they do not display. I need the &var in order for the 'FOC_NONE to work in this way. I am trying to create an amper variable for the BYTOC value.

Any ideas or suggestions

ERINP

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

Dialogue Manager cannot be combined with data requests this way.

By the time your TABLE FILE is running, Dialogue Manager has long been resolved.

Let me understand your requirement ... you want an Excel report with an individual tab for each COUNTRY. Now, when COUNTRY = 'ENGLAND' you don't want 2 of the fields to show up ...

I don't think you can do that in one single pass ...

Having (or not) a field in a TABLE FILE statement is applicable to the request as a whole and not for certain records.

You can resort to some techniques such as styling those fields to display in "white" foreground and background colours depending on the value of COUNTRY but,
- They will still use up space on your spreadsheet
- The column title will still be there (unless you fake it with HEADING)

The other approach would be to run 2 requests using compound: one for all countries except ENGLAND including all columns, a second one only for ENGLAND without the columns. I've never used BYTOC and COMPOUND in combination though.

This last approach will also cause ENGLAND to show up at the end of the document instead of "somewhere in the middle" where it would normally appear due to sorting.

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

njsden,
I think the last option you mentioned is the one I will have to use. I can get the report output I am looking for with:

 
 SET COMPOUND=BYTOC

This creates a worksheet for each Country. If I exclude England from the BYTOC list I do not know how to add an additional worksheet to the existing workbook so that England would be included in the spreadsheet. I have found a couple discussions talking about combining to one worksheet but not adding a worksheet.

Any suggestions??

ERINP

FOC_NONE is for use in Where statemnts when yopu wish to omit the Where clause.
If you are just trying to comment out the 2 fields try this.

 
-SET &CMT = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN '-*' ELSE '';

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
&CMT.EVAL     'CAR.BODY.BODYTYPE'
&CMT.EVAL     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END  

FOC_NONE can be used *anywhere* you want a particular line of code not to be processed by the FOCUS engine; this is not limited to WHERE statements.

Regardless of that, for the case being discussed the line:

-SET &CMT = IF CAR.ORIGIN.COUNTRY EQ 'ENGLAND' THEN '-*' ELSE '';

is a Dialogue Manager statement that is evaluated *before* any FOCUS statement is processed; though you might think that in that context CAR.ORIGIN.COUNTRY represents a field read from the table, Dialogue Manager actually sees it as the constant value: 'CAR.ORIGIN.COUNTRY' as it is too early in the process to even have the table involved.

Think of Dialogue Manager as a macro language or a pre-processor if you wish.

ERINP, I think the only choice (unless Compound Composer can help there) is to do it on a country-by-country basis, that is, getting your base list of countries and then looping through them so you can have more control over each of them (the reports, not the countries ).

The code below creates a report for each country, but does not include MODEL when COUNTRY='ITALY'.

TABLE FILE CAR
PRINT DST.COUNTRY AS 'COUNTRY'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HCOUNTRY FORMAT ALPHA
END
-RUN
-SET &CNTRYCNT = &LINES;
-REPEAT :DOBYTOC FOR &I FROM 1 TO &CNTRYCNT STEP 1
-READFILE HCOUNTRY

-SET &COMPOUND  = IF &I EQ 1 THEN 'OPEN' ELSE IF &I EQ &CNTRYCNT THEN 'CLOSE' ELSE 'FOC_NONE';
-SET &HIDEFIELD = IF &COUNTRY EQ 'ITALY' THEN 'FOC_NONE' ELSE '';

SET COMPOUND=&COMPOUND
TABLE FILE CAR
PRINT
   CAR
   MODEL  &HIDEFIELD
   SEATS
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TITLETEXT='&COUNTRY', $
ENDSTYLE
END
-:DOBYTOC

Hope that gives you some ideas.

quote:

I've never used BYTOC and COMPOUND in combination though

Doh! BYTOC is just a special flavour of COMPOUND Oh well, I didn't lie either.

I think I am going to try the SET COMPOUND=BYTOC... PCHOLD EXL2K OPEN the first report all countries NE 'England'... Then do a second report PCHOLD EXL2K CLOSED for Country EQ 'England' to see if that works.

ERINP

njsden,
the line

 -READFILE HCOUNTRY 

is throwing a FOC303 error:CONTROL LINE NOT RECOGNIZED IN FOCEXEC: -READFILE HCOUNTRY
I also tried it as

 -READ FILE HCOUNTRY

(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ
 FILE HCOUNTRY

both throw errors??

I think -READFILE is a 7.7.x feature ... you can replace it in 7.6.9 with something like:

-READ HCOUNTRY NOCLOSE &COUNTRY.A10.

njsden,
Thanks again for all of your help. I was able to manipulate the CAR file example and incorporate it into my actual report. My final code ends up being a loop within a loop within a loop but it does work. Since this is a report caster ran job the performance impact of the multiple loops is a non-issue.

Thanks,
ERINP

Hmmm, I don't see why you'd need 2 nested loops for that but I'm assuming it's due to the complexity of your actual requirement.

Glad the idea was of help!