Skip to main content

Do you know how to create Data Model and Report from Excel Input file in Oracle Fusion?

In this blog, we are going to see how to create Fusion BI Data Model using Excel File as a Data Source

First and foremost, Oracle supports only .xls (not .xlsx) files in BI Data Model as of now. So use only .xls files.
Format the data to be usable in BI Data Model
The data should have a header for all the columns as shown below
We can also have multiple tables (of data) in a single excel file as shown below
When we use multiple tables (of data), define each table with a distinct name.
Select the table (including header and data) and go to "Formulas" tab and then click "Define Name". The Name should start with BIP*
Once the excel file is ready upload it to the BI Data Model
Give a Name to the Data Set, select "Local" as Data Source and Click Upload Icon as shown below
Once uploaded Select the "Sheet Name" and "Table Name" and click "OK". I have created 4 tables BIP_Name, BIP_Position, BIP_Grade and BIP_Departmnet in the excel that were listed in the "Table Name"
Following the same procedure I have created Data Sets for all the tables "Name", "Position","Grade" and "Department"
The table view of the data looks like below. Save the Data Model and Click "Save As Sample Data" button and Click "Create Report" button
Select "Use Report Editor" and click "Finish"
Select "Blank" Layout (Portrait or Landscape) based on your need
Select "Insert" tab and click on "Data Table"
Select "Table" tab and click on "Show" and select 1st option. If you need the "Total" metrics then select 2nd option
Drag and drop all the required Columns and save the template
Click on "View a List" and select the "Default Format" based on your need. I have selected "PDF" as Default Format and saved the Report
Open the Report to see the result as "PDF"
I hope this explains how to create Fusion BI Data Model using Excel File as a Data Source. If you have any questions, please post it in the comments section.
Labels:

Comments

  1. JP3 March 2019 at 09:17

    How to create cloud report without column header with received inbound files..

    ReplyDelete
    Replies
    1. Hassan3 March 2019 at 21:00

      If your excel file does not have a header, then BI Data Model considers first row as the header.

      Delete
    2. Hassan3 March 2019 at 23:03

      You can use CSV as Data Set Source to create report without column header. Refer to this blog
      https://elearningever.blogspot.com/2019/03/oracle-cloud-create-data-model-and.html

      Delete
  2. Unknown22 September 2021 at 04:55

    How to fetch one column's value from Excel data set, only when that column is null in SQL Query data set?

    ReplyDelete

Post a Comment

Popular posts from this blog

Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id   -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSy...
3 comments
Read more

Do you know how to generate output using eText Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’ ...
20 comments
Read more

Do you know how to generate output using Excel Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using Excel Templates in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Excel file and save it as type "Excel 97-2003 Workbook" (.xls) I have created 3 tabs "Summary", "Detail" and "XDO_METADATA" XDO_METADATA tab is mandatory. Place the below text in XDO_METADATA tab Version ARU-dbdrv Extractor Version Template Code Template Type TYPE_EXCEL_TEMPLATE Preprocess XSLT File Last Modified Date Last Modified By Data Constraints: In the "Detail" tab create Header row as shown below Now add the XDO tags Select the blank ...
2 comments
Read more