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,

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?




Note:

We can also write the element name in the name box and enter.




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...