Embedding Oracle Analytics into Oracle APEX Task Approvals

Photo by fabio on Unsplash

Oracle APEX includes comprehensive functionality for definition of workflows and tasks — critical operations for any type of business since these are the places where decisions are made. The addition of analytics based insights at that point in a workflow can be of great benefit, providing essential context and other relevant details.

This blog will describe a method to embed Oracle Analytics content within an Oracle APEX expense tracker application, based on a LiveLab so you can try it yourself.

I’ve blogged previously on the various methods for embedding Oracle Analytics into APEX and the complimentary nature of these products. It’s not a case of using either/or, but building analytic applications using the combination of the feature sets offered by both products is unbeatable. In addition, my goal is always to embed Analytics in an interactive manner — i.e., if the embedded analytics is, for example, filtered by values chosen by the user using APEX selectors or faceted searches then that provides additional value over a more ‘static’ embed.

Task Related Insights

Here is a look at the user experience we’re going to build, a user JOHN has submitted an expense for a conference pass. This expense will be reviewed by CLARA. On choosing the line item, the task details are shown, along with an embedded analytics canvas that is automatically filtered on the same expense type as that in the claim. The canvas displays the average, minimum and maximum values for this expense type, immediately providing CLARA with insight on how JOHN’s expense compares and in this case, CLARA approves the expense in the knowledge that the amount claimed by JOHN is reasonable.

APEX Expense Application Approval Workflow with Analytic Insights

Use the Same Data in APEX and Analytics

The data underpinning the APEX application is stored in tables in the APEX schema. This makes it easy to access the same data in Oracle Analytics by creating a connection to the Oracle Autonomous DB in Oracle Analytics and then mapping in the tables as data sets. The data preparation capabilities of Oracle Analytics come in useful in this particular case since the EMPLOYEE_DETAILS table has a column EMPNO of type number and the table EMP_EXPENSE_REQUESTS has a column EMP_NO of type varchar2. Using the data preparation functionality, we can create a new column in the data set based on EMPLOYEE_DETAILS that has the same name and type so we can join on this column to obtain the employee name when creating analytics canvases.

Creating an EMP_NO column in an analytics data set to facilitate joins with EMP_EXPENSE_REQUESTS

Create the Analytics Canvas

Once you have the data sets created in Oracle Analytics, you can create a workbook that provides some insights on the expenses data. In the use case for task approval, I’ve created a workbook that shows the AVG, MIN and MAX values for expense types.

AVG, MIN and MAX values for Expense Type

In order to automatically filter on the expense type that the expense claim contains, we can use a workbook parameter and then pass the value of the expense type from the APEX task as a parameter value. Here is a video where I explain how to use parameters with embedded analytics:

Adding the Analytics content to the APEX app

The APEX application page to which we’re going to add the analytics content is called ‘Task Details’ and is Page 2 in my version of the completed expense application Live Lab.

The first thing I’ve added is a hidden page item called ‘P2_EXPENSE_TYPE_FROM_TASK’ with the following SQL query as the source that will populate the page item with the expense type:

select param_value
  from apex_task_parameters
 where task_id = :P2_TASK_ID
   and is_visible = 'Y'
   and param_label = 'Expense Type';

I’ve then created a static region with the following HTML in between the ‘Details’ and ‘Comments’ sections in the page content body.

<iframe id="OACtask" width=100% height=600px src="">
</iframe>

As you can see, there is no URL defined for the src parameter, we’re going to generate that dynamically and pass the value using JavaScript.

At the page level, I’ve added the following to the ‘Execute when Page Loads’ section (you’ll have to scroll to see the end of the last line in he code sample below where the important bit is !):

vExpenseType = apex.item("P2_EXPENSE_TYPE_FROM_TASK").getValue();
console.log('EXPENSE TYPE:' + vExpenseType);
document.getElementById("OACtask").src = "https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&noheader=true&reportpath=%2F%40Catalog%2Fshared%2FAPEX%20EXPENSE%20APP%2FAPEX%20AVG%20COST%20BY%20EXPENSE%20TYPE%20PARAMETER&p1n=pEXPENSE_TYPE&p1v=" + vExpenseType;

In this code, we’re populating the variable vExpenseType from the page item created earlier. The console.log is useful for troubleshooting and is optional.

In the final line, we’re passing the value of the vExpenseType as a parameter value to the Oracle Analytics workbook URL and then using that as the src for the iFrame with reference “OACtask”. Note the addition of &noheader=true to the URL.

I’ve included a screenshot of the APEX Task Details page below.

Here is how the application page looks at runtime, with the analytics embedded visible in the bottom right hand corner:

A Personalised Analysis of Expense history

In this section, we’re going to add an analytics canvas to the ‘My Expense Requests’ APEX expense application page. We will embed the canvas so it shows only the expense requests for the user logged into the APEX application. The application page with embedded analytics is shown below:

The key additional information for this use case is how to obtain the user who is logged in to the APEX application. The process for applying the filter to the emebedded analytics canvas is the same as the earlier use case, i.e., pass the parameter on the analytics workbook URL referenced in an iFrame in a static region that has the src parameter dynamically generated.

In order to obtain the user who is logged in, a hidden page item ‘P6_USER’ is created with default value ‘&APP_USER.’

At the page level, the ‘Execute when Page Loads’ property contains (again, scroll to the end of the code sample below) :

vUserName = apex.item("P6_USER").getValue();
console.log('USERNAME:' + vUserName);
document.getElementById("OACembed").src = "https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportpath=%2F%40Catalog%2Fshared%2FAPEX%20EXPENSE%20APP%2FAPEX%20EXPENSES%20ANALYSIS&viewermode=true&noheader=true&p1n=pEMP_NAME&p1v=" + vUserName;

To wrap up, I’ve made the argument that adding analytics content, in context, to APEX applications that use workflow and tasks can add value in terms of insights that can assist the user making business decisions. And, hopefully as this blog has shown, it’s quite an easy process to add that insight from Oracle Analytics.