How to Create BI Report using static Excel file .xls

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:

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

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