My ultimate goal is to get three compound spreadsheets, one for each country using ReportCaster to distribute. Each spreadsheet would be a compound spreadsheet with tabs for each car in that country. When I select 'burst' I get 12 spreadsheets, 1 for each country and one for each car type. I've researched this on FocalPoint but didn't find anything that resolved my issue, as I hand code my programs, I don't use the GUI. Below is my logic using the car file. Thanks in advance for your help.

  
-SET &ECHO = ALL;
-* File rc_compound.fex
-*?FF CAR
-DEFAULT &ACTR = 0;
-DEFAULT &BCTR = 0;
TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS HCNTRY
END
-SET &ARECS = &LINES;

-REPEAT ALOOP &ARECS TIMES
-READ HCNTRY NOCLOSE &COUNTRY.10.
-SET &BCTR = 0;
-SET &ACTR = &ACTR + 1;
-SET &CMPND1 = IF &ACTR EQ 1 THEN 'OPEN' ELSE '';

TABLE FILE CAR
PRINT CAR
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY.EVAL'
ON TABLE PCHOLD AS HLDRPT FORMAT EXL2K &CMPND1.EVAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

TYPE=REPORT,
 GRID=ON,
 FONT='ARIAL',
 SIZE=9,
 STYLE='NORMAL',
 COLOR='BLACK',
 TOPGAP=0.013889,
 BOTTOMGAP=0.27778,
   WRAP=OFF,
  TITLETEXT = '&COUNTRY' ,
$
END

TABLE FILE CAR
BY CAR
WHERE COUNTRY EQ '&COUNTRY.EVAL'
ON TABLE HOLD AS HCAR
END
-SET &BRECS = &LINES;
-REPEAT BLOOP &BRECS TIMES

-SET &BCTR = &BCTR + 1;

-SET &CMPND2 = IF &ACTR EQ &ARECS AND &BCTR EQ &BRECS THEN 'CLOSE' ELSE '';

-READ HCAR &CAR.16.
TABLE FILE CAR
PRINT MODEL
BY CAR
WHERE CAR EQ '&CAR.EVAL'
ON TABLE PCHOLD AS HLDRPT FORMAT EXL2K &CMPND2.EVAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

TYPE=REPORT,
 GRID=ON,
 FONT='ARIAL',
 SIZE=9,
 STYLE='NORMAL',
 COLOR='BLACK',
 TOPGAP=0.013889,
 BOTTOMGAP=0.27778,
   WRAP=OFF,
  TITLETEXT = '&CAR Detail' ,
$
END

-BLOOP
-ALOOP

This message has been edited. Last edited by: <Kathryn Henning>,

Hi linus,

First, you need to add the same first BY to be able to burst on the same value

TABLE FILE CAR
PRINT MODEL
BY COUNTRY NOPRINT
BY CAR
WHERE CAR EQ '&CAR.EVAL'
ON TABLE PCHOLD AS HLDRPT FORMAT EXL2K &CMPND2.EVAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, GRID=ON, FONT='ARIAL', SIZE=9, STYLE='NORMAL', COLOR='BLACK', TOPGAP=0.013889, BOTTOMGAP=0.27778, WRAP=OFF, TITLETEXT = '&CAR Detail' , $
END

Second, I'm not sure that it's possible to perform it the way you're asking. The bursting facility creates on output per burst value which is, in this case each tab in the Excel (per country). So it's going to be one file per country (5 countries and not three) and one file per car (10 cars).

Maybe someone else will find an answer but as far as I know, to only have 5 files (1 per country) you need to generate only five tabs.

As Martin points out ReportCaster will burst on the first BY field. However, it is possible to distribute reports, like compound reports, or XLSX reports that may not be burstable by using a combination of ReportCaster for scheduling and EDAMAIL for distribution.

I'll illustrate with a report that I had to create to get around the bursting limitations. My requirement was to produce a TARGETS vs ACTUAL SALES spreadsheet in XLSX format for each of our sales reps, but with the tabs at the bottom to be months.

There are two difficulties here: a) ReportCaster cannot burst XLSX output; b) Even with report types that are burstable ReportCaster can use the first BY field for either distribution OR for creating the tabs at the bottom, but not both at the same time.

So I was unable to simultaneously use the first BY field with a distribution list and also have the months across the bottom of my spreadsheet.

I got around this limitation by using ReportCaster solely for the scheduling of the report and EDAMAIL for the distribution.

In my case the burst value I wanted is the Sales Reps initials. I create a list dynamically at the beginning of the following code.

-DEFAULTH &SALESREP=' ';

-* Get a list of the sales reps who have TARGETS and count how many there are

TABLE FILE SALES_TARGS
SUM SALESREP
BY SALESREP NOPRINT
ON TABLE HOLD AS ALLREPS FORMAT ALPHA
END

-SET &NUMREPS=&LINES;
-*SET &ADMIN_EMAILS='gp@casti.net';
-SET &REPDATE=EDIT(&YYMD, '9999-99-99');
-RUN

-* Read the ALLREPS file one line at a time in a loop

-READFILE ALLREPS
-REPEAT ENDREPEAT &NUMREPS TIMES

-* Note that with READFILE amper variables are automatically created for the fields based on the fieldnames in the HOLD file
-* In this case it just happens to be the sales reps initials, which we use for internal e-mail

-SET &SREP_EMAIL=LOCASE(3, &SALESREP, 'A3') || '@xxxxx.com';

-SET &SUBJECT='Targets and Sales for ' | &SALESREP | ' ' | &REPDATE;

-* I want one copy of the report to go to the respective sales rep and a copy to come to me, 
-* so I concatenate the e-mail addresses as per the EDAMAIL documentation

-SET &ALL_EMAILS=&SREP_EMAIL || ';' | &ADMIN_EMAILS;

-* A whole bunch of code goes here for manipulating bits of data ....

-* The following isn't the actual code, but provides the idea - just create your report, compound or otherwise

-* I want to be able to retrieve my HOLD file from a specific location
APP HOLD BASEAPP

SET COMPOUND=BYTOC

TABLE FILE XYZ
SUM
    TARGET
BY  MONTH NOPRINT
SUM
     SALES
BY  MONTH NOPRINT
BY  CCODE AS 'Customer'
BY  DESC AS 'Product'
SUM
    QUANTITY/D12C AS ''
BY  MONTH NOPRINT
BY  CCODE AS 'Customer'
BY  PDESC AS 'Product'
ACROSS DEPARTMENT AS ''
ACROSS SOLDBY AS ''

-* The following indicates that I am creating the report for a single sales rep at a time
WHERE SALESREP EQ '&SALESREP'
HEADING
"Targets and actual sales for &SALESREP "
"Report Date <+0>&DATEtrMDYY <+0> "
ON TABLE SUBFOOT
" "
"This report runs from the file &FOCFEXNAME"
ON TABLE HOLD AS 'Targets and Sales for &SALESREP &REPDATE' FORMAT EXL07
ON TABLE SET STYLE *
     INCLUDE = problue,
$
END
-RUN

-* The above creates a single report based on whatever WHERE value you want.
-* It can be distributed independent of the BY clauses within the report itself
-* Use EDAMAIL to distribute the report - one copy to the sales rep and another to me

EX EDAMAIL &ALL_EMAILS, ReportCaster@xxxxx.com, &SUBJECT,a,XLSX,baseapp/Targets and Sales for &SALESREP &REPDATE
-RUN

-* Now get the next sales rep and repeat the process

-READFILE ALLREPS
-ENDREPEAT
-RUN

-* ReportCaster needs to produce something and distribute it so the only name in my distribution list is myself, 
-* and this just lets me know that the report has run

TABLE FILE XYZ
SUM DUMMY NOPRINT
BY CCODE NOPRINT
HEADING
"Targets and Sales Reports completed"
END

Now obviously this technique is not the most efficient, because the report runs sequentially for each "burst" value rather than running once and then using the distribution server to send. But it does allow reports of whatever complexity to be created and distributed without necessarily referencing the BY fields within the report.

This message has been edited. Last edited by: George Patton,

Thanks, I'll print this for future reference/development. I currently generate the report with looping and processing one region at a time and I use JavaMail to email it. My company purchased ReportCaster so the push is to move to ReportCaster but I don't see the sense in re-writing it to use EDAMAIL if it works fine with JavaMail and ReportCaster isn't really doing the distribution of the report.