nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Is Multiple Data Sets Creation supported for BI Bursting

AshishERPCloud

Summary

Multiple Data Sets in BI Bursting

Content

This is with reference to Oracle Fusion HCM Applications

Say we want to have a report which would hold Person Details as one Data Set (Parent) and Email Address (Child Data Set).

We then need to burst the Data to a SFTP Folder

Can we do this , if so please help with a simple example

Thanks

Ashish

Find more posts tagged with

Reporting

Report Output

Report Delivery

Sample Reports

Accepted answers

All comments

Wade Wilson

Hi Ashish,

This appears to be doable. I dug up some sample code that one of my teammates used.

SELECT 'key' AS "KEY"
     , '<TemplateName>' AS TEMPLATE
     , 'en-US' AS LOCALE
     , 'text' AS OUTPUT_FORMAT
     , 'FTP' AS DEL_CHANNEL
     , '<SFTPServerName>' PARAMETER1
     , '</directory/location>' PARAMETER4
     , '<file.txt>' PARAMETER5
     , 'true' PARAMETER6
FROM dual

This link explains the parameters. Just scroll to the 'FTP and SFTP' section - http://docs.oracle.com/cd/E23943_01/bi.1111/e22258/add_burst_def.htm#BIPDM394

Hope this helps.

AshishERPCloud

Hi Wade,

Thanks for replying, but am looking for multiple data-sets.

The one you are referring to is the most commonly known and widely used calssic single data-set example which I am aware of am looking for a multi data-set example

Thanks

Ashish

Wade Wilson

Hi - The same should apply for multiple data sets. You can add a second bursting in the data model. You would use a different Split By/Deliver By for each bursting. We do this to email to HR Reps vs Workers' managers and they are in separate data sets.

Zanith

Hi Ashish,

Do u able to achieve the requirement of busting using multiple data sets?

Thanks,

Zanith.

AshishERPCloud

Hi Zanith,

I am unable to achieve this yet.

Kind Regards

Ashish

Priya_Ananda

Hi , please let me know how to proceed with the Bursting logic when we have multiple data sets.

Is there any workaround for this ?

Thanks,

Priya

RVohra

You'll need two data sets and create a link between these two data sets

Data Set 1: Person Details (Parent)
Data Set 2: Email Addresses (Child, linked via person_id or party_id)

Using the BI Publisher Plugin for Word or Excel:

Design a template with groupings (Group by Person).
Show person's details in the header.
Use a repeating group for the email addresses.

In the Data Model, click Bursting and define the bursting configuration.

Sample Query could be

SELECT
p.person_number AS KEY,
p.person_number || '_report.pdf' AS OUTPUT_NAME,
'PDF' AS TEMPLATE_FORMAT,
'en-US' AS TEMPLATE_LANGUAGE,
'Default' AS TEMPLATE_NAME,
'sftp' AS DELIVERY_TYPE,
NULL AS PARAMETER1, -- Not used for SFTP
NULL AS PARAMETER2,
NULL AS PARAMETER3,
NULL AS PARAMETER4,
NULL AS PARAMETER5
FROM
per_all_people_f p
WHERE
trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date;

Note: KEY must match the data grouping key in your report (e.g., person_number). TEMPLATE_NAME should match the name of your uploaded layout. DELIVERY_TYPE = 'sftp'