Since ReportCaster can not handle bursting compound excel reports, I'm trying to do so using and ETL flow to update the tables for a scheduled job. I have my ETL set up like this:

 TABLE FILE CAR
SUM COUNTRY
BY COUNTRY
ON TABLE SAVE AS VALS
END
- RUN
-SET &LNES = &RECORDS ;
-TYPE LOOP MODIFY &LNES TIMES
-REPEAT :LOOP  &LNES TIMES
-READ VALS NOCLOSE  &CNTRY.10.
-SET &CNTRY = TRUNCATE(&CNTRY);
-TYPE '&CNTRY.EVAL'
TABLE FILE BOTPARMS
SUM COMPUTE PARAM_VALUE/A3200V=' &CNTRY.EVAL';
BY SCHEDULEID 
BY TASKID
BY PARAM_NAME
BY PARAM_INDEX
WHERE SCHEDULEID EQ 'S14i1ies4f05';
ON TABLE HOLD AS PARAMS FORMAT ALPHA
END
-RUN
MODIFY FILE BOTPARMS
 
FIXFORM FROM PARAMS
 
MATCH SCHEDULEID TASKID PARAM_NAME PARAM_INDEX
	ON MATCH UPDATE PARAM_VALUE
	ON NOMATCH REJECT
	DATA ON PARAMS
END
-RUN
DEFINE FILE BOTSCHED
NOWDATETIME_A/HYYMDS=HGETC(8, 'HYYMDS');
NOWDATETIME/HYYMDS=HADD(NOWDATETIME_A, 'minute', -3, 8, 'HYYMDS');
NOWDATE/YYMD=&YYMD;
STARTDATE/YYMD=19700101;
DAYSDIFF/D8=DATEDIF(STARTDATE, NOWDATE, 'D');
MSDIFF/D32=DAYSDIFF*24*60*60*1000;
ADDOFFSET/D12=4*60*60*1000;
NOWMILLISEC/D12 = HTIME(8, NOWDATETIME, 'D12.2');
NEEDTOADD/D13=NOWMILLISEC+ADDOFFSET+MSDIFF;
-*NEXTRUNTIME_NEW/A17=FTOA(NEEDTOADD, '(D13c)', 'A17');
NEXTRUNTIME_T/A13 =  FTOA(NEEDTOADD, '(D13c)', 'A13');
NEXTRUNTIME_NEW/A32 = '0000000000000000000' | NEXTRUNTIME_T;
END
 
SET ASNAMES = ON
TABLE FILE BOTSCHED
PRINT SCHEDULEID NEXTRUNTIME_NEW AS NEXTRUNTIME
WHERE SCHEDULEID = 'S14i1ies4f05'; 
ON TABLE HOLD AS SCHDHOLD
END
-RUN
 
MODIFY FILE BOTSCHED
 
FIXFORM FROM SCHDHOLD
 
   MATCH SCHEDULEID
    ON MATCH UPDATE NEXTRUNTIME
    ON NOMATCH REJECT
    DATA ON SCHDHOLD
    END 
-RUN
SLEEP 75
-:LOOP 

This updates the parameter of the job, updates the next runtime and tells the job to wait long enough for ReportCaster to pick up the scheduled job and run it. This all works, but when the job is run, it for some reason does not get any records back from the Car file, and doesn't distribute anything.

This is the log I get from ReportCaster:

 Schedule Executed Due To NEXTRUNTIME 
   Starting task: burst test 
   Task type: MR Standard Report 
   Task domain: developm/developm.htm 
   Retrieving MR report: app/burst_test 
   Connecting to server MFADHOC with static execution id 
   Executing focexec. 
   No report to create. 
   SET COMPOUND = OPEN 
   TABLE FILE CAR 
   SUM 
   SALES 
   AVE.DEALER_COST AS AVE,DEALER_COST 
   BY COUNTRY 
   WHERE COUNTRY EQ W GERMANY 
   HEADING 
   "" 
   FOOTING 
   "" 
   ON TABLE SET PAGE-NUM OFF 
   ON TABLE NOTOTAL 
   ON TABLE PCHOLD FORMAT EXL2K 
   ON TABLE SET HTMLCSS ON 
   ON TABLE SET STYLE * 
   UNITS=IN, 
   SQUEEZE=ON, 
   ORIENTATION=PORTRAIT, 
   TYPE=REPORT, 
   GRID=OFF, 
   FONT= ARIAL , 
   SIZE=9, 
   TYPE=TITLE, 
   STYLE=BOLD, 
   TYPE=TABHEADING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=TABFOOTING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=HEADING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=FOOTING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=SUBHEAD, 
   SIZE=10, 
   STYLE=BOLD, 
   TYPE=SUBFOOT, 
   SIZE=10, 
   STYLE=BOLD, 
   TYPE=SUBTOTAL, 
   BACKCOLOR=RGB(210 210 210), 
   TYPE=ACROSSVALUE, 
   SIZE=9, 
   TYPE=ACROSSTITLE, 
   STYLE=BOLD, 
   TYPE=GRANDTOTAL, 
   BACKCOLOR=RGB(210 210 210), 
   STYLE=BOLD, 
   ENDSTYLE 
   END 
   SET COMPOUND = CLOSE 
   TABLE FILE CAR 
   SUM 
   SALES 
   AVE.DEALER_COST AS AVE,DEALER_COST 
   BY MODEL 
   WHERE COUNTRY EQ W GERMANY 
   HEADING 
   "" 
   FOOTING 
   "" 
   ON TABLE SET PAGE-NUM OFF 
   ON TABLE NOTOTAL 
   ON TABLE PCHOLD FORMAT EXL2K 
   ON TABLE SET HTMLCSS ON 
   ON TABLE SET STYLE * 
   UNITS=IN, 
   SQUEEZE=ON, 
   ORIENTATION=PORTRAIT, 
   TYPE=REPORT, 
   GRID=OFF, 
   FONT= ARIAL , 
   SIZE=9, 
   TYPE=TITLE, 
   STYLE=BOLD, 
   TYPE=TABHEADING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=TABFOOTING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=HEADING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=FOOTING, 
   SIZE=12, 
   STYLE=BOLD, 
   TYPE=SUBHEAD, 
   SIZE=10, 
   STYLE=BOLD, 
   TYPE=SUBFOOT, 
   SIZE=10, 
   STYLE=BOLD, 
   TYPE=SUBTOTAL, 
   BACKCOLOR=RGB(210 210 210), 
   TYPE=ACROSSVALUE, 
   SIZE=9, 
   TYPE=ACROSSTITLE, 
   STYLE=BOLD, 
   TYPE=GRANDTOTAL, 
   BACKCOLOR=RGB(210 210 210), 
   STYLE=BOLD, 
   ENDSTYLE 
   END 
   -* 
   SET DISTRIBUTE=OFF 
   Task finished. 
   No report to distribute. 

**The COUNTRY value is enclosed in tick marks in the report although the RC log doesn't show them.  I've tried it both with .EVAL and without.
 

The weird thing about this is that if I go into the ReportCaster Console and run the report manually, I have no problems. It executes fine and I get my output. Does anyone have any suggestions or ideas on how reportcaster might be working under the covers? Or some other idea on how I might be able to achieve my objective?

Thanks,
Eric

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

quote:

I've tried it both with .EVAL and without.

Have you tried it with .QUOTEDSTRING?

T

Tony,

QUOTEDSTRING did the trick. It turns out that you need to use QUOTEDSTRING within the ETL defines and not in the actual FEX.

Now we finally have an answer on how to Burst out a compound excel Report. Its not pretty, but it works.