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,

hp.email_address,

hp.country,

hp.city,

hp.status

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:

XDO_?ElementName?

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

XDO_GROUP_?groupname? 

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?