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.
Default output when we run the 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.
To create a file name that appends the day, month, and year, such as:
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
Go to Output tab, select the delivery option as 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 'norep[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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM