Report Bursting in Oracle Cloud

By Phaneendra Garlapati

Bursting enables you to deliver a single report to multiple destinations simultaneously. By taking advantage of this feature, you can create a single report, then send it in any format (for example PDF,HTML etc.) to multiple destinations (for example Email,Portal or Printer etc.).This feature also enables you to improve performance, since you fetch the data only once for different destinations.

Here are the steps to achieve Report bursting in Fusion.

Steps to create Data Model

->Navigator->More->Reports and Analytics

(OR)

->Home-> Tools-> Reports and Analytics


Select one Directory from My Folders-> New-> Data Model


New->SQL Query->

Name: For example, “PO_Detail_Report_SQL”

Data Source: Select the data source from LOV

Type of SQL: Standard SQL

SQL Query: Sample SQL Query (SQL Query should contain Delivery information)

->OK

Click on Save->Name(For example, “PO_Detail_Report_DM”)->Ok

Data Model->Bursting->New->

Name: For example, “Sample Bursting”

Type: SQL Query

Data Source: Select the data source from LOV

Split by: Select the element from the data set by which to split the data.

Deliver by: Select the element from the data set by which to format and deliverthe data.

Delivery Query:SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

Sample Bursting Query Format

SELECT po_number KEY,
‘BITemplate’ TEMPLATE,
‘en-us’ LOCALE,
‘PDF’ OUTPUT_FORMAT,
‘true’SAVE_OUTPUT,
‘America’TIMEZONE
‘ENGLISH_HIJRAH’CALENDAR,
‘EMAIL’ DEL_CHANNEL,
‘PO_Detail_Report’OUTPUT_NAME,
‘To Email Addressfor Example:’po_grp@trinamix.com’parameter1,
‘Cc’parameter2,
‘From’ parameter3,
‘Subject’ parameter4,
‘Body’ parameter5,
‘true’ parameter6,
‘Reply-To’parameter7,
‘Bcc’parameter8
FROM ( Paste SQL Query used for Data Model)

KEY:The Delivery key 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 (For Example: ‘BITemplate’)

LOCALE: The template locale (For Example: ‘en-us’)

OUTPUT_FORMAT: Output file format (For Example: ‘PDF’,’HTML’,’Excel’)

SAVE_OUTPUT: Indicates whether to save the output documents to BI Publisher history tables that the output can be viewed and downloaded from the Report Job History page.

TIMEZONE: The time zone to use for the report. Values must be in the Java format, for example: ‘America/Los_Angeles’. The system default time zone is used to generate the report if not provided.

CALENDAR: The calendar to use for the report. Valid values are:

  • GREGORIAN

  • ARABIC_HIJRAH

  • ENGLISH_HIJRAH

  • JAPANESE_IMPERIAL

  • THAI_BUDDHA

  • ROC_OFFICIAL (Taiwan)

If not provided, the value ‘GREGORIAN’ is used.

DEL_CHANNEL:

  • EMAIL

  • FAX

  • FILE

  • FTP

  • PRINT

  • WEBDAV

OUTPUT_NAME: The name to assign to the output file in the report job history.

Steps to create Layout

Design a Required Layout in Word Document with XML Tags and save it in RTF Format.


(OR)

Downloading XML data for report layout.

Click on Data tab in Data model->Export->Ok

Open Word Document->BI Publisher Tab->Sample XML->Choose .xml file from Local System->Open

All Fields->Save as ‘Sample_Name.rtf’

Attaching .rtf Layout to Data Model

Data->View->Table View->Create Report

Upload ->

Layout Name:Name (For example, “PO_Detail_Report_DM”)

Browse: Choose .rtf file from Local System

Type:Choose RTF Template from LOV

Locale: Choose from LOV.

Click on search for Data Model (Attaching Data Model to Layout)

Select the Data Model from Directory->OK

Click on Save->Choose Directory for Layout->Name for Layout->OK

Layout attached to Data Model.

Click on Properties->Check Enable Bursting


Launching Request for Running Report.

Right Click on Layout->Schedule

Submit Report Job Name(Any Custom name )->OK

Check Request status in Scheduled Processes

Bursting Program completed successfully with Succeeded status.

Below is sample email sent by Bursting Program with attached .pdf report

PO Line Data in pdf report


About the Author

Phaneendra Garlapati is a Technical consultantat Trinamix Inc. He has around 3 years of experience as a technical consultant and specialized in the areas of Oracle Cloud, Oracle Applications and, Demantra.


For more Information Contact Us