Using Dummy Bursting Query in BI Report to achieve dynamic filename
Using Dummy Bursting Query in BI Report to achieve dynamic filename

 284 total views

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.

bursting query

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.

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.

Leave a Reply