Here's what I'm trying to do. I have a compound report that I want to burst using a dynamic address in Report Caster.

First there would be a job to get a list of countries that the compound report would be run for.

 
TABLE FILE CAR
PRINT
COUNTRY
WHERE COUNTRY IN ('ENGLAND', 'JAPAN', 'ITALY');
ON TABLE PCHOLD
END 

Then the compound report would need to be run for each of the countries in the list.

  
TABLE FILE CAR
PRINT
RETAIL_COST
DEALER_COST
BY COUNTRY NOPRINT
BY BODYTYPE
BY MODEL
ON COUNTRY SUBHEAD 
"<COUNTRY"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE PCHOLD FORMAT PDF OPEN
END

TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
COMPUTE VAR/D12.2 = RETAIL_COST - DEALER_COST; AS 'VARIANCE'
BY COUNTRY NOPRINT
BY BODYTYPE
ON COUNTRY SUBHEAD
"<COUNTRY"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE PCHOLD FORMAT PDF CLOSE
END

Then the distribution would run a job that would get the email address for each country.

  
VALUE             DEST
ENGLAND           BOB@XYZ.COM
ITALY             FRANK@XYZ.COM
JAPAN             LISA@XYZ.COM
W GERMANY         JOE@XYZ.COM

I'm sure this is possible, but I don't know how to create the loop to run the complete compound report for each COUNTRY value and store the output for bursting.

Thanks for your suggestions.
Bethany

Have you tried this as is? Does it not work?

I presume you would need some sort of loop to run through the list of countries. I don't know how to do that.

Hi Bethany,

Just so happens I just completed one, so, here's some code:

  TABLE FILE CAR
SUM
     COUNTRY
BY COUNTRY NOPRINT
  ON TABLE SAVE AS MGR_LIST
END
-RUN
-*******************************************************************************
-SET &MAXCNT  = &LINES;
-SET &CNTR    = 0;
-REPEAT BEG_LOOP WHILE &IORETURN EQ 0;
-READ MGR_LIST NOCLOSE &COUNTRY.A16.
-SET &CNTR       = &CNTR + 1;
-SET &OPEN_CLOSE = IF &CNTR EQ 1       THEN 'OPEN'  ELSE
-                  IF &CNTR EQ &MAXCNT THEN 'CLOSE' ELSE ' ';
-*******************************************************************************

TABLE FILE CAR
PRINT
RETAIL_COST
DEALER_COST
BY COUNTRY NOPRINT
BY BODYTYPE
BY MODEL
ON COUNTRY SUBHEAD 
"<COUNTRY"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE PCHOLD FORMAT PDF &OPEN_CLOSE
END
-RUN
-IF &CNTR EQ &MAXCNT GOTO EOJ;
-BEG_LOOP
-EOJ
-EXIT

Thanks Tom. I tried to run your code and got an error: A VALUE IS MISSING FOR: &IORETURN

Where do you set &IORETURN?

Hi Bethany,

Change this

-REPEAT BEG_LOOP WHILE &IORETURN EQ 0;
-READ MGR_LIST NOCLOSE &COUNTRY.A16.

to

-REPEAT BEG_LOOP &MAXCNT TIMES
-READ MGR_LIST NOCLOSE &COUNTRY.A10.

Hi Bethany,

This works, just tested. Moved COUNTRY to the HEADING..

 TABLE FILE CAR
SUM
     COUNTRY
BY COUNTRY NOPRINT
  ON TABLE SAVE AS MGR_LIST
END
-RUN
-*******************************************************************************
-SET &MAXCNT  = &LINES;
-SET &CNTR    = 0;
-REPEAT BEG_LOOP &MAXCNT TIMES
-READ MGR_LIST NOCLOSE &COUNTRY.A10.
-SET &CNTR       = &CNTR + 1;
-SET &OPEN_CLOSE = IF &CNTR EQ 1       THEN 'OPEN'  ELSE
-                  IF &CNTR EQ &MAXCNT THEN 'CLOSE' ELSE ' ';
-*******************************************************************************

TABLE FILE CAR
HEADING CENTER
"<COUNTRY </1"
PRINT
RETAIL_COST
DEALER_COST
BY COUNTRY NOPRINT
BY BODYTYPE
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE PCHOLD FORMAT PDF &OPEN_CLOSE
END
-RUN
-IF &CNTR EQ &MAXCNT GOTO EOJ;
-BEG_LOOP
-EOJ
-EXIT
 

I got the code to work and added an additional compound report. Now I'm trying to test distribution in report caster.

  TABLE FILE CAR
SUM
     COUNTRY
BY COUNTRY NOPRINT
  ON TABLE SAVE AS MGR_LIST
END
-RUN
 

I'm trying to burst the reports by country. I tried to make the country list a pre-processing job but got this error.

THE FOCEXEC PROCEDURE CANNOT BE FOUND: BURSTNQW

I put the file in the same domain and folder that the rest of the job is in. Anyone know why it won't find the job???

Thanks for your help Tom!
Bethany

Hi Bethany,

I use the Dynamic Address List, which is the focexec that creates the distribution list, not a pre-processing fex from the Advanced button(I "think" that's what you are doing).

When you select Dynamic Address List, there will be a button to double-click on that will list all application domains, then the focexec's; select the email list focexec.

For your example, you'll need:

 

DEFINE FILE CAR
-* X_MGR/A55  = '&MO_C.EVAL' || ' ' | '&FY.EVAL' | ' - ' | F_MGR;
 X_ADDR/A30   = DECODE COUNTRY (
                    'ENGLAND' 'Bethany@IBIFocalPoint.com' 
                    'FRANCE'  'Tom@IBIFocalPoint.com
             continue on with the other countries...
                  ELSE ' ');
END
TABLE FILE CAR
SUM
    COUNTRY      AS 'VALUE'
    X_ADDR       AS 'DEST'
  ON TABLE PCHOLD
END
-RUN
 

Then, you schedule your compound job and ReportCaster will take care of the rest...

Hope this helps...

Tom

I don't know if I'm being overly simplistic about this, but it seems that all that needs to be done is create a coordinated compound PDF report and distribute it using a dynamic distribution list. We have done this in several apps. No need for loops and counters and such. All three reports are run, sorted together, burst, and distributed to the email associated with a country via a dynamic distribution list. I can work through some sample CAR code next week, if needed.

Darin,

PLEASE do. I set counters to use one set of code to OPEN and CLOSE.
I distribute an EXCEL workbook to 180 managers; maybe I'm doing to much; don't know.


Please share your thoughts and code, much appreciated...

Tom

Bethany

I think you have got a far to complex solution.

Your basic compound report is already as it should be, but just remove the line(s) "where country eq ...". that is not needed since the burst value takes care of this.

In the RC setup you say this should be a burst report.

then you either create the static burst value list with the fixed mail adresses and that is it.

No looping or other complex things.

If your address list should be dynamic then you create a fex that builds that XML file on the fly.

BTW The compound report itself could be done in one report. Take a look at "multiverb" possibilities.

The reports I'm trying to burst are an account summary and then transaction detail and payroll details. The final output is pulled from different tables so I don't think a multiverb report is possible.

I tried to make this really simple and create 3 car reports and put them all into a report caster job with 3 tasks. This worked, sort of. Each person on the distribution list got 3 files for each country. Since I had 2 countries on the distribution file, I got 6 total PDF attachments. Ideally, I would like for each person to get one PDF attachment (including Spec Report, Cost Difference Report, and Average Cost Report) for each country they are assigned to.

Files used:

Spec Report

 TABLE FILE CAR
PRINT
LENGTH
WIDTH
BY COUNTRY
BY BODYTYPE
BY MODEL
HEADING
"CAR SPECS"
ON TABLE PCHOLD FORMAT PDF
END 

Cost Report

  
TABLE FILE CAR
PRINT
COMPUTE MODEL2/A15 = EDIT(MODEL, '999999999999999');
DEALER_COST AS DCOST
RETAIL_COST AS RCOST
COMPUTE DIFF/D12.2 = RETAIL_COST - DEALER_COST;
BY COUNTRY
BY BODYTYPE
HEADING
"COST DIFFERENCES"
ON TABLE PCHOLD FORMAT PDF
END

Average Cost Report

TABLE FILE CAR
SUM
AVE.DEALER_COST
AVE.RETAIL_COST
BY COUNTRY
BY BODYTYPE
HEADING
"AVERAGE COST"
ON TABLE PCHOLD FORMAT PDF
END  

I also tried setting up the RC job to have one task that runs all of the reports from a single fex.

 
SET COMPOUND = OPEN
-INCLUDE CARFILE1
SET COMPOUND = OPEN
-INCLUDE CARFILE2
SET COMPOUND = CLOSE
-INCLUDE CARFILE3
-EXIT
 

For this run, I received 2 files but each only had the Average Cost Report output for each country.

Bethany

I also tried creating a coordinated compound report with the PDF layout painter. When I burst the output, each user got one PDF for each country assigned to them; however, we each got 5 pages for each file with the Average Cost repeated on all 5 pages, but nothing of the other 2 reports.

Any ideas???

Bethany

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