Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Updating ReportCaster parameters with ETL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Updating ReportCaster parameters with ETL
 Login/Join
 
Gold member
posted
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,


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Expert
posted Hide Post
quote:
I've tried it both with .EVAL and without.

Have you tried it with .QUOTEDSTRING?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Gold member
posted Hide Post
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.


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Updating ReportCaster parameters with ETL

Copyright © 1996-2020 Information Builders