This article is all about creating BIP Reports in Oracle Fusion thereby leveraging excel adapters in Oracle HCM reports and analytics.
In Oracle HCM cloud, SQL plays a vital role in extracting the data from the database and creating a report. In addition to this, there are some other features where it is highly favored for customers/stakeholders to create a report using the data in excel. Below is the process to create a BIP report in the Oracle HCM cloud, without using SQL.
Oracle BIP – Oracle Business Intelligence Publisher. Oracle BI publisher is a reporting tool, used to extract data and represent them in different formats such as PDF, CSV, Excel, XML, etc.,
Let’s take a case study to understand the Creation of a BI Publisher Report in Oracle Fusion.
Create a report, which should provide the respective consolidated data of an employee using the unordered data in the Excel file.
It’s time to create the BI Publisher report! Let’s dive deeper.
Download the required data in an excel file, which has to be transferred to the BIP report. The Excel file can contain multiple sheets, later which can be mapped to different tables in Data sets.
Fig 1: Data in the Excel
Below is an example.
Provide a name to the worksheet as per convenience. A worksheet can have multiple tables.
Provide a name starting with BIP to each table as per the below screenshot.
Fig 2: Naming Each table starting with “BIP
Fig 3: Second sheet with Salary information
Select the range of cells along with the header row, click on Name manager under Formulas, click on New and provide the name. The table name should start with BIP, if not those tables will not get recognized by the BIP data model.
Fig 4: Formula creation
Repeat step 2 for all the tables in Excel.
Save the Excel file as “Excel 97-2003 Workbook(*.xls)”, because other excel formats don’t support BI publishers.
- Step 3:
- Create a Data Model.
- Login into application
- Navigate to Tools > Reports and analytics
- Create a new data model.
- Select “Microsoft Excel” as the source
Fig 5: Uploading the Excel file into Data Set
Provide a name to the Data set, select the source as Local and upload the input file.
Fig 6: Step load the excel file
Choose the sheet name, and table name for creating the dataset.
Fig 7: Choosing the sheet and table name
Repeat step 3 – III and IV, step 4 and 5 for all the tables.
Link the person number from all the data sets that have been created.
Viewing the data will look similar to the below figure:
Fig 9: Output of Data Set
Create the report. Thus the final BI Publisher report will be as,
Fig 10: Output Report
From the above steps, a consolidated BIP report of an employee can be created using Excel data.
- Data transformation
- Data manipulation.
- Consolidation of scattered data
- Schedule/share the created report with stakeholders instead of sharing the excel file.
- Highly useful for customers since it does not require SQL knowledge.
Excel file data act as a database. So other sources are unable to use as a database.
Author: Subash Ravichandran, Oracle HCM Consultant