Hi,

Iam trying to generate a compound report one below the other in EXCEL format
based on a BY field(Country).Now as shown in the below example,since I know that there
are only 3 countries in the Car file,so that I can hardcode the values in
the filter condition and generate the report.Also when I run the below code,
I get the 3 reports one below the other without any spacing between one report
and the next.

But when I have to generate the compound report based on a BY field which
is dynamic(say sometimes I may have only 2 country values or sometimes 5 values and so on)....
Please let me know how can I generate the compound reports in this situation
without hardcoding the country values.....

Any suggestions or solutions would help me.....Thanks a lot!

TABLE FILE CAR
SUM
SEATS RETAIL_COST DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END

TABLE FILE CAR
SUM
SEATS RETAIL_COST DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE'
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END

TABLE FILE CAR
SUM
SEATS RETAIL_COST DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-EXIT

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

Could be a way

TABLE FILE CAR
BY COUNTRY
-*WHERE RECORDLIMIT EQ 4;
ON TABLE HOLD AS CNTRY FORMAT BINARY
END
-RUN

-IF &LINES EQ 0 THEN GOTO NORPT;
-SET &NBCNTRY = &LINES;
-SET &LOOP    = 1;

-NEXTRPT
-SET &OPT = IF &NBCNTRY EQ &LOOP THEN 'CLOSE' ELSE 'OPEN NOBREAK';

-READFILE CNTRY
-RUN

TABLE FILE CAR
SUM SEATS RETAIL_COST DEALER_COST
BY COUNTRY
ON COUNTRY SUBFOOT
""
WHERE COUNTRY EQ '&COUNTRY.EVAL';
ON TABLE PCHOLD FORMAT EXL2K &OPT
END

-SET &LOOP = &LOOP + 1;
-IF &LOOP LE &NBCNTRY THEN GOTO NEXTRPT;
-GOTO ENDRPT;

-NORPT
-TYPE No Report To Display
-ENDRPT

Thanks a lot MartinY....
It helped me !!

Regards!

My pleasure.

Change the subject of your first post to [SOLVED]

Also, update your signature to include your WF setup and version, it may help us to give you a proper solution based on it.

Thanks MartinY; this is so clear and easy to follow.

I ran across a couple of things when trying to replicate this with my own data that I think others might find helpful.

When creating the first hold file that you will -READFILE from (CNTRY in this example) FOCUS will create a second field of data to do the sorting if you've used a BY. So the -READFILE operation will pull that field used for sorting as a piece of data to use; which you would not want as it is just some database value used for sorting and not "real" data.

Here is what my data looked like when outputting to XML: