In this article we will look into getting the dynamic filename generated for a BI Report using bursting query.
By default the BI Report generates output with the BI Report and Template Name and there isn’t any simple setting to override the filename.
The only possible way to override the filename is to use bursting query and create a dynamic filename, however we have to schedule the report in order for the bursting query to take effect.
Even though we don’t require the output to be sent over to ftp/wcc/email, we still need to use the bursting query to generate dynamic filename by using dummy query which just renames the output filename.
We will add a dummy key in the data model sql and use the below dummy bursting query to get the dynamic filename and add it to the bursting section of the Data Model.
Table of Contents
Sample bursting query:
SELECT 'DUMMY' AS KEY,
'Audit Report' AS TEMPLATE,
'EXCEL' AS OUTPUT_FORMAT,
'Output File_' || to_char(sysdate, 'YYYYMMDD') AS output_name
FROM dual
You can tweak this query as per your requirement by setting the Key, Template, Output Format and Output Name.
Use Cases:
When we add the BI Report to run using Payroll Flow (Run BI Publisher Report), we would need to use the above dummy bursting query to get the output file name dynamically as the payroll flow run will schedule the BI Report and hence the bursting query would run on schedule.
If you have any other ideas let me know in the comments section.
hi Team,
We have used similar technique to achieve dynamic file name which includes parameters and flow instance name in the file name. Our requirement is to send Periodic Payroll Register Report data xml file for third party integration. Copied globalreports data model, added bursting definition to achieve the required file name. Created a report and attached this data model. Report is added as FTP delivery option. The issue we are currently facing is, few files are delivered to FTP with file name extension (.xml), few are delivered without file extension.
ADP_SC_EVUC_Historical_Payroll_20240501_20240528_300000028084080_3EB4GZCN34FF
ADP_SC_EVUC_Historical_Payroll_20240501_20240528_300000028083970_LR5NEKVJ7C2S.xml
We always need file with extension. Any leads what could be wrong?
Below is the query:
SELECT
:payrollActionId KEY ,
del_opt.bip_template_name TEMPLATE ,
del_opt.output_type OUTPUT_FORMAT ,
‘ADP_SC_EVUC_Historical_Payroll_’
||TO_CHAR(pact.start_date,’YYYYMMDD’)
||’_’
||TO_CHAR(pact.effective_date,’YYYYMMDD’)
||’_’
||(REPLACE(SUBSTR( pact.legislative_parameters,INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) , INSTR(pact.legislative_parameters,’|’,INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) ) -INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) ),’TAX_REPORTING_UNIT=’,”))
||’_’
||
(
SELECT
fi.instance_name
FROM
fusion.pay_requests r ,
fusion.pay_flow_instances fi
WHERE
r.flow_instance_id = fi.flow_instance_id
AND pact.pay_request_id = r.pay_request_id)||’.xml’ OUTPUT_NAME ,
del_opt.calendar_code CALENDAR ,
‘true’ SAVE_OUTPUT ,
pay_report_delivery.get_del_channel(del_opt.delivery_type,del_opt.ext_delivery_option_id,pact.payroll_action_id) DEL_CHANNEL ,
pay_report_delivery.get_del_param_value(‘1′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER1 ,
pay_report_delivery.get_del_param_value(‘2′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER2 ,
pay_report_delivery.get_del_param_value(‘3′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER3 ,
pay_report_delivery.get_del_param_value(‘4′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER4 ,
‘ADP_SC_EVUC_Historical_Payroll_’
||TO_CHAR(pact.start_date,’YYYYMMDD’)
||’_’
||TO_CHAR(pact.effective_date,’YYYYMMDD’)
||’_’
||(REPLACE(SUBSTR( pact.legislative_parameters,INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) , INSTR(pact.legislative_parameters,’|’,INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) ) -INSTR(pact.legislative_parameters,’TAX_REPORTING_UNIT=’,1,1) ),’TAX_REPORTING_UNIT=’,”))
||’_’
||
(
SELECT
fi.instance_name
FROM
fusion.pay_requests r ,
fusion.pay_flow_instances fi
WHERE
r.flow_instance_id = fi.flow_instance_id
AND pact.pay_request_id = r.pay_request_id)||’.xml’ PARAMETER5 ,
pay_report_delivery.get_del_param_value(‘6′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER6 ,
pay_report_delivery.get_del_param_value(‘7′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER7 ,
pay_report_delivery.get_del_param_value(‘8′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER8 ,
pay_report_delivery.get_del_param_value(‘9′,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER9 ,
pay_report_delivery.get_del_param_value(’10’,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) PARAMETER10 ,
pay_report_delivery.get_del_param_value(‘LOCALE’,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) LOCALE ,
pay_report_delivery.get_del_param_value(‘TIMEZONE’,del_opt.ext_delivery_option_id,del_opt.delivery_type,NULL,’PPA’,pact.payroll_action_id) TIMEZONE
FROM
pay_payroll_actions pact,
per_ext_delivery_options_b del_opt
WHERE
pact.payroll_action_id =NVL(:archiveActionId,:payrollActionId)
AND del_opt.ext_delivery_option_id=:deliveryOptionId
Hi Veena,
ideally, it shouldn’t happen that way. It should append .xml at the end of the file every time as per the bursting code.
Why don’t you hardcode the complete FTP bursting code in the bursting SQL and try it?
Thanks,
Sricharan
hi Sricharan,
What does it mean by hardcode bursting code? We need to include flow instance name and parameters. And the flow is submitted using flow instance REST end point. We submit one a flow for each TRU and for each month. Assume a client has 3 TRUs, then for last 36 months we submit 3*36 times the Periodic payroll register report one after the other. I am just wondering if a flow instance is incompatible to another, but it should not be the case as far as i know as these are only reports.
Thanks,
Veena