OTBI Report – As a Data Model to BI For Bursting and ESS Job
- 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
![](http://oratrainings.com/wp-content/uploads/2021/01/image-6.png)
![](http://oratrainings.com/wp-content/uploads/2021/01/image-7.png)
BI Data Model
![](http://oratrainings.com/wp-content/uploads/2021/01/image-8-1024x516.png)
![](http://oratrainings.com/wp-content/uploads/2021/01/image-9-1024x533.png)
BI Report and Template
![](http://oratrainings.com/wp-content/uploads/2021/01/image-10-1024x277.png)
ESS Job
![](http://oratrainings.com/wp-content/uploads/2021/01/image-11-1024x386.png)
ESS Job Output
![](http://oratrainings.com/wp-content/uploads/2021/01/image-12-1024x472.png)
---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