Introduction
I hope most of you must be aware of what a BIP Data Model is and what is meant by a BIP Report. In case some of you are not well versed with the same or would like to re-visit the same do feel free to refer this link here.
I guess it is a very common requirement where we want to send some email notification or report data output to specific business users based on specific schedule but the problem arises when these notification emailers are send even when there is no relevant data. Basically it causes a lot of issues to the end users whose life would be made simpler if we could ensure that they receive an email only when there is some data returned by the Report. This sort of functionality was easily achieved in E-Business Suite by making use of Event Triggers but the same had been troubling Fusion Cloud Consultants for quite some time now but times have changed and what we have been gifted as a part of BI Publisher 11g is ‘Schedule Trigger’
A Schedule Trigger is a Code Snippet which fires when a Report Job is scheduled to run. When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped. The schedule trigger that we associate with a report job can reside in any data model in the catalog. We do not need to create the schedule trigger in the data model of the report which we want to execute. We can therefore reuse schedule triggers across multiple report jobs.
In this article we will try to create a very simple Schedule Trigger and understand how the same works.
Creating a Schedule Trigger
In order to create a Schedule Trigger:
-
In the Data Model task pane, we need to click on Event Triggers under Properties
-
Next we need to click on the (+) icon and fill in the following details
-
Also for each trigger we do have other fields too which are on the lower UI Pane
Note: pSchedule is a Parameter which has a Static LOV attached to it.
Now we will save this Data Model as ‘ScheduleTrigger_dm’.
Using Schedule Trigger
Now we already have a Schedule Trigger Defined and we would proceed to the next step where we will try to use this schedule.
For this we will take an already existing BIP Report (we have a simple report developed named as ‘NoData Report).
Let us try to schedule the same.
We have two scenarios here.
Scenario 1
We will try to ensure that the schedule is a success. For this, we would pass a value of 1 to the Success(1)/ Skip(2) parameter of the ScheduleTrigger_dm
We need to fill the populate details on the Schedule Tab
Next, we click on ‘Submit’. Give a Report Job Name (say ‘SuccessSchedule’) and check the result.
Also, we may click on ‘SuccessSchedule’ under Report Job Name to get more details:
We can clearly notice that a PDF Output File has got generated as expected.
Scenario 2
For this run we would try to ensure that the Schedule is skipped. As such we will pass a value of 2 for the parameter such that the ( SQL query attached to the Data Model of the Schedule Trigger returns no data)
We would need to populate the following details on the ‘Schedule’ Tab
Next we click on ‘Submit’ , give a Report Job Name ( say ‘SkipSchedule’) and check the results
Also we may click on ‘SkipSchedule’ under Report Job Name to get more details
Inference / Conclusion
So we have seen how using a parameter and passing specific value 1, 2 we may have a successful and skipped schedule run respectively.
So this is how we may use Schedule Trigger while Scheduling a BIP Report and even control when / how to get a particular schedule skipped. We may even use more complex logic and control the behavior of the Scheduled BIP jobs but that would be left to the readers to try and explore.
Good Luck!
Comments
RSS feed for comments to this post