Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Append datestamp onto file using ReportCaster to FTP
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Append datestamp onto file using ReportCaster to FTP
 Login/Join
 
Silver Member
posted
Hi:
I've seen a number of discussions regarding adding a datestamp to a file but most end with the solution being something programmatic within the .fex. Has there been any movement made to do this in the ReportCaster tool? Seems like that would be the logical place for such a thing?
Thanks,
B

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


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
Save As: filename_&YYMD.xlsx/txt/csv/docx/etc...
Works for us...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Hi Tom:
Thanks for your reply- are you saving it as filename_&YYMD.csv in the .fex and in the ReportCaster schedule? We tried saving it in the schedule but it FTP'd as filename_&YYMMDD.csv to the test site. We are hoping to send YYYYMMDD for the date.
B


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
We use email, not FTP. May have to look into the manual, or, wait for anther response...
We use the Save As: for the filename in ReportCaster...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Ah okay- thanks Tom -we cannot email this data; must use SFTP. I tried filename_&YYMD.csv as well as the variation I mentioned above and ReportCaster delivers the filename_&YYMD.csv rather than the actual date on the filename. I'll take a look at the manual again and hope more folks respond with their experience as well. thanks again!


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
Guru
posted Hide Post
FTP works as well, you may have to set the "Override the Format ..." option in Reportcaster to make it work.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
We have this working with SFTP. Here is an example that you can change to fit your needs:

Fex:
 
-SET &HR_MIN = EDIT(&TOD,'99$99');
-SET &FILE_NAME = &YYMD||&HR_MIN;

-* all the code that creates the data file

ON TABLE PCHOLD AS ParkingISOList&FILE_NAME FORMAT DFIX DELIMITER |
END
-RUN

-* NOTE: CAN'T HAVE A -EXIT IN THIS QUERY OR THE & VARIABLES IN THE SCHEDULE WILL NOT RESOLVE
 


Schedule:

On the Task tab "Save Report As:" ParkingISOList&FILE_NAME|.txt
(I do not have Override the Format checked)

On the Distribution tab (General) Name: ParkingISOList&FILE_NAME|.txt


WebFOCUS 8.2.01M
 
Posts: 12 | Location: Charlotte, NC | Registered: December 19, 2012Reply With QuoteReport This Post
Master
posted Hide Post
Do you have to use Report Caster to do the sftp? Can you use an external script and call it from within the fex?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 953 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Read the docs: Procedure: How to Use the FTP Distribution Option

quote:
To use the dot file extension separator after a variable, terminate the variable with the '|' character (for example, &YYMD|.htm).


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 602 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thank you all for your excellent suggestions and replies!
Frans- I tried "Override the Format Specified..." and Save Report As: cartest_dummy_data&YYMD|.csv My Error is: cartest_dummy_data&YYMD|.csv unable to distribute to sft......and "The distribution failed: No such file"


Joanna- I tried adding code to the .fex for a variable for the date but it delivered my file with the name cartest_dummy_data&DATE.csv. (it did not add today's date to the filename). And when I try to Burst, I get the error "The file for the distribution list does not exist".(I do not have a -EXIT in my code)

Jgelona- I am able to use another scheduling tool to append the datestamp- but was hoping to simplify and use only ReportCaster to run the fex and deliver the file.

Hallway- yes- thanks I re-read the doc and tried to add &YYMD|.csv also tried &YYYYMMDD|.csv (which is the format of the date they require). "Error:
The distribution failed: No such file"

this is the code in my .fex:

TABLE FILE CAR
PRINT DEALER_COST RETAIL_COST SALES
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT XLSX
END


I am able to SFTP the file when I don't attempt to append a date to the end of the filename. cartest_dummy_data.csv goes to our SFT server successfully.

Seems like I just don't have the correct combination of .fex code and ReportCaster configuration just yet...


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
Member
posted Hide Post
B - Can you let us know your exact requirements? I'm surprised that you are trying to SFTP a file and also trying to Burst at the same time. Burst changes the name of the file to include the burst value in the file name.


WebFOCUS 8.2.01M
 
Posts: 12 | Location: Charlotte, NC | Registered: December 19, 2012Reply With QuoteReport This Post
Silver Member
posted Hide Post
Hi Joanna- I have tried many iterations- Burst was only one of them. There is no need to burst. The goal was to deliver a .csv file to a 3rd party vendor using only ReportCaster- they require a datestamp in the format of YYYYMMDD appended to the filename. I am able to get the .fex to run (independently of ReportCaster) and append the datestamp YYYYMMDD onto the filename. But the minute I run the .fex through ReportCaster, the datestamp is not recognized or translated and I deliver a file without it. I have changed the .fex code to be this now:
-SET &MYDATE = &YYMD;
TABLE FILE CAR
PRINT DEALER_COST RETAIL_COST SALES
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
ON TABLE PCHOLD AS cartest_dummy_data&MYDATE FORMAT DFIX DELIMITER ,
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=CAR, SQUEEZE=2,$
ENDSTYLE
END
-RUN


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Barb

I think it is time to open a case with techsupport if you haven't already.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1885 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
B. Not sure you understand what I'm saying. Use Report Caster but an external script to send the file to another server. Note this is for our Linux system where we sftp files to the our local network server:
TABLE FILE CAR
PRINT DEALER_COST RETAIL_COST SALES
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
ON TABLE HOLD AS CARTEST FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=CAR, SQUEEZE=2,$
ENDSTYLE
END
-*
 FILEDEF FTPSH DISK ftpsh.txt ( LRECL 120 RECFM V
-RUN
-SET &FILENM='cartest_' | &YYMD | '.xlsm';
-WRITE FTPSH #!/bin/csh
-WRITE FTPSH smbclient //sXXx01/workgroups "password" -Iss.sss.ss.ss -WOOOOO -Uuserid<< :end
-WRITE FTPSH cd "\Child Welfare\REPORTS"
-WRITE FTPSH put &FILENM
-WRITE FTPSH quit
-WRITE FTPSH :end
-RUN
-UNIX . ftpsh.txt


This creates a file, ftpsh.txt, that contains the Linux commands to use smbclient to send the report to a remote Windows server.

The -UNIX . ftpsh.txt command tell Linux to execute the commands in ftpsh.txt

This uses smbclinet to do the sftp, if you are going to another server you can use ftp or sftp. Another option is to see if your system guys can set up a symbolic links to your other severs then use scp to just copy the file(s) to where you want them.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 953 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thank you again, all!
We are able to deliver the filename_YYYYMMDD.csv file using SFTP- after creating a separate .fex in the Dataserve area which has this code:
***************************************
-SET &S_TIME='myfilename_' || &YYMD || '.csv';
-TYPE &S_TIME

DEFINE FILE CAR
FTP_FILENAME/A40 WITH COUNTRY = '&S_TIME' ;
END

SET ASNAMES=ON

TABLE FILE CAR
PRINT FTP_FILENAME AS DEST
IF RECORDLIMIT EQ 1
ON TABLE PCHOLD
END

************************
and then in the ReportCaster schedule, General Tab, we use "Dynamic Distribution List" for the SFTP and give the path to the above .fex,
and the Distribution Options tab has our SFTP info- where the file should go.


now WF8 Release 8.2.01M, Windows 2012 now in Production
 
Posts: 41 | Location: Colorado | Registered: January 13, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Append datestamp onto file using ReportCaster to FTP

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.