Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Scheduling a report
- Thread starter bhp
- Start date
synapsevampire
Programmer
"I only want the report to run if it returns data"
The report won't know if it returns data until it runs, that's where the snake begins to eat it's tail...
Under the Report Options in Crystal Reports, you can select Suppress Printing if no records.
Check out creating Events in CE for more sophisticated options.
-k kai@informeddatadecisions.com
The report won't know if it returns data until it runs, that's where the snake begins to eat it's tail...
Under the Report Options in Crystal Reports, you can select Suppress Printing if no records.
Check out creating Events in CE for more sophisticated options.
-k kai@informeddatadecisions.com
- Thread starter
- #3
synapsevampire
Programmer
Active-X scripting???
Never heard of it...I know that you can use CSP/VB Script/Java Script to accompish this, please share what you used.
-k kai@informeddatadecisions.com
Never heard of it...I know that you can use CSP/VB Script/Java Script to accompish this, please share what you used.
-k kai@informeddatadecisions.com
- Thread starter
- #5
Sure am happy to send, basically 3 things need to happen:
1. A SQL DTS job needs to be created, this dumps the info to a text file - this is where the active x script hangs of, in order that the file is only dumped when data is returned.
2. A batch job is set-up which deletes the aforementioned file
3. An alert is set-up on Crystal Enterprise, that looks for an instance of the aforementioned file.
The Active x script will need to be edited for database connectivity and the select changed.
Is there somewhere where I can place the script rather that in a posting?
1. A SQL DTS job needs to be created, this dumps the info to a text file - this is where the active x script hangs of, in order that the file is only dumped when data is returned.
2. A batch job is set-up which deletes the aforementioned file
3. An alert is set-up on Crystal Enterprise, that looks for an instance of the aforementioned file.
The Active x script will need to be edited for database connectivity and the select changed.
Is there somewhere where I can place the script rather that in a posting?
I want to do the same, could you plz help me. I am not using SQL server, I am using Oracle. Is there any way I can write PL/SQL code to dump the file. I guess you are talking about event based scheduling. In that case every time you need to drop the text file and recreate it.
I guess the batch file will do that.
2. A batch job is set-up which deletes the aforementioned file
What kind of script you have used to create this batch file?
RB
I guess the batch file will do that.
2. A batch job is set-up which deletes the aforementioned file
What kind of script you have used to create this batch file?
RB
- Thread starter
- #7
There are probably several ways to create a file in an Oracle environment. The UTL_FILE package is one way, that can be run from PL/SQL. My data warehouse load creates a load-complete file on the database server using the sqlplus SPOOL command, and then the unix shell script ftp's the file to the W2K3 CE server. Enterprise looks for the ftp'd file as a File Event for a set of daily-recurring reports. The file is removed from the CE server by a batch file (IF EXIST LCW_LOADED.txt DEL LCW_LOADED.txt) that is run by the Windows scheduler.
(There is a problem with CE's Event server design that forces me to prevent the file from being created before the reports are scheduled to run. This may not be a problem in your case. For me, the ftp'd file is not the name the event server looks for. Another batch file [IF EXIST LCW_LOADED.CE DEL LCW_LOADED.CE // IF EXIST LCW_LOADED.txt REN LCW_LOADED.txt LCW_LOADED.CE] renames the event file one minute after the report schedule time. The cleanup batch also conditionally deletes the .CE file.)
(There is a problem with CE's Event server design that forces me to prevent the file from being created before the reports are scheduled to run. This may not be a problem in your case. For me, the ftp'd file is not the name the event server looks for. Another batch file [IF EXIST LCW_LOADED.CE DEL LCW_LOADED.CE // IF EXIST LCW_LOADED.txt REN LCW_LOADED.txt LCW_LOADED.CE] renames the event file one minute after the report schedule time. The cleanup batch also conditionally deletes the .CE file.)
Hi,
Not enough specifics to be precise, but for Oracle you would:
A: Create a PlSql Proc that will output a text file ( see the DBMS_OUTPUT and UTIL_FILE Docs)if there are any rows of data
B: Schedule that Proc using the DBMS_JOB package ( again, see the Docs)
C: Have the Event Server look for the file after the scheduled time for the Job run..
![[profile]](/data/assets/smilies/profile.gif "[profile] [profile]")
To Paraphrase:"The Help you get is proportional to the Help you give.."
Not enough specifics to be precise, but for Oracle you would:
A: Create a PlSql Proc that will output a text file ( see the DBMS_OUTPUT and UTIL_FILE Docs)if there are any rows of data
B: Schedule that Proc using the DBMS_JOB package ( again, see the Docs)
C: Have the Event Server look for the file after the scheduled time for the Job run..
To Paraphrase:"The Help you get is proportional to the Help you give.."
Hi,
I forgot to add that you need to have some method to remove the file after the report has been run..
I would use a batch job scheduled to run after the Report.
Also, read the docs about using an event to trigger a run, especially this part:
To schedule an object with events, first ensure that you have created an event in the Events management area. When you schedule an object, select any Run option which includes the phrase, "with events."
To Paraphrase:"The Help you get is proportional to the Help you give.."
I forgot to add that you need to have some method to remove the file after the report has been run..
I would use a batch job scheduled to run after the Report.
Also, read the docs about using an event to trigger a run, especially this part:
To schedule an object with events, first ensure that you have created an event in the Events management area. When you schedule an object, select any Run option which includes the phrase, "with events."
To Paraphrase:"The Help you get is proportional to the Help you give.."
- Status
Similar threads
- Locked
- Question
- Locked
- Question