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.
Table of Contents
High Level Steps to do BI Publisher bursting in Fusion
- Create BIP Data model
- Create Report using the Data model
- Add Layout(s) to the BIP Report
- Create Bursting Query in Data model
- Enable Bursting at Report definition level
- Test by scheduling the Report
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
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
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
Save the report
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
Parameter List For FTP Delivery Channel
SQL Bursting Query
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.
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.
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.