BI Publisher has a very power reporting feature called Burst Reporting. It allows one Oracle BI report to be automatically generated by the key field and delivered to users associated with the key value. A common use case is a project budget report that can be split into multiple prints by project number and sent to each project manager for review.
Burst Report always works with Task Scheduling engine to automatically distribute the reports. There are multiple distribution channels including email, fax, file, printer, FTP and WebDav. In this article, we will focus on step-by-step instruction for email distribution.
NOTE: While many Oracle Primavera applications use BI Publisher as the reporting engine, make sure that you have the correct licensing to use this option illustrated here.
To start with, we need to enable the bursting function for a report. In BI Publisher, we locate a report data model and click Edit to update the configuration.
At the left page frame, drill down to the last Properties “Bursting” and add a bursting configuration.
The configuration page consists of three essential elements:
Split By: This value should be unique in the Data Model used for report splitting.
Deliver By: This is how deliveries are spread out based upon the value of this key field.
SQL query: This gives BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.
The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:
<ROWSET>
<ROW>
<KEY></KEY>
<TEMPLATE></TEMPLATE>
<LOCALE></LOCALE>
<OUTPUT_FORMAT></OUTPUT_FORMAT>
<DEL_CHANNEL></DEL_CHANNEL>
<TIMEZONE></TIMEZONE>
<CALENDAR></CALENDAR>
<OUTPUT_NAME></OUTPUT_NAME>
<SAVE_OUTPUT></SAVE_OUTPUT>
<PARAMETER1></PARAMETER1>
<PARAMETER2></PARAMETER2>
<PARAMETER3></PARAMETER3>
<PARAMETER4></PARAMETER4>
<PARAMETER5></PARAMETER5>
<PARAMETER6></PARAMETER6>
<PARAMETER7></PARAMETER7>
<PARAMETER8></PARAMETER8>
<PARAMETER9></PARAMETER9>
<PARAMETER10></PARAMETER10>
</ROW>
</ROWSET>
Common elements are defined as follows:
- KEY is the Delivery key and must match the Deliver By element in the Burst setup page. For instance, if there are 12 distinct values in the Delivery Key field, 12 separate emails will be produced and distributed.
- TEMPLATE – is the name of the Layout to apply. (Note: this is the template name in BI Publisher, not the file name such as invoice.rtf).
- OUTPUT_FORMAT – is the output format. Valid values are: HTML, PDFZ, PDF, RTF, EXCEL, EXCEL2000, MHTML, PPT, PPTX, XSLFO, XML, CSV, ETEXT
- DEL_CHANNEL – is the delivery method. Valid values are: EMAIL, FAX, FILE, FTP, PRINT, WEBDAV
- OUTPUT_NAME – is the name of the output file in the email attachment.
- PARAMETER1 – is the Primary email recipient(s). Multiple email addresses are supported via comma separators. There is no restriction on the email addresses, as long as they are valid, no matter if the email domain is internal or external.
- PARAMETER2 – is the c.c email recipient(s). Multiple email addresses are supported via comma separators. Same to the above, there is no restriction on the email addresses.
- PARAMETER3 – is the sender’s email address.
Below is the example of the SQL script:
SELECT <Key Field> KEY,
<Report Template Name> TEMPLATE,
<Report Template Format, such as ‘RTF’> TEMPLATE_FORMAT,
<Locale, such as ‘en-US’> LOCALE,
‘PDF’ OUTPUT_FORMAT,
‘EMAIL’ DEL_CHANNEL,
<Report Output Name> OUTPUT_NAME,
‘<email addresses1, email address2,…>’ PARAMETER1,
‘<email addressesA, email addressB,…>’ PARAMETER2,
‘bipublisher@drmcnatty.com’ PARAMETER3, — Sender’s email address
‘Schedule Report For Project: ‘||ugenprojectnumber PARAMETER4, — Title of the email
‘If you receive this message, Scheduled Burst Report is working.’ PARAMETER5, — Message body
‘true’ PARAMETER6,
‘replyto@nowhere.com’ PARAMETER7 – reply to email address
FROM unifier_us_p
where ugenprojectnumber in (‘P-0011′,’P-0019′,’P-0024’)
order by ugenprojectnumber
For more detail, please refer to Oracle Help Center: https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm.
After completing the bursting configuration, go to the Report Properties to enable the Bursting setup, as shown below.
At this moment, the burst reporting setup is complete. The next job is to configure the automatic delivery for the report. Let’s navigate to the main page schedule a report job via New > Report Job. Firstly, we pick the bursting-enabled report as shown below.
Under the Output tab, check the checkbox “User Bursting Definition to Determine Output & Delivery Destination” to connect the burst reporting function to the schedule job.
Under the Schedule tab, we can define the job execution time via Frequency options.
- Selecting Once, we can run the report immediately or pick a specific time to run.
Or, we can schedule the periodical runs to distribute the report. The schedule setup includes Hourly/Minute, Daily, Weekly, Monthly, Annually or Specific Dates, which is very flexible to fit into the various business requirements.
After the schedule setup is complete, we may click Open > Report Jobs to verify whether the job is properly configured and in a queue.
To know the status of the job after the scheduled runtime, go to Open > Report Job History to check if the execution is successfully done. If yes, all email recipients(s) should have received the email(s) with the attached report(s) as well.
In conclusion, Bursting with scheduling setup is a convenient and scalable approach to enable auto distribution and delivery of the BI Publisher report. When the report is in production mode, it brings benefits to business automation by normalizing a reporting process. Furthermore, because the report recipients do not need to be BI licensed users, an organization does not need to pay an extra licensing fee to Oracle, which is a big plus for cost-effectiveness.