Introduction
Generally all letter format reports are prepared using a SQL Query Based BI Report. However, the same can even be accomplished using an OTBI Analysis too.
We would demonstrate the same in this article.
We would perform the following steps in order to generate such a report:
-
Create Data Model using ‘Oracle BI EE’ as Data Source
-
Create Bursting Query using ‘Oracle BI EE’ as Data Source
-
Create RTF Template
-
Upload RTF Template and Create a BI Report
-
Execute the Report
Worked Example
We would use a simple Analysis SQL as the Data Source. We would fetch the following columns in the report:
-
Employee Name
-
Person Number
-
Department
-
Employee Work Email
-
Manager Name
-
Manager Work Email
The SQL query used is mentioned below:
|
SQL Used As Data Source for Data Set |
|
SELECT "Workforce Management - Work Relationship Real Time"."Worker"."Employee Name" EMPNAME, "Workforce Management - Work Relationship Real Time"."Worker"."Person Number" PERSONNUMBER, "Workforce Management - Work Relationship Real Time"."Department"."Department Name" DEPARTMENT, "Workforce Management - Work Relationship Real Time"."Worker"."Employee Email Address" WORKEMAIL, "Workforce Management - Work Relationship Real Time"."Worker"."Manager Name" MANAGERNAME, "Workforce Management - Work Relationship Real Time"."Worker"."Manager E-Mail Address" MANAGEREMAIL FROM "Workforce Management - Work Relationship Real Time" WHERE ("Worker"."Employee Email Address" IN (This email address is being protected from spambots. You need JavaScript enabled to view it.', This email address is being protected from spambots. You need JavaScript enabled to view it.', This email address is being protected from spambots. You need JavaScript enabled to view it.')) |
Next we would also need to create a Bursting Query. We would use the ‘PERSONNUMBER’ data column for Split By and Deliver By value used for Bursting
|
Bursting Query |
|
SELECT "Workforce Management - Work Relationship Real Time"."Worker"."Person Number" KEY, 'JoiningLetterTemplate' TEMPLATE, 'en-us' LOCALE, 'PDF' OUTPUT_FORMAT, 'EMAIL' DEL_CHANNEL, "Workforce Management - Work Relationship Real Time"."Worker"."Employee Email Address" PARAMETER1, This email address is being protected from spambots. You need JavaScript enabled to view it.' PARAMETER2, This email address is being protected from spambots. You need JavaScript enabled to view it.' PARAMETER3, 'Dear Employee,
Congratulations!
We are delighted to onboard you in our esteemed organization.
Please refer the attachment which contains relevant details related to the same.
Kind Regards, HR Helpdesk Team' PARAMETER4, 'Joining Letter' PARAMETER5, 'true' PARAMETER6, This email address is being protected from spambots. You need JavaScript enabled to view it.' PARAMETER7 FROM "Workforce Management - Work Relationship Real Time" WHERE ("Worker"."Employee Email Address" IN (This email address is being protected from spambots. You need JavaScript enabled to view it.', This email address is being protected from spambots. You need JavaScript enabled to view it.', This email address is being protected from spambots. You need JavaScript enabled to view it.')) |
Also we would use the below RTF Template:
Report Execution
When we execute the Report we will receive an email with the Joining letter as an attachment
Details after we run the Report using Schedule Option
Email as Received in Inbox
Screenshot
Apps2Fusion Articles 