You are currently viewing How to generate CSV output from BI Reports/HCM Extracts?
How to generate CSV output from BI Reports/HCM Extracts?

In this article we will look into another interesting topic of generating CSV output from HCM Extracts/BI Reports.

I see lot of clients have requirement to generate the comma separated file with file extension as .csv and oracle doesn’t provide a robust solution for this problem. We will look into all possible ways to solve this problem both for HCM Extracts and BI Reports.

Table of Contents

Generating CSV file using Seeded CSV functionality for BI Report

Oracle has provided a seeded “Data(CSV)” delivery option for BI Report, however it generates the header with the column names and then the data accordingly. BIP has a limitation that when CSV output is requested, all the data in the underlying XML output is included in the CSV file. It also includes the input parameters in the output as an individual column and we do not have any control over the template. We cannot modify the template to hide the input parameters or modify the column headings.

Let’s look at how it works with a sample Data Model:

Query:

select
PRIMARY_EMAIL_ID
,PRIMARY_PHONE_ID
,MAILING_ADDRESS_ID
,PRIMARY_NID_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
from per_all_people_f 
where rownum <5

Sample Data Model Output:

image 71 1024x206 - How to generate CSV output from BI Reports/HCM Extracts?

Now, lets create a blank rtf template and select the output format as Data(CSV)

image 72 1024x247 - How to generate CSV output from BI Reports/HCM Extracts?

Now, let’s run the report and check the output:

image 73 - How to generate CSV output from BI Reports/HCM Extracts?

It is showing all fields in the output csv file in the same order as present on the data model as shown below:

image 74 - How to generate CSV output from BI Reports/HCM Extracts?

Now, let’s add an input parameter effective date and get data as of that input date and check how the output changes with it.

image 75 - How to generate CSV output from BI Reports/HCM Extracts?

Modified Query:

select
PRIMARY_EMAIL_ID
,PRIMARY_PHONE_ID
,MAILING_ADDRESS_ID
,PRIMARY_NID_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
from per_all_people_f 
where rownum <5
and :p_date between effective_start_date and effective_end_date

Here is how the DM output looks like:

image 76 - How to generate CSV output from BI Reports/HCM Extracts?

Now lets check the report output:

image 77 - How to generate CSV output from BI Reports/HCM Extracts?

We can see that the input parameter got added to the CSV file and there isn’t a way to change this behavior.

Even though we create an RTF template selecting only the fields that are required, once we change the output format to “Data (CSV)” it will override the template with the seeded behavior and present the output with this default behavior and there isn’t any workaround for it.

We can create an etext template to get the columns that we need and modify the header descriptions as well. However with this approach we cannot get the file extension as .csv.. It will generate output file as <output file>.txt only.

Generating CSV File from BI Report using FTP option

Getting the file extension of .CSV is possible with custom etext template ONLY when the report is scheduled to place the output on FTP server. In the FTP output options, we can provide the remote file name and it will override the filename and file extension as well.

image 78 1024x425 - How to generate CSV output from BI Reports/HCM Extracts?

In rest of all cases for Email/Bursting/Content Server/Manual run, we will not be able to get the csv file extension with custom template.

Generate CSV File in HCM Extract on FTP server

Similar to BI Reports, HCM Extracts will generate the output XML only and in backend BI Report is the one which actually generates the output file. As BIP has a limitation that when CSV output is requested, all the data in the underlying XML output is included in the CSV file. This will result in all the parameters (Payroll Action ID, Delivery Option Id, Flow Instance Id), as well as the HCM Extract input parameters being included in the CSV output.

We can use the same FTP approach to override the generated file name if FTP delivery option is being used.

Below is the setting that can be used on the extract delivery options for FTP option to override filename.

image 79 1024x663 - How to generate CSV output from BI Reports/HCM Extracts?

Generate CSV File in HCM Extract using XSL Template

This approach has been developed by Oracle CoE (Center of Excellence) team. It utilizes an XSL template to transform the generated XML to create a modified XML which will then be used to generate the CSV output file.

This approach works for all delivery modes like email/ftp/web content server, etc.

The only caveat with this XSL file approach is that the output CSV file should contain at least one column with constant value, else we will need to add one additional column at the end of file with constant value.

Here is the complete document by Oracle CoE team which lists the steps to achieve this functionality.

Generating CSV file in HCM Extracts on Web Content Server

Oracle has come up with a seeded CSV option starting somewhere in 19B/19C and it can be used only with the Web Content Server delivery mode. For other delivery modes, this solution doesn’t work.

We need to configure the delivery option on HCM Extracts like below:

image 80 1024x630 - How to generate CSV output from BI Reports/HCM Extracts?

Even here, if the extract contains multiple data groups and records, you will get unnecessary rows on the output as we do not have control over the template. The seeded CSV option utilizes the XML to generate the csv file and we do not any control if we want to modify the layout or heading. if the extract is straight forward with all columns in a single record, then this option works.

Hope you now know all possibilities of generating CSV file from BI Reports and HCM Extracts.