Exporting Crystal Reports With Date In File Name ?

[CmdrGravy]

Hi

This is probably a dumb question but we have just upgraded our Aspect reporting from Report Runner/Writer to Crystal Reports.

Using Report Runner we could schedule reports and have them exported to file and use $m-$d to put the date they were generated into the filename.

I have been playing around with scheduling reports using the Info Desktop in Crystal but I just cannot find anyway to do a similar thing and include the date in the file name when I export the file to a Shared Drive somewhere. Is it possible to do this, and if so - how ?

 

[emcevoy]

Hi

Unfortunately, this is not something that is possible with Info Desktop. When Aspect bundled in the Crystal into their system, they made the necessary adjustments in runner to enable this, basically goes thru another step in Runner before the final export. Crystal Decisions have not programmed this functionality into their product, but would be nice.

Hope this helps.

Eileen McEvoy
Authorized Crystal Engineer
Crystal Reports Consultant and Trainer

http://www.crystalconsulting.ca

emcevoy@crystalconsulting.ca

 

[Moebius01]

On a side note, a small batch script could be written to rename files dynamically. Basically append a date stamp to them. All you would have to do is dump the files to a give folder (or set of folders) then have the batch script execute before uploading or distributing the reports.

 

[CmdrGravy]

That sounds like a viable plan, do you know if it is possible to have a batch file execute once a report has finished running, immediatley after it is saved ?

If you could get from Seagate the name of the last report it had saved into a batch file it would make things a whole lot easier since I would only have to write one script ( rather than hundreds ! ) which could be used for all my reports but I will need to find out if that is possible or not ?

Thanks for all your help.

Joe.

 

[Moebius01]

I don't think there's a way to do it as you described. However, a batch file could be written in a way to date stamp all files in a directory, or set of directories, so you'd still only need one script. Basically, it would run the command on every file in the directory. I'll have to do a bit of digging, but I think I have code that would do it.

 

[Moebius01]

I couldn't find the script I had written for this a while back, but I'm going to write up a small sample below. The downside of this is that it would put the date at the beginning of the file. To put it before the extension requires a bit more work, and I can't remember if off the top of my head. Still, this might give you an idea.
*********BATCH FILE********
@ECHO OFF
::Set Variables
set local=c:\folder::Create Date Variables
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set DayOfWeek=%%i
set Month=%%j
set Day=%%k
set Year=%%l)
:: Process
dir %local% /b | find /v "#Recycled" > files.txt
Echo. Renaming Files
for /f %%x in (files.txt) do ren %local%%%x %Month%-%Day%-%Year%-%%x
ONE
:: Clean Files Created By Script
del files.txt > nul

:END
Echo Report Rename Script Completed
*********BATCH FILE********

This would rename report.xls to 5-29-2003-report.xls, and so on. To create report-5-29-2003.xls requires a bit more work (and is something I don't remember off the top of my head).

 

[CmdrGravy]

Thanks again for all your help so far moebius, I will try this batch file out and see how it works ( a bit rusty on Windows batch files right now ).

I have just been thinking and think there might be another way but I would like to know if anyonce can see any major problems or "don'ts" with this:

Looking at the CINFO.MDB database which Seagate Info seems to keep all the schedule information in ( amongst other things by the look of it ). There is a table containing details of all the scheduled reports including a field with the path and filename the report will be exported to.

What I was thinking of doing was writing a little VB program to read through this field every day and if it came across a $m or $d string within the file and path name it would replace them with the correct day and month instead and update the table with the new file and path string.

It would have to do this every day before any of the scheduled reports ran but that could easily be arranged.

Does this sound like a sensible plan or is there anything anyone can see which would trip me up ?

 

[CmdrGravy]

Hmm, thinking was never my strong point - I have just realised the problem with my previous suggestion is that once the file name and path had been updated once then it would lose the $m $d identifiers and never be updated again

 

[nnamdi]

Here's how we have it set-up. see if it this is applicable in your environment.
1. We have over a 100 reports scheduled.
2. Keep the name and location of the report constant for each report.
3. Have a log of the report schedule times.
4. Build a batch job to copy each of the reports in the specified output to your distribution point and maybe an archive point. Rename the copies by appending some form of date stamp.
5. Run the batch at least an hour after your last report to give time for overlappng reports to complete.
The advantage to this is that your output locations always have clean current reports. The output location also never gets cluttered. The disadvantage is that on the seainfo desktop the instances for each report will point to the same data.

If this seems like someting you want to do i can send you a copy of one of our batch jobs.

 

[Moebius01]

We actually used to do something almost identical to that. But unless I'm misreading, doesn't that mean any time you change a report, or add a new one that the batch job must be modified with the new report name?

Also, in your case, are you changing file names, or just copying them to a remote location?

 

[jtomberlin]

A little something else that may help you (if you haven't already worked around this). You can actually use SI7 (or 7.5) to run programs, such as batch files and base it's run condition upon the presence of a particular file generated by your report object. For that matter, you can also schedule the batch file to run at the appropriate times, if that works out to be easier.

I use this method to run a batch file daily to clean up the tons of temp files that SI likes to leave lying around.

 

[CmdrGravy]

I noticed that you can schedule batch files using SI7 but I haven't really investigated this much.

I don't quite understand what you mean though when you say you can base the run condition on the presence of a generated file, do you mean you can somehow pass the outputted filename to the batch file or program ?

Also the main problem I am having at the moment is that our disk on the Seagate Server is far too small so I'm trying to cut back on the amount of unecessary junk as much as possible - where about's are these temporary files and is there anything to look out for when deleting them ?

Thanks, Joe.

 

[jtomberlin]

I knew that sounded a bit fuzzy when I typed it, but I will try to clarify a bit.

You can schedule batch files, or reports to run, based on Programs/Events under the 'When' tab. In order to use this, you will have to go into Info Administrator and set up an 'Event'. A file event is basically just looking to see if a particular file name exists, and if so, the event check is satisfied and it kicks off the report/program object. There should be a couple of example files named file1.txt and file2.txt already listed under Events. You can specify the directory and file name you wish to use (such as the output file from one of the report runs). You can use this to set up an event for each report, or simply wait for the last report to run, and base your check off of the file it generates. It is important to note that you will need to remove the file you are basing your event off of to reset the cycle, or it will just run the one time and stop there.

If you do not wish to base your schedule off of a particular file at all, due to it being on a machine that isn't convenient to access by the APS, etc., you can use what is known as a 'generic event'. This is also set up in the Info Administrator and is basically just a flag that can be set or cleared by the Notify - Events portion of the schedule dialog box. Your last report 'sets' the flag and your batch file 'clears' the flag after it runs.

As for the excess temporary files, these usually show up in the Windows directory (winnt, etc) or in the defined temp file directory in the system variables, such as c:\temp. Do a search for .tmp files and you should find them easily enough. There are quite often thousands upon thousands of these that get left behind, as SI's clean up routine doesn't always seem to get theh files.

I hope this information is clear enough. There really isn't a lot of documentation available for the Events portion of SI...I actually had to attend a class on SI administration to get even this much info.

J Tomberlin

 

[Moebius01]

In addition, if there was trouble scheduling to an event like that, you could through a "trigger" report in. Basically, a report that shows nothing called "Complete" or something like that. Then have the batch file check for the existence of that report before continuing.

Finally, throwing these lines in before the renaming begins will get rid of the temp junk left behind. This was designed for ReportRunner, but should work just as well for SI7.

:: Clean Report Runner / Writer Junk From Machine
DEL /F/S/Q C:\~*.*
DEL /F/S/Q C:\*.TMP
DEL /F/S/Q C:\*.??0
DEL /F/S/Q C:\*$*.*

@ECHO OFF

 

[michaelgray]

Not sure if anyone cares... but I have expanded on Moebius01's batch which was written higher in this thread.

What it does is scans directories (not just the current directory) for files starting with in this case "~" and renames those.

It may need tweaking for your own system... (I Have used E:\ as my example, and hard-coded is certain functions because I could not be bothered)

=================BATCH===============
:: Begin
@ECHO OFF

::Set Variables
set local=E:\"XYZ ABC"

:: Create Date Variables
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set DayOfWeek=%%i
set Month=%%j
set Day=%%k
set Year=%%l)

:: Process
@Echo Directory Dump
dir %local% /ad/b/s | find /v "#Recycled"> E:\files.txt

@Echo Renaming Files in Directories
for /f "delims=?" %%x in (E:\files.txt) do (
chdir %%x
for %%y in (~*.*) do ren "%%x"\"%%y" "%Month%-%Day%-%Year%-V1.0%%y"
)


:: Clean Files Created By Script

del E:\files.txt > nul

:: END

Echo Report Rename Script Completed

=================END BATCH===============

 

[jocago]

If you are already thinking of using VB to create an app to change a record in a db, why don't you create a VB app to check the directory every hour for a record in a config file or a db and replace the output filename from infodesktop with a format that includes the date/time that you prefer. This is exactly what we had to do and it works well. I would share the .exe with you, but it is owned by the company since I developed it on company time. I would, however, be more than willing to share tips about the creation of it (if anyone is still interested).

 

[700503]

jocago, hello, I am interesting how to write a vb to update the output filename. Can u provide more information?

Many Thanks

Samuel

 

[andyjackson]

I use KIX with a bit of trickery to name with todays date when the reports have finished, tried to get yesterdays date on it but it didnt work, it also includes a self housekeepingt to create a directory with the mont year name etc..