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.
