How to Pass Values from Embedded Oracle Analytics to Oracle APEX
Introduction
If you’ve embedded Oracle Analytics (OAC) into an Oracle APEX application, you may want to pass values from the embedded analytics content to your host application page. In this blog, I’ll describe how to do that.
Let’s assume a scenario where you’d like to perform some analysis of high school data (I’m using the Oracle APEX NY City High Schools demo data set and application). The video below illustrates the types of interactions that we can define between APEX and OAC.
One of my earlier blogs describes how to create the first interaction shown in the video, where I select values in an APEX multi-selector and use those values as filters within an embedded OAC canvas. This blog will focus on the second interaction in that video where I have performed a ‘top n / bottom n’ analysis in Oracle Analytics and then send that value to the APEX interactive report on the right hand side of the page.
Adding an Event Listener for Analytics Data Action
For the reasons described in this blog, mostly related to JET versions, using an iFrame to embed Oracle Analytics provides the most flexibility by sandboxing the embedded content. It is still possible, however, to communicate with the embedded Oracle Analytics canvas using the window postMessage method. While my previous blogs have mostly described use cases where I’m passing values into the iFrame, this blog will explain how to pass values out of the iFrame.
I will assume that an event data action has been defined on the canvas that is emebdded. More information about working with iFrames and events, including a description of the payloads and how to obtain values from those payloads can be found here, here and here.
This is sample code for adding an event listener within the iFrame where OAC is embedded and building a string variable that contains the value to be passed to the parent APEX application.
- It’s all within an iFrame using the srcdoc parameter so any double quotes “ have to be escaped to "
- You will replace values relevant to your instances and OAC workbooks that are highlighted <LIKE THIS>
- This code also contains event handlers for messages passed into the iFrame that include the name of a workbook and insight reference joined by a $ character
- The code contains console.log(); statements to illustrate the payloads being sent by the data actions and to help with troubleshooting
- This approach can also be used to obtain the values of filters that are applied to the analytics canvas — more details in this blog
<iframe id='OACiFrameEmbed' width="1024" height="800" srcdoc="
<!DOCTYPE HTML>
<html dir='ltr'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<!-- <meta name='viewport' content='width=device-width, initial-scale=1'> -->
<script src='https://<ORACLE ANALYTICS INSTANCE>.ocp.oraclecloud.com/public/dv/v1/embedding/standalone/embedding.js' type='application/javascript'>
</script>
</head>
<body>
<div style='width:1024px; height:800px' >
<oracle-dv id='Filter'
project-path='{{projectPath}}'
active-page='insight'
active-tab-id='{{activeTabid}}'
project-options='{"bDisableMobileLayout":true, "bShowFilterBar":true}'
filters='{{filters}}'>
</oracle-dv>
</div>
<script>
requirejs(['jquery','knockout', 'obitech-application/application','ojs/ojcore', 'ojs/ojknockout', 'ojs/ojcomposite', 'jet-composites/oracle-dv/loader'],
function($, ko, application) {
function model() {
var self = this;
self.projectPath = ko.observable('<PATH TO OAC PROJECT WITH EVENT DATA ACTION');
self.activeTabid = ko.observable('<ACTIVE TAB ID>');
self.filters = ko.observableArray([{
'sColFormula': "XSA('<USER>'.'APEX NYC HIGHSCHOOLS').\"NYC_HIGHSCHOOLS\".\"BOROUGH\"",
'sColName': 'BOROUGH',
'sOperator': 'in',
'isNumericCol': false,
'isDateCol': false,
'bIsDoubleColumn': false,
'aCodeValues': [],
'aDisplayValues': []
}]);
window.addEventListener('message', (event) => {
if ((Array.isArray(event.data)) && event.origin == '<DOMAIN OF APEX APP>') {
self.filters([{'sColFormula': "XSA('<USER>'.'APEX NYC HIGHSCHOOLS').\"NYC_HIGHSCHOOLS\".\"BOROUGH\"",
'sColName': 'BOROUGH',
'sOperator': 'in',
'isNumericCol': false,
'isDateCol': false,
'bIsDoubleColumn': false,
'aCodeValues': [],
'aDisplayValues': event.data}
]);
}
if ((typeof event.data === 'string' || event.data instanceof String) && event.origin == '<DOMAIN OF APEX APP>') {
self.projectPath(event.data.substr(0, event.data.indexOf("$")));
self.activeTabid(event.data.substr(event.data.indexOf("$")+1, event.data.length));
}
}, false);
}
application.setSecurityConfig('oauth_3legged');
ko.applyBindings(new model);
});
// Add code to deal with the event data action
var eventName = 'oracle.bitech.dataaction';
var element = document.getElementById('Filter');
if (element) {
var oEventListener = element.addEventListener(eventName, function (e) {
console.log('***** Payload from DV ***** ');
console.log('eventName = ' + e.detail.eventName);
console.log('payload = ' + JSON.stringify(e.detail.payload));
console.log('***** Payload from DV end ***** ');
var res = '';
Object.keys(e.detail.payload.context).forEach(function(key) {
console.log(key, e.detail.payload.context[key]);
var temp = e.detail.payload.context[key]['oValueMap'];
var temp1 = Object.keys(temp)[0];
res = res.concat(temp1);
});
window.parent.postMessage(res, '<DOMAIN OF APEX APP>');
}, true);
}
</script>
</body>
</html>">
</iframe>JavaScript at the APEX Page Level
At the APEX application page level (where OAC is embedded) we need to have an event listener and event handler function defined. In the following parameters, I have the following code:
Execute when page loads:
- This code also includes passing the value of the APEX multiselect (P14_SELECT_BOROUGH) to the vBouroughName that is then passed as a message to the iFrame with OAC embedded
- The event listener detects a message from the iFrame, when the event data action is invoked and then calls a function onMessageHandler
vBoroughName = apex.item("P14_SELECT_BOROUGH").getValue();
setTimeout(() => { document.getElementById('OACiFrameEmbed').contentWindow.postMessage(vBoroughName, 'https://<APEX INSTANCE>'); }, 2000);
window.addEventListener('message', onMessageHandler);Function and global variable declaration:
- Check the event type is a string
- Set the hidden APEX page item P14_EVENT_DATA to the value of the string from the event
- Submit the page
function onMessageHandler(event) {
if (typeof event.data === "string") {
var sBorough = event.data;
apex.item("P14_EVENT_DATA").setValue(sBorough,null,true);
apex.submit('submit');
}
}Note: make sure that the hidden APEX page items don’t have ‘Value Protected’ set to ‘on’ or you may see errors when running the applciation.
Query Used by APEX Region
This is the query used for the interactive report, that uses the value P14_EVENT_DATA, populated by the event handler function, based on the value passed from the embedded analytics when an event data action is invoked.
select BOROUGH,
SCHOOL_NAME,
TOTAL_STUDENTS,
GRADUATION_RATE,
ATTENDANCE_RATE,
SEATS,
APPLICANTS
from NYC_HIGHSCHOOLS h
where instr(':'||:P14_EVENT_DATA||':',':'||h.borough||':') > 0Passing Event Data to Alternative APEX Application Page
In the last part of the video demo, I link to another page in the APEX application using a button. This has an action ‘Redirect to Page in this Application’ and a target as shown below:
On page 17, I have an interactive report based on the following SQL, that provides a more detailed view of the borough sent from the analytics event data action:
select ID,
BOROUGH,
SCHOOL_NAME,
NEIGHBORHOOD,
INTEREST,
METHOD,
TOTAL_STUDENTS,
GRADUATION_RATE,
ATTENDANCE_RATE,
COLLEGE_CAREER_RATE,
SAFE,
SEATS,
APPLICANTS,
DBN,
LATITUDE,
LONGITUDE,
LANGUAGE_CLASSES,
ADVANCED_PLACEMENT_COURSES,
SCHOOL_SPORTS
from NYC_HIGHSCHOOLS h
where instr(':'||:P17_BOROUGH_FROM_EVENT||':',':'||h.borough||':') > 0Summary
This blog has described how to use the payload that is generated from an Oracle Analytics event data action and pass values from that payload to a host application, in this case, Oracle APEX, where the values can be used to filter any SQL statements that are used within APEX regions such as interactive reports.
