Monday, April 11, 2022

ERP - BI report using Excel template

Here, we will create a BI report based on a Excel template.

Highlevel steps:

  • Create a Data model based on SQL query.
  • Export the sample xml data from the created Data Model.
  • Create a .xls template and upload.
    • For element, use : XDO_?elementname?
    • For group, use: XDO_GROUP_?elementname?
  • Run the report to generate the output.
SQL query used:

Select hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,



from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=5

Steps in detail:

Step1: Create a Data model:

Select SQL Query

Provide SQL Query

From Data tab, view the data, export and save as sample.

Save the Data model.

Step2: Create a .xls file

Based on the exported xml file, choose the field names and mention them as below with the element syntax:


Select one element and go to Formulas >>Name Manager

Click New

Select Scope as Sheet1 >> ok

Similary do the same steps for all the element or column name.

Now select all the element and Formulas >> Name Manager 

Click New

Add the Group name as below syntax

Select scope as Sheet 1.

Now in sheet2, rename it as XDO_METADATA and copy the following in the sheet.

Create the header section by entering the following variable names in column A, one per row, starting with row 1:

• Version

• ARU-dbdrv

• Extractor Version

• Template Code

• Template Type

• Preprocess XSLT File

• Last Modified Date

• Last Modified By

• Skip a row and enter “Data Constraints” in column A of row 10.

• In the header region, for the variable “Template Type” enter the value: TYPE_EXCEL_TEMPLATE

Hide the XDO_METADATA sheet.

Save it with .xls extension

Step3: Upload the .xls template and run the report to get the output.

Note: if you have multiple groups in the data model, like G_2 under G_1, then we have to perform the following:

For example group 1 have PO headers and group 1 also have Group 2 which contains PO lines,

  • For group1  elements use: XDO_?ElementName?
  • For group2 elements use: XDO_?ElementNane?
  • Select all the headers and elements and create group element with XDO_GROUP_?group1Name?

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...