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]Trouble Attaching Excel report to email through report caster

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Trouble Attaching Excel report to email through report caster
 Login/Join
 
Platinum Member
posted
I created an export of a report in excel (XLSX)format to distribute via email through a report caster job. I was able to set up the report caster job to attach this file to the email and send it.

I went back and redesigned that extract into a compound report (data on multiple tabs of the excel workbook). Now the file will no longer attach to the email from the report caster job. The .fex name remains the same as before. Below is the procedure that creates the excel file. I am using the OPEN and CLOSE commands to accomplish putting the data on the different tabs of the workbook.

 -TYPE *** IBFS:/WFC/Repository/eongou02/std_reports/reportsdevel/DailyShrinkFlash/Daily_Blended_File_Doug *** &DATEMtDY &TOD *** Start
-SET &ExecedFrom = 'Report' ;
-INCLUDE IBFS:/WFC/Repository/eongou02/std_reports/reportsdevel/DailyShrinkFlash/Daily_Shrink_Recap_File_Checking.fex
-SET &ECHO = 'ALL' ;
-DEFAULT &DIVISION = 99
-SET &FileTitle = IF &DIVISION.EVAL EQ 99 THEN 'Daily Shrink Recap, Total Company' ELSE 'Daily Shrink Recap, Division ' | &DIVISION ;
-SET &ECHO = 'OFF' ;
-TYPE *** &|FileTitle  = &FileTitle  ***
-SET &ECHO = 'ALL' ;
SET NODATA = ' '
DEFINE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_TBL
TYRATE/D7.2%=TYRATE*100;
LYRATE/D7.2%=LYRATE*100;
OPTRATE/D7.2%=OPTRATE*100;
GAPCL/D7.2%=GAPCL*100;
RDFCST/D7.2%=RDFCST*100;
END

TABLE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_TBL
PRINT
INVENTORY/MDYY AS 'DATE'
TYSHRINK/D10CBM AS 'TY ,SHRINK'
TYSALES/D10CBM AS 'TY ,SALES'
TYRATE AS 'TY ,SHRINK ,%'
STATUS
LYSHRINK/D10CBM AS 'LY ,SHRINK'
LYSALES/D10CBM AS 'LY ,SALES'
LYRATE AS 'LY ,SHRINK ,%'
VARBPS/D10CB AS 'VAR'
OPTDLLRS/D10CBM AS 'OPTIMIZED ,$'
OPTRATE AS 'OPTIMIZED ,%'
VARTOOPT/D10CB AS 'VAR ,TO ,OPTIMIZED'
GAPCL AS 'GAP ,CLOSURE'
RDFCST AS 'RD ,FCST'
FINPDPOSTED AS 'POST ,PERIOD'
COHORT
IMPROVED AS 'IMPRVD'
TSS
FINQTRPOSTED AS 'QTR'
FLAG AS 'HSAP'
POSTING AS 'POST ,DATE'
FINWKPOSTED AS 'FIN ,WK'
-IF &DIVISION.EVAL EQ 99 THEN GOTO SHOWALLREG;
WHERE SDIV EQ &DIVISION.EVAL
-SHOWALLREG
WHERE STATUS EQ 'Rec'
BY LOWEST SDIV AS 'DIV'
BY LOWEST REGION AS 'REG'
BY LOWEST DISTRICT AS 'DIS'
BY LOWEST LOCID AS 'STORE'
-*ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
-*ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT,FONT='CALIBRI',SIZE=9,COLOR=RGB(66 70 73),STYLE=NORMAL,JUSTIFY=CENTER,TITLETEXT=Stores,$
ENDSTYLE
ON TABLE PCHOLD AS '&FileTitle.EVAL' FORMAT XLSX OPEN
END





DEFINE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_DISTRICT_TBL
TYRATE/D7.2%=TYRATE*100;
LYRATE/D7.2%=LYRATE*100;
OPTRATE/D7.2%=OPTRATE*100;
GAPCL/D7.2%=GAPCL*100;
RDFCST/D7.2%=RDFCST*100;
TYOPTRATE/D7.2%=TYOPTRATE*100;
END

TABLE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_DISTRICT_TBL
PRINT
TYSHRINK/D10CBM AS 'TY ,SHRINK'
TYSALES/D10CBM AS 'TY ,SALES'
TYRATE AS 'TY ,SHRINK ,%'
LYSHRINK/D10CBM AS 'LY ,SHRINK'
LYSALES/D10CBM AS 'LY ,SALES'
LYRATE AS 'LY ,SHRINK ,%'
VARBPS/D10CB AS 'VAR'
OPTDLLRS/D10CBM AS 'OPTIMIZED ,$'
TYOPTRATE AS 'OPTIMIZED ,%'
VARTOOPT/D10CB AS 'VAR ,TO ,OPTIMIZED'
GAPCL AS 'GAP ,CLOSURE'

-IF &DIVISION.EVAL EQ 99 THEN GOTO SHOWALLDIS;
WHERE SDIV EQ &DIVISION.EVAL
-SHOWALLDIS
BY LOWEST SDIV AS 'DIV'
BY LOWEST REGION AS 'REG'
BY LOWEST DISTRICT AS 'DIS'
-*ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
-*ON TABLE NOTOTAL

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$

TYPE=REPORT,FONT='CALIBRI',SIZE=9,COLOR=RGB(66 70 73),STYLE=NORMAL,JUSTIFY=CENTER,TITLETEXT=District,$
ENDSTYLE
ON TABLE PCHOLD AS '&FileTitle.EVAL' FORMAT XLSX
END




DEFINE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_REGION_TBL
TYRATE/D7.2%=TYRATE*100;
LYRATE/D7.2%=LYRATE*100;
OPTRATE/D7.2%=OPTRATE*100;
GAPCL/D7.2%=GAPCL*100;
RDFCST/D7.2%=RDFCST*100;
TYOPTRATE/D7.2%=TYOPTRATE*100;
END

TABLE FILE EBRRPT_RPT_DAILY_BLENDED_SHRINK_SUMMARY_REGION_TBL
PRINT
TYSHRINK/D10CBM AS 'TY ,SHRINK'
TYSALES/D10CBM AS 'TY ,SALES'
TYRATE AS 'TY ,SHRINK ,%'
LYSHRINK/D10CBM AS 'LY ,SHRINK'
LYSALES/D10CBM AS 'LY ,SALES'
LYRATE AS 'LY ,SHRINK ,%'
VARBPS/D10CB AS 'VAR'
OPTDLLRS/D10CBM AS 'OPTIMIZED ,$'
TYOPTRATE AS 'OPTIMIZED ,%'
VARTOOPT/D10CB AS 'VAR ,TO ,OPTIMIZED'
GAPCL AS 'GAP ,CLOSURE'

-IF &DIVISION.EVAL EQ 99 THEN GOTO SHOWALL;
WHERE SDIV EQ &DIVISION.EVAL
-SHOWALL
BY LOWEST SDIV AS 'DIV'
BY LOWEST REGION AS 'REG'
-*ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
-*ON TABLE NOTOTAL

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$

TYPE=REPORT,FONT='CALIBRI',SIZE=9,COLOR=RGB(66 70 73),STYLE=NORMAL,JUSTIFY=CENTER,TITLETEXT=Region,$
ENDSTYLE
ON TABLE PCHOLD AS '&FileTitle.EVAL' FORMAT XLSX CLOSE
END







-RUN
-SET &ECHO = 'OFF' ;
-TYPE *** &RECORDS Records &LINES lines ***
-TYPE *** IBFS:/WFC/Repository/eongou02/std_reports/reportsdevel/DailyShrinkFlash/Daily_Blended_File_Doug *** &DATEMtDY &TOD *** Ended
 

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Silver Member
posted Hide Post
Hello,
You are missing the OPEN on your second report.
Have a great day,
Emily


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Expert
posted Hide Post
OPEN is not needed on 2nd report.
Need to FILEDEF and APPEND.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
OPEN is not needed on 2nd report.
Need to FILEDEF and APPEND.


Thank you Tom. I was thinking that the OPEN was only needed on the first report.

Can you please elaborate on where and how to use the FILEDEF and APPEND?


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
OPEN is not needed on 2nd report.
Need to FILEDEF and APPEND.


Also, is this going to try and append my 3 reports into 1? I still want each of my reports to live on a different tab in the XLSX file. thanks!


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
Hello,

One thing you could try is to override the format within the caster schedule. Open the schedule and edit your task (which should be calling your FEX). Within there you should find Report Properties, try checking the box that says "Override the Format Specified in the Procedure" and choose EXL07.

For some reason I think once upon a time I had to do that for a report to work. Just something to try.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by jcannavo:
Hello,

One thing you could try is to override the format within the caster schedule. Open the schedule and edit your task (which should be calling your FEX). Within there you should find Report Properties, try checking the box that says "Override the Format Specified in the Procedure" and choose EXL07.

For some reason I think once upon a time I had to do that for a report to work. Just something to try.


Well, that worked!

I'd like to understand more about that so I knew why it was needed. But I'll take the win :-)


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
And I wish I could explain why it is needed, but I'm not entirely sure either Smiler. It's as if compounding the report confuses the scheduler as far as the file-type is concerned coming from the FEX. Maybe someone else perusing this request may be able to explain it better.

I just recall once with a compound report having an issue and trying the override and it worked. Glad that worked for you as well. If it is indeed working and fixed, please update your original post and add [Solved] to the title.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Master
posted Hide Post
Unless you change the default, Caster output HTML, you always have to override the output format if it is not HTML.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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]Trouble Attaching Excel report to email through report caster

Copyright © 1996-2020 Information Builders