I need to run a set of reports, after the daily run of data import to our data warehouse, is complete.
We have a table in the data warehouse that holds all the status of all import jobs, and I want to trigger the execution of the WebI-reports when all these import jobs get status “complete”.
An example is where I have a SQL SELECT-statement that returns a row only when all the daily jobs are complete. When this select statement returns a row, all reports are run once, and only once per day.
You could use a file-based trigger to kick off report schedules. Create a stored procedure that creates a dummy file to use as a trigger when your SQL statement returns a row.
It is a bit complicated to implement this as we have restricted access to the file system on the BO-server. We then need to create a sort of file event that checks both for a file and at the same time checks that the report has not already run. This is possible, but I think it requires some batch or script programming to reset things, so that reports are run every day, but only once a day.
If you can create a file, you can create an event for it and schedule the reports once per day. For example, let’s say you expect the data load to be complete by 4:00am. You’d schedule the report to run daily at midnight. It will wait until 4:00am when the Event fires, and then run. Even if the Event fires multiple times during the day, the report won’t run again until after the next firing of the Event after it starts waiting for it at midnight of the next day.
Is there any way to schedule a WebI report to run so that it will produce a file if it finds data, and NOT produce a file if the query returns no rows?
If so, I could have a report with a select statement that returns a row only when all data load jobs are complete.