BI Publisher bursting in Fusion

Bi Publisher Bursting is a feature of Oracle Business intelligence publisher which allows you to send the output of the BIP report to different channels like FTP / EMAIL / UCM etc.

The same BI Engine is present in Oracle fusion applications and most of the features of BI Publisher can be used.

The common use cases when you want to use the bursting feature of BI Publisher is :

  • Send output to an email(s)
  • Send output with different layouts conditionally to different emails / channels.
  • Send output to FTP location.

High Level Steps to do BI Publisher bursting in Fusion

How to do BI Publisher bursting in Fusion !

  1. Create BIP Data model

    Use SQL Query to do this

  2. Create Report using the Data model

    Create Report from the Data model you designed

  3. Add Layout(s) to the BIP Report

    One or multiple layouts can be added. Use layout name not the rtf file name.

  4. Create Bursting Query in Data model

    Add bursting SQL query to the Data model. Use appropriate Split by and deliver by condition

  5. Enable Bursting at Report definition level

    Set the report to use the bursting definition by checking the checkbox in report properties

  6. Test by scheduling the Report

    Run the report and test the output.

Lets See the Steps in Detail with an example

Let’s suppose we have 2 Layouts (Layout A and Layout B) for a BIP Report and the requirement is to send Layout A to Email 1 and Layout B to Email 2 along with their associated data.

Write a SQL based data model

image 1 BI Publisher bursting 2
SELECT
   arc.cash_receipt_id,
   arc.pay_from_customer,
   arc.status,
   arc.type,
   arc.receipt_number,
   arc.receipt_date,
   rac.trx_number,
   ara.amount_applied,
   ara.org_id,
   decode(sign(arc.cash_receipt_id - 30000), - 1, 'info@wpsbutton.com', 'info.wpsbutton@gmail.com') EMAIL 
FROM
   ar_cash_receipts_all arc,
   ar_receivable_applications_all ara,
   ra_customer_trx_all rac 
WHERE
   ara.cash_receipt_id = arc.cash_receipt_id 
   AND ara.applied_customer_trx_id = rac.customer_trx_id 
   and arc.org_id = rac.org_id 
   and arc.pay_from_customer = 300000047394603 
   and ara.org_id = 300000046987012 
   and ara.amount_applied > 1064340
  • Data model Output
image 3 BI Publisher bursting 3

We will split this data by email address and the condition is if the cash receipt id is > 30000 the email should go to info@wpsbutton.com else it should go to info.wpsbutton@gmail.com.

Once the data model is ready, test the data and create a report

Assign two layouts templates in our case it is named BurstingDemo_Layout and BurstingDemo_Layout2

image 10 BI Publisher bursting 4

Save the report

image 11 BI Publisher bursting 5

Create a Bursting Query and use the data model query in its from clause.

The bursting query has a specific SQL template that lists the parameters for delivery types .

The important values of  delivery XML are:

  • KEY — The Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data.

  • TEMPLATE — The name of the Layout to apply. Note that the value is the Layout name (for example, ‘Customer Invoice’), not the template file name (for example, invoice.rtf).

  • LOCALE — The template locale, for example, ‘en-US’.

  • OUTPUT_FORMAT — The output format. 

Parameter List For Delivery Channel : Email

image 5 BI Publisher bursting 6

Parameter List For FTP Delivery Channel

image 6 BI Publisher bursting 7
image 4 BI Publisher bursting 8

SQL Bursting Query

bi-publisher-bursting-in-fusion
SELECT distinct
   EMAIL KEY,   -- Split by
   IDCLAYOUT TEMPLATE,    -- Catalog
   :xdo_user_report_locale LOCALE,   -- English (es)
   'PDF' OUTPUT_FORMAT,   -- PDF,HTML,CSV,RTF,EXCEL
   'EMAIL' DEL_CHANNEL,   -- EMAIL/FAX/FTP/FILE/SFTP/PRINT/WEBDAV
   'Sales Report',   -- Output Name
   EMAIL parameter1,   -- To
   '' parameter2,  -- cc
   'noreply@oracle.com' parameter3,   -- From
   EMAIL || ' Sales Report With Template ' || IDCLAYOUT parameter4,
-- Subject
   'Please See Report Attached' parameter5,   -- Body
   'true' parameter6,   -- Attachment
   'noreply@oracle.com' parameter7,   -- Reply to
   '' parameter8 	-- Bcc
From
   (
      SELECT
         arc.cash_receipt_id,
         arc.pay_from_customer,
         arc.status,
         arc.type,
         arc.receipt_number,
         arc.receipt_date,
         rac.trx_number,
         ara.amount_applied,
         ara.org_id,
         decode(sign(arc.cash_receipt_id - 30000), - 1, 'info@wpsbutton.com', 'info.wpsbutton@gmail.com') EMAIL,
         decode(sign(arc.cash_receipt_id - 30000), - 1, 'BurstingDemo_Layout2', 'BurstingDemo_Layout') IDCLAYOUT 
      FROM
         ar_cash_receipts_all arc,
         ar_receivable_applications_all ara,
         ra_customer_trx_all rac 
      WHERE
         ara.cash_receipt_id = arc.cash_receipt_id 
         AND ara.applied_customer_trx_id = rac.customer_trx_id 
         and arc.org_id = rac.org_id 
         and arc.pay_from_customer = 300000047394603 
         and ara.org_id = 300000046987012 
         and ara.amount_applied > 1064340 
   )

Enable the Report to use Bursting and select the bursting name from the dropdown.

image 9 BI Publisher bursting 9

Result Output after running the report

We receive the conditional report at both the email addresses with different layouts and a different set of data.

bi-publisher-bursting
image 13 BI Publisher bursting 10

References Adding Bursting in Oracle Business Intelligence Publisher Guide

Also read How to insert image in BIP Report

more fusion apps tech posts

FAQs

What is the use of Bursting in BIP?

The most common use is to send report output to an email or list of emails

What are the output options?

Output can be sent to EMAIL/FAX/FTP/FILE/SFTP/PRINT/WEBDAV

Can conditional Layouts be attached to outputs during delivery?

Yes. The above example covers this case.

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Enable registration in settings - general
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart