Here is an example of implementing Data Triggers (Event Triggers) using Data Templates in Oracle BI Publisher (XML Publisher).

As per Oracle documentation, “Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.”

Data trigger is implemented using the <dataTrigger> element in a data template. This element has two attributes: a name and a source. The source attribute points to a function where our PL/SQL code resides.

Note: It is important that the PL/SQL code called by data triggers should reside in a function and the function should be wrapped in a package. The function should return a boolean value (true for success and false for failure). I tried implementing data triggers using procedure and code that was not wrapped in a package – it did not work for me. So I am only using functions returning a boolean value for data triggers.

Also, location of the trigger indicate at what point the trigger fires. Placing the data trigger element before the <dataStructure> element will fire the code before the data query is executed – this will act like a “Before Report Trigger”. Placing the data trigger element after the <dataStructure> element will execute the code after the data query is executed – acting like a “After Report Trigger”.

In the following example I use the data template to populate a staging table using a “before report” trigger. Then execute a SQL on the staging table to fetch the report data and in the end execute an “after report” trigger to log an entry into a Log table.

For this example, I am using the customers table in the oracle demo database “oe” schema.

Step 1: Create a staging table and a log table. The staging table will be populated by the before trigger and I will make an entry in the log table using the after trigger.

DROP TABLE dataTemplateLog
/
DROP TABLE stageCustomers
/
CREATE TABLE dataTemplateLog (actionReport VARCHAR2(100), dtReport DATE)
/
CREATE TABLE stageCustomers AS SELECT * FROM customers WHERE 1 = 2
/

Step 2: Create a package containing PL/SQL code for the before and after data triggers.

CREATE OR REPLACE PACKAGE dataTemplateTriggers
AS
  FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN; -- Function for Before Report Trigger
  FUNCTION afterTriggerLogRun RETURN BOOLEAN; -- Function for After Report Trigger END;
/
CREATE OR REPLACE PACKAGE BODY dataTemplateTriggers AS
 FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN AS
 BEGIN
  DELETE FROM stageCustomers; -- Clear the staging table
  INSERT INTO stageCustomers
  SELECT * FROM customers; -- Insert data into the staging table for the report
  COMMIT;
  RETURN true;
 EXCEPTION
  WHEN OTHERS THEN
   RETURN false;
 END;
 FUNCTION afterTriggerLogRun RETURN BOOLEAN AS
 BEGIN  -- Create a Log entry after the report runs
  INSERT INTO dataTemplateLog (actionReport, dtReport) VALUES ('Data trigger executed.', sysdate);
  COMMIT;
  RETURN true;
 EXCEPTION
  WHEN OTHERS THEN
   RETURN false;
 END;
END;
/

Step 3: Create a data template for the Customer report

Create a report with Data Template code for the Data Triggers

Here is the data template code:

<dataTemplate name="CustomerReport" dataSourceRef="oe" defaultPackage="dataTemplateTriggers">
 <dataQuery>
  <sqlStatement name="CUSTOMER1">
   SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL, CREDIT_LIMIT FROM stageCustomers
  </sqlStatement>
 </dataQuery>
 <dataTrigger name="beforeReport" source="dataTemplateTriggers.beforeTriggerPopulateStage()"/>
 <dataStructure>
  <group name="G_CUSTOMER" source="CUSTOMER1">
   <element name="CUST_FIRST_NAME" value="CUST_FIRST_NAME"/>
   <element name="CUST_LAST_NAME" value="CUST_LAST_NAME"/>
   <element name="CUST_EMAIL" value="CUST_EMAIL"/>
   <element name="CREDIT_LIMIT" value="CREDIT_LIMIT"/>
  </group>
 </dataStructure>
 <dataTrigger name="afterReport" source="dataTemplateTriggers.afterTriggerLogRun()"/>
</dataTemplate>

Step 4: Execute the report. The data in the report is from the staging table populated by the Before Report Data Trigger.

View Report. Data is displayed from the staging table populated by Before Report Data Trigger

Step 5: Select information from the Log table. The latest entry was made by the After Report Data Trigger.

SQL> SELECT substr(actionreport, 1, 30), dtreport FROM datatemplatelog;
SUBSTR(ACTIONREPORT,1,30)      DTREPORT
------------------------------ ---------
Data trigger executed.         30-OCT-09

You can use this example and enhance it to match your requirements for Before and After report triggers in Oracle BI Publisher.

Here are the steps to implement a “Date” parameter type in BIP. I have also included some additional Java Date formats that can be used to format the dates.

1. Open (Edit) the report using date parameter in BIP. 
2. Click on the “Parameters” node. Click the New button in the toolbar to create parameter.
3. Use the Parameter Type of “Date” and set the remaining values for the Parameter (as displayed):

Define a Date Parameter (Calendar Widget) in Oracle BI Publisher

Following is a description of the important fields used with the “Date” parameter type:

Note: We can perform calculation on “Default Value”. Example: {$SYSDATE()-7$}

Also, I found the Date From and To fields very helpful. It allowed me to keep users within a pre-defined range of dates. Please note when using these two fields, specify the dates in the format defined in the “Date Format String” field. If you don’t use the exact same format, BI Publisher will not give you any errors. But at run-time, it will not display the date correctly.

Some additional Java Date formats that I got from a Java Book:

Finally, run your BI Publisher report and click on the calendar icon to invoke the Calendar Widget.

Here is a run time image of the calendar widget for the report date parameter