• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 20, 2023
  • Reading time:11 mins read
You are currently viewing How to Create BI Report using static Excel file .xls
How to Create BI Report using static Excel file .xls

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.

image 50 - How to Create BI Report using static Excel file .xls

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.

image 51 - How to Create BI Report using static Excel file .xls

This worksheet “Salary” has the third table BIP_EMP_SAL.

image 53 1024x592 - How to Create BI Report using static Excel file .xls

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.

image 52 - How to Create BI Report using static Excel file .xls

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.

image 47 - How to Create BI Report using static Excel file .xls

Provide a name to the Data Set and select the source as Local and upload the input file.

image 48 - How to Create BI Report using static Excel file .xls

Browse for the input file and click Upload.

image 49 - How to Create BI Report using static Excel file .xls

Once the file is uploaded, we need to map the sheet and table name to the data sets.

image 54 1024x497 - How to Create BI Report using static Excel file .xls

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.

image 55 - How to Create BI Report using static Excel file .xls
image 56 - How to Create BI Report using static Excel file .xls

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:

image 57 - How to Create BI Report using static Excel file .xls

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:

image 58 1024x280 - How to Create BI Report using static Excel file .xls

Now, we can go ahead and save the sample data and create a report out of it.

The output will look like:

image 59 - How to Create BI Report using static Excel file .xls

Use Cases

  • 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 Files

You do not have access to this VIP only Content. Please subscribe to VIP Plan to check this section.

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.