Dynamic Output and Bursting for BI Report
Dynamic Output and Bursting for BI Report

 1,584 total views

In this post we will look into how to achieve dynamic output filename for BI Reports using scheduling and bursting query.

Want to learn Fusion Technical tools? Checkout this article

In the previous article we have looked into how to achieve dynamic output file name in HCM Extract

Normally when we create a BI Report and run the report, by default the output will get generated with <Report Name>_<Template Name>.<Default File Extension> Ex: Demographic_Report_Test.pdf (Report Name is Demographic_Report and Template Name is Test and the default file extension is pdf).

We do not have much control over the output file name and file extension in the BI Report when we run the report manually.

We can achieve some cases when we use the schedule Report functionality.
Let’s see what we can achieve and what we cannot.

We will consider an existing report to look at different ways to get the dynamic name functionality.

Default Report settings for bi publisher fusion hcm
Default Report settings

Default output when we run the report.

Default output of BI Report in bi publisher fusion hcm
Default output of BI Report

We can schedule the output to FTP server to have a dynamic filename.

For FTP destination we can enter a date expression to have BI Publisher dynamically include a date expression in the file name. The date is set at runtime, using the server time zone.

Expressions that can be used in filename for FTP option in fusion hcm bursting bi publisher
Expressions that can be used in filename for FTP option

To create a file name that appends the day, month, and year, such as:
myfile_01_11_2010.pdf
Enter the following:
myfile_%d_%m_%y.pdf in the Remote File Name field.

If your file name includes an undefined expression, such as
my_file_%a%b%c.pdf, the file will be created as named:
“my_file_%a%b%c.pdf” as %a, %b and %c are not defined expressions that you can use.

Navigate to BI Report > Click More > Schedule

BI Report schedule in bi publisher fusion hcm bursting bi publisher
BI Report schedule

Go to Output tab, select the delivery option as FTP

Dynamic filename for FTP in fusion hcm bi reporting bursting bi publisher
Dynamic filename for FTP

We can specify the dynamic file name in the Remote File Name section and we can even modify the output file extension if there is a need. Normally if we use etext template we get the output in .txt format and many of the clients request for the file name to be changed to .csv, we can achieve it here only with the FTP option by modifying the file extension in the Remote File Name.

For other delivery options like Email, Content Server we cannot achieve the dynamic file name using scheduling option.

Other option would be to write bursting SQL and use dummy key in the data model and bursting SQL to get it delivered to Email/FTP with dynamic filename. We will use the ‘Dummy’ text as key as we will have to send the output only once and not for each employee in which case we need to burst by person_id and for each person_id the bursting would happen and multiple output files will be generated.

Sample Bursting SQL for Email Delivery Option:

select
'Dummy' as "KEY", --- must be same as deliver by
'Report_Template' as "TEMPLATE", -- report template name
'RTF' as TEMPLATE_FORMAT, -- template format
'PDF' as OUTPUT_FORMAT, -- output format pdf rtf text etc..
,'Output_' || to_char(sysdate,'YYYYMMDD') OUTPUT_NAME, -- Output File Name
'EMAIL' as DEL_CHANNEL, -- delivery channel name email,fax,print etc..
'test@abcd.com' as PARAMETER1, -- to mail address
'test2@abcd.com' as PARAMETER2, -- CC mail address
'noreply@oracle.com' as PARAMETER3, -- from mail address
'Test email with dynamic filename' as PARAMETER4, -- subject
'FYI' as PARAMETER5, -- message body
'true' as PARAMETER6 -- attachment true/false value
'noreply@oracle.com' as PARAMETER7, -- reply-to mail address
FROM
dual

Sample Bursting SQL for FTP Delivery Option:

SELECT 'Dummy' AS "KEY" --- must be same as deliver by
,'Report_Template' TEMPLATE -- report template name
,'TEXT' TEMPLATE_FORMAT -- template format
,'en-US' LOCALE -- Language
,'TEXT' OUTPUT_FORMAT -- output format pdf rtf text etc..
,'FTP' DEL_CHANNEL -- delivery channel name email,fax,print etc..
,'Output' OUTPUT_NAME -- Output File Name
,'TEST' PARAMETER1 -- servername
,'abcd' PARAMETER2 -- username (not mandatory)
,'efgh' PARAMETER3 -- password (not mandatory)
,'/outbound' PARAMETER4 -- report path
,'Output_' || to_char(sysdate,'YYYYMMDD' || '.txt' PARAMETER5 -- remote file name
,'true' PARAMETER6 -- secure ftp
FROM DUAL

Sample Bursting SQL for WCC Delivery Option:

Using the below query in the bursting sql will upload the output to Web Content Server (UCM).

SELECT
'Dummy' as KEY -- Split Key from data model SQL
,'Report_Template' template -- Report template name
,'en-US' locale
,'TEXT' output_format -- Output type
,'WCC' del_channel -- To send to content server
,'FA_UCM_PROVISIONED' parameter1 -- server name (BI Administration > Manage BI Publisher > Delivery > Content Server)
,'FAFusionImportExport' parameter2 -- security group name
,'hcm.user' parameter3 -- Author of the file
,'hcm$/dataloader$/import$' parameter4 -- Account (Optional)
,'Output File'||TO_CHAR(SYSDATE,'YYYY/MM/DD') parameter5 -- Title
,'Output_'|| to_char(SYSDATE,'YYYYMMDD') parameter6 -- Output file name
,'' parameter7 -- Comments (Optional)
,'' parameter8 -- Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one
,'false' parameter9 -- Custom metadata (true/false). Specify ‘false’
,'' parameter10 -- Folder Path
FROM dual

Note: With bursting queries we can achieve dynamic file name with date for Email, FTP and WCC, however we can override the file extension only with FTP and WCC options and not with Email.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 2 Comments

  1. Talal Mohamed Ibrahim

    Very useful thank you

  2. Gopi

    Good article … thanks Bro

Leave a Reply