OBIEE Apex

There are often times when you want to update values presented to a user on a dashboard page, but “writeback” is not a secure solution and offers no validation

A workaround, is to embed an APEX form/application and pass values via a java script called with in an OBIEE analysis.

Steps to Achieve this :-

Develop the Apex Application

• A sample application is attached “f104.sql” which is the application used within APEX for this scenario. It already has the Login page defined, and the “update” form for the application which can be tailored.
• Much of the application can be created automatically via the APEX design tempate/wizard. The application though has a few key items
• The “Retrieve” is run, so passing in the value from the OBIEE analysis will target the source table, and return the row based on the key provided see screenshot below for row retrieval :-

• Here we can see the table name, plus the name of the parameter being passed by the OBIEE analysis
• The Update action button is then defined,

• Note the Update button database action

Edit UserScripts.js

• usually found @ locations :- $ORACLE_HOME/Oracle_BI1/bifoundation/web/appv2/res/b_mozilla/actions
• /home/oracle/obiee3/user_projects/domains/bifoundation_domain/servers/AdminServer/tmp/_WL_user/analytics_11.1.1/silp1v/war/res/b_mozilla/actions
• A sample script is listed below. This calls the APEX form developed above. It accepts a parameter that is passed from the OBIEE analysis.

USERSCRIPT.callApexUpdatePop=function(aParams){

var sArgs = “”;
for( args in aParams )
{
var argName = args;
var argValue = aParams[argName];

sArgs = argValue;

}

console.log(“sArgs is :”,sArgs);
targUrl = “http://obieesample.us.oracle.com:8080/apex/f?p=104:1:::::P1_ROW_WID:” + sArgs;
console.log(“targurl is :”,targUrl);

var w,h,scroll;
w=”600″;
h=”400″;
var LeftPosition = (screen.width) ? (screen.width-w)/2 : 0;
var TopPosition = (screen.height) ? (screen.height-h)/2 : 0;
var settings = ‘height=’+h+’,width=’+w+’,top=’+TopPosition+’,left=’+LeftPosition+’,scrollbars=’+scroll+’,resizable=0,toolbar=0,menubar=0,location=0,status=0′;
popupWindow = window.open(targUrl,”,settings);
popupWindow.show();

};

USERSCRIPT.callApexUpdatePop.publish={
// The existence of this ‘publish’ object causes the ‘USERSCRIPT.example_displayParameters’ function to be
// shown when browing the available user script functions (during creation of a Script action).

// If you wish the Script function to have parameters automatically created on selection of the function,
// create a ‘parameters’ object as shown below.
// You can have any number of parameters, with each parameter requiring a unique name, prompt and an
// optional value.
parameters :
[
new USERSCRIPT.parameter( ‘P1_ROW_WID’, ‘ROW WID ID’ )
]

// If no generated parameters are required, either create an empty array
// parameters : []
// or don’t declare the ‘parameters’ object at all.
};

Having edited the UserScripts.js file., the entire OBIEE weblogic/obiee services need to be restarted

Edit OBIEE Analysis

The analysis can be used to embed and “edit” link into the table output, and pass the value of the item from the analysis to the APEX form. In the example above the parameter is named “P1_ROW_WID”.

Edit the OBIEE analysis, add a new column, default the text to ”   EDIT     “

Edit the column > select column properties > select the interaction tab

Edit the action, if the UserScript.js has been updated correctly, browse then select the name of the function you want to call. In this case callApexUpdatePop.  

You can now enter the name of the Variable as defined in the script, and pass in the correct value form the OBIEE analysis i.e. select the column you need to pass into the APEX application.