Friday, 8 August 2014

Skipping schedule in BI Publisher 11g


This is an awesome feature in BI Publisher 11g but I do not hear a lot of noise about it so let me create some.
I'm talking about Event triggers in BI Publisher 11g. Using event triggers, we can conditionally stop the delivery of a report. We will see the process of skipping schedules in this article.

Steps:
  1.   Create a data model for a BIP report.
    • a. Define a dataset
    • b. Define a Event trigger. Select Schedule as the Type. I have put the following query in my event trigger.
                       select 1 as some_column from dual where 1 = 1
  2.     Create a report on top of this data model.
  3.     Run the report and create a schedule as shown below
  4.  













  5. Define schedule trigger as shown below. Note that New Event Trigger 1 is the event trigger defined in my data model.
  6. Run the report immediately by selecting the Run Now button on the same schedule tab and submitting the job.
    Open the report again and check the job execution history as shown below.














    You will see a success status as shown below.








  7. Go to the data model again and change the query in the event trigger to the following
  8.             select 1 as some_column from dual where 1 =2
    Clearly, this query isn't going to return records. Hence we expect the schedule to be skipped.
     
    Set a schedule again, once the SQL of the Event trigger has been changed.  
    You will see that the schedule has been skipped as shown in the screenshot below.

No comments: