• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:December 30, 2020
  • Reading time:8 mins read
You are currently viewing Skip Schedule using Schedule Triggers in BI Publisher
Skip Schedule using Schedule Triggers in BI Publisher

In this article we will look into the schedule trigger concept in BI Publisher and the use cases of it.

Table of Contents

What is Schedule Trigger?

Schedule trigger is a type of Event Trigger that we can create in the Data Model to conditionally execute an occurrence of job. It is a SQL code which runs before the schedule is triggered to see if the schedule needs to run or get skipped based on the results in the trigger SQL Query. There are other types of Event Triggers like Before Data and After Data using Packages but as we cannot create custom packages in fusion, we cannot use these types. Only some seeded reports might use Before Data and After Data type triggers.

What are the Use cases?

There are multiple use cases for creating schedule triggers. Some of them are listed below:

  • If there is a need to run the report only on month start and month end dates
  • If there is a need to run the report only if the current payroll has been processed
  • If there is a need to run the report only if the Data Model fetches at least 1 row of data (Birthday Reminders, Anniversary Reminders, New Hire Notifications, Termination Notifications, etc.)
  • If there is a need to generate Escalation emails if the tasks aren’t completed on time or reminders for tasks.

In all of the above cases if we don’t use schedule trigger then the output will return incorrect results. Like if there are no new hires on that particular day, then the report will get errored.. if there is any other master report with changes since last day, and if no rows are fetched, it will generate a blank output. In order to avoid the blank output and errored schedule schedule triggers come to our rescue.

How to define Schedule Triggers?

Schedule Triggers are defined at Data Model level. The schedule Trigger needs to be explicitly selected while scheduling the BI Report. While executing the schedule, first the trigger SQL will be evaluated to see if it returns any output, if yes then the actual report will run, else the schedule will get skipped instead of running to error.

Sample Trigger SQL Query for Last Day of the Month:

Navigate to the Data Model for the BI Report to which the schedule trigger needs to be added.

image 2 - Skip Schedule using Schedule Triggers in BI Publisher
Schedule Trigger

SQL Query to check if today is the last day of the month:

select 'true' from dual where trunc(sysdate) = trunc(last_day(sysdate))
-- We can select the any value in the select clause. If it doesn't return anything then the trigger will fail and the report will get skipped. If this sql returns any rows then the report will be executed.

SQL Query to run the report only on Weekdays:

select 1 from dual where UPPER(TO_CHAR(sysdate, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH')) NOT IN ('SAT','SUN')

We can also use the BI Report input parameters in the schedule trigger SQL Query if there is a need to derive logic based on that field.

Tip: If it’s a generic schedule trigger like month start and end date and many reports use the same logic, then the schedule trigger can be added to a single data model and the trigger can be selected while scheduling other reports as well. There is no restriction that the trigger needs to be present on the same report data model.

How to use Schedule Triggers?

Schedule Triggers will be used while scheduling the BI Reports on the schedule tab.

image 3 - Skip Schedule using Schedule Triggers in BI Publisher
Select the trigger and submit schedule

Select the Trigger from the dropdown. If the trigger is in another data model, then change the Data Model radio button and select the Data Model having the trigger.

image 5 - Skip Schedule using Schedule Triggers in BI Publisher
Trigger in another Data Model than the report that is running

Once the trigger is selected, submit the schedule.

As today isn’t the last day of the month, the report should get skipped.

image 4 - Skip Schedule using Schedule Triggers in BI Publisher
Report skipped as today isn’t last day of the month.

With this we complete the demonstration of Schedule Triggers in BI Publisher Report.

Note: The schedule trigger concept is applicable only to BI reports and doesn’t apply for HCM Extracts as we don’t have the option to select the schedule trigger. It needs to be handled in the Data Group filter criteria or by customizing the Global Reports Data Model.

Updated 12/30/2020:

Even after following all the above steps, if you get any error “The Trigger SQL failed to execute” or “Failed to execute event trigger” like below:

image 20 - Skip Schedule using Schedule Triggers in BI Publisher

Then the reason could be that there are some commented lines in the schedule query using /* comments */ or — comments in the Schedule SQL Query.

We need to remove the commented code from the SQL Query and the schedule triggers will work perfectly fine.