In this article we will look into how to create BI Report using excel files. Currently the BI Publisher supports .XLS files only.. It doesn’t support .xlsx files yet.
In our earlier post, we have seen how to create BI Report using CSV file. If you haven’t checked it yet, please check it out.
Table of Contents
Prepare Sample Excel File
First, we should have the data in an excel file. We can have different worksheets as well which can be mapped to different tables in Data Sets.
Let’s first look at the sample Excel file with multiple tables and worksheets.
The worksheet “Names” consists of two tables one with Name and the other with employee info. We need to define the table names for this data. The table needs to have the header as well with a description of the column.
This worksheet “Salary” has the third table BIP_EMP_SAL.
Select the range of cells along with header row and click on Name Manager and click New and define a table name for the range.. The table name should start with BIP_ else those tables will not get recognized by the BIP data model.
Finally the excel file should be saved with type as “Excel 97-2003 Workbook(*.xls)” as other excel formats won’t work in BI Publisher.
Supported Excel Files:
- Save Microsoft Excel files in the Excel 97-2003 Workbook (*.xls) format by Microsoft Excel
- The source Excel file can contain a single sheet or multiple sheets.
- Each worksheet can contain one or multiple tables. A table is a block of data that is located in the continuous rows and columns of a sheet.
- In each table, Oracle BI Publisher always considers the first row to be the heading row for the table.
- The first row under the heading row must not be empty and is used to determine the column type of the table. The data type of the data in the table may be number, text, or date/time.
- If multiple tables exist in a single worksheet, the tables must be identified with a name for BI Publisher to recognize each one
- If all tables in the Excel file are not named, only the data in the first table is recognized and fetched.
- When the data set is created, BI Publisher truncates all trailing zeros after the decimal point for numbers in all cases. To preserve the trailing zeros in your final report, you must apply a format mask in your template to display the zeroes.
Creating Data Model using Excel File
Now, let’s get into BI Publisher and try to import this excel and create a report out of it.
Create New > Data Model
Create New Data Set and select the “Microsoft Excel File” as source.
Provide a name to the Data Set and select the source as Local and upload the input file.
Browse for the input file and click Upload.
Once the file is uploaded, we need to map the sheet and table name to the data sets.
We can also add the data model parameter to the data set so that when we run the data model for a parameter, it will filter the excel data based on the parameter. The problem with using a parameter is like we can only select a single value for the parameter, it does not work if the parameter is setup to use multi-selection or select all functionality.
Like this, we need to add other data sets as well.
Now we can link the Emplid from all data sets so all three data sets fetch the info for the same employee.
This is how the data model looks like after this setup:
We can also add SQL based data sets and map them to these data sets if we need additional information from database.
When we run the data model for one employee, this is how the output will look like:
Now, we can go ahead and save the sample data and create a report out of it.
The output will look like:
- Data Transformation or manipulation
- Consolidation of data
- Sharing the output with multiple users based on a local file. Instead of sharing excel, we can share the report out of the local file.
Download FilesYou do not have access to this VIP only Content. Please subscribe to VIP Plan to check this section.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM