Multi Data set Master Detail Report in Oracle Fusion Business Intelligence

 New --> Data Model --> Properties --> Data set --> Diagram --> Click on Plus icon

SQL Query

Name: Parent_query

Data Source: ApplicationDB_FSCM

Type of SQL: Standard SQL

SQL QUERY:

select rata.trx_number, rata.customer_trx_id 

  from ra_customer_trx_all rata

where rownum <=1

Press Button --> OK

Now we will view the data eithere query is returning any data or not.

Click on Tab --> Data --> View --> Table View (It should display only single record)

Now we will create new Data Model for child 

New --> Data Model --> Properties --> Data set --> Diagram --> Click on Plus icon

SQL Query

Name: Child_query

Data Source: ApplicationDB_FSCM

Type of SQL: Standard SQL

SQL QUERY:

select rctla.interface_line_attribute1, rctla.customer_trx_line_id, rctla.customer_trx_id,        rctla.line_number, rctla.inventory_item_id  from ra_customer_trx_lines_all rctla  

Press Button --> OK

Now drag and drop column customer_trx_id from master query over customer_trx_id column exists in child query. 

Now we let us view the data.

Click on Tab --> Data --> View --> Table View

Now we will change default display name to custom display name

Click on Tab --> Structure

Change display name as

TRX_NUMBER --> Transaction No.

CUSTOMER_TRX_ID --> Customer Transaction Id

INTERFACE_LINE_ATTRIBUTE1 --> SO

CUSTOMER_TRX_LINE_ID --> Customer Transaction Line Id

CUSTOMER_TRX_ID --> Customer Transaction Id

LINE_NUMBER --> Line Code

INVENTORY_ITEM_ID --> Inventory Item Code

Press Button --> Save

Name: Cusomter_Master_Detail_DM

Press Button --> OK

Click on Button --> Save as Sample Data

New Window will appear --> Press Button --> OK

Now we will create report

Click on Button --> Create Report --> New Dialog will display --> Press Button --> Cancel 

New Window will appear to confirm --> Press button  --> OK

Click on button Search to Get required Data Model --> Cusomter_Parent_child_DM

Now Press Button --> Generate RTF Template

Name: MyBusiness_Temp

Press Button --> Generate

Press Button --> Save

Name: Cusomter_Parent_child_Layout 

Press Button --> OK

Click on Button --> View Report

Now we will download the template and will edit as per required format

New window will appear --> Press hyper link --> Edit

It will download the template

Open the template in MS word and edit it as you required.

Now go back to template

Press Button Delete Template as we need to upload the altered RTF file

Catalog --> Cusomter_Parent_child_Layout --> Edit

New Window will appear --> Click on button --> Upload

New Dialog Box will appear and fill the below details

Layout Name: MyBusiness_Cust_Temp

Click on Button --> Choose File 

Type: RTF Template

Locale: English

Press Button --> Upload

Press Button --> Save

Now let us check the changes whether those changes impacted or not

Press Button -->  View Report.

That is it.