Is there a way to run a report just to get the dataset and send it to an FTP server, without having to format a report?
I like the reporting format, but many of the reports that I would like to make are simply SQL queries that would be FTPed to a server and stored as CSV files. My problem is that if I try to make a traditional report and do this using the table component, some of my datasets have 40+ rows and this seems to make the table component very cumbersome. In fact it seems to start to slow down on me at around 25 columns.
I am only using the table component because I don’t know how else to get the dataset from the query to spit out into a CSV file via the report’s scheduling mechanism.
Thanks!
Sounds like a job for a gateway event script, not a report. Scripts can retrieve the data (e.g. system.db.runPrepQuery()), obtain the CSV from the dataset, and send an email.
I actually have it running as a gateway event script right now and emailing. Then I found out the end users wanted it FTP’d to their site. I knew the reports could FTP fairly easily but could not find a way to do it in scripting.
The report interface is very nice in that it also keeps a very organized and visual record of reports, which i would consider this one of. So I like having it get the data but am just not sure how to send it to a FTP server as a CSV without using the table component.
So I am blocked - scripting with no FTP or report module where I can’t get the data out. Kind of ironic.
If you’re running in 7.8 and emailing the report successfully, you should be able to FTP it also. Check out system.report.executeAndDistribute – ftp is the fourth action setting listed, and the next to last code snippet is an example of using it.
If you’re emailing it without being in a report, then you’ll need a Jython solution for FTP. I know there’s libraries out there, but they aren’t coming to mind at the moment.
[edit]
Whaddya know, they’re built in. jython.org/docs/library/ftplib.html