Report scheduling to run the only during business hours i.e. report must trigger every 10 minutes between 7:30 AM and 5:30 PM on weekdays. Now if you have scheduled a report in Oracle Fusion, you would know that one can very easily schedule the report to run periodically and you can define for how many days the report must trigger. But if report is scheduled like this, it will run during non business hours too, something that client did not want.
So here is a simple way to restrict the report to run only during the specified hours. Just go to the data model of your report and click on edit. Go to “Event Triggers”. Click on “+” sign. Give it a name and Type as “Schedule”. In SQL query section, choose the data source as appropriate. I used the below query:
SELECT NEW_TIME(SYSDATE, ‘GMT’,’HST’)
FROM dual
WHERE to_char(NEW_TIME(SYSDATE, ‘GMT’,’HST’), ‘DAY’) IN (‘1′,’2′,’3′,’4′,’5’)
AND to_char(NEW_TIME(SYSDATE, ‘GMT’,’HST’),’HH24MI’) >= ‘0730’
AND to_char(NEW_TIME(SYSDATE, ‘GMT’,’HST’),’HH24MI’)<=’1730′
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Since my client’s timezone is HST and cloud stores the time in GMT, I am converting the time to HST. Once the report is scheduled to run based on the trigger, report will trigger only when the trigger query returns the output. As obvious, above query will return the value only if the current time is between 7:30 AM and 5:30 PM on first 5 days of the week.
Now go to your report and click on schedule. Provide the parameters as you deem fit. Go to schedule tab. Choose the Frequency as Hourly/ Minute. Check the radio button against Minute and give the value as 10 ( remember that I needed to run the report every 10 min during business hours?).
Then check the “Use Trigger” checkbox. Click on the Trigger Dropdown. And Choose the trigger you created above. Click on submit, give the job a name and you are all set.
Related Posts
Fusion Report Job Parameters
Report Security Query – Data Access On Specific Entities
Report Security
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com