• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:June 16, 2020
  • Reading time:8 mins read
You are currently viewing Dynamic Output and Bursting for BI Report
Dynamic Output and Bursting for BI Report

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

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.

Demographic Report settings 1024x260 - Dynamic Output and Bursting for BI Report
Default Report settings

Default output when we run the report.

default output - Dynamic Output and Bursting for BI Report
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.

date expressions - Dynamic Output and Bursting for BI Report
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

schedule report - Dynamic Output and Bursting for BI Report
BI Report schedule

Go to Output tab, select the delivery option as FTP

dynamic filename - Dynamic Output and Bursting for BI Report
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.

Table of Contents

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..
'[email protected]' as PARAMETER1, -- to mail address
'[email protected]' as PARAMETER2, -- CC mail address
'[email protected]' 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
'[email protected]' 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.

  • In visible box by plugintheme