Tech Trainings

OTBI Report – As a Data Model to BI For Bursting and ESS Job

January 10, 2021 by admin

• Req: BI Report based on the OTBI data model to use bursting for sending email
• Design OTBI Report – Get the Query from Advance Tab
• Design BI Data Model based on the above query
• Also add Bursting Query
• Design BI Report & Template based on the above BI Data Model
• Create ESS Job , based on the above Report
• Run the ESS Job

OTBI Report

BI Data Model

BI Report and Template

ESS Job

ESS Job Output

---CODE---
DataModel Query : 
---------------

SELECT
'XX' Key,
   "General Ledger - Journals Real Time"."- Ledger"."Ledger Name" 
FROM "General Ledger - Journals Real Time"

--------------------------

Bursting Query : 

SELECT  'XX' AS "KEY"
        ,'XXGLOTBI_REP' TEMPLATE
        ,'en-US' LOCALE
        ,'PDF' OUTPUT_FORMAT
        ,'EMAIL' DEL_CHANNEL
        ,'XXGLOTBI_REP'||'_'||TO_CHAR(CAST(SYSDATE AS TIMESTAMP) AT TIME ZONE 'Australia/Brisbane','YYYYMMDDHH24MISS')||'.pdf' OUTPUT_NAME
        ,'sreeram.erptech@oratrainings.com' PARAMETER1  
		,'from@oracle.com' PARAMETER3
		,'Your BIP Report is Ready Subject' PARAMETER4
		,'Email Message Body : Dear Team, PFA Supplier Output Details report..' PARAMETER5
		,'true' PARAMETER6   --For PDF Attachments
FROM DUAL

Report Path : /Custom/OraTrainings/XXGLOTBI_REP.xdo
Template Name : XXGLOTBI_REP

Comments |0|