How to color cells in Data Entry Form in Oracle Hyperion Planning 11.1.1.3

Hi guys,

is a bit of time that I don't write on my blog, but I come back with a beautiful and interesting post.

The goal of this post is illustrate how to color cells in Data Entry Form in Hyperion Planning (from now HPL), not in last version but in 11.1.1.3 version. I remember that from 11.1.2.0 version, Oracle delivered the functional control on Data Entry Form and maybe available this example on these feature.

For this example, we have necessary to refer at JavaScript file named "ValidateData.js" that allows us to create the control on DEF and we will use the "validateForm()" function for execute our control on save button event.

So, the implementation is not complicated, but very simple. We imagine that we have a DEF build as figure below:

Form - MyDEF

For this implementation I report a simple code to color cells into DEF that must be insert in ValidateData.js.

At begin of file we can declare a global variable that contain the hexadecimal code for to color cells. Otherwise, it's also possible insert the RGB code instead the hexadecimal code. The corresponding RGB code is: 255,102,51

Declare global variable.

If you want use the RGB code replace "#FF6633" whit "rgb(255,102,51)" value.

In the ValidateData.js is already defined a set of native functions. One of this is the validateForm() function. See the code below and rewrite it in validateForm function.

validateForm function

The validateForm function is called from process when user click the save button for save data after any modify the DEF. 

This function return a boolean value through a variable named "valid". This variable is set at begin of the function with default value "TRUE".

When user resides into DEF that named "MyDEF" belonging at "MyApplication" application, the custom function "ctrlForm_MyDEF" is called. This last function is the core of control of data into DEF and is here where set the properly properties for color the relative cells that not respecting the conditions parameters.

ctrlForm_MyDEF - Custom function - 1 of 2

ctrlForm_MyDEF - Custom function - 2 of 2

The custom function "ctrlForm_MyDEF" is divided in two fondamental parts. 

In the first part, there are the declaration of variable that identify the columns where doing the control and the variable named "row" for indentify the start row in the grid refer the DEF.

The second part, is where the control has affected on the grid. The strategy is to do two cycle into grid:

1. Cycle all grid for verify if there are inconsistent value respect to control. If found different value from actual and budget column, an alert appear whit a relative message and the "valid" variable is set to "FALSE". A "break" instruction is used for exit to cycle for.
2. The second cycle is necessary for to color the cells using the same "IF" statement of first cycle. The condition is the same. If the condition is verified, the actual and budget column ,on the respecting row, are colored with red color defined by "#FF6633" hexadecimal code in "colorError" global variable.

N.B.: Note that if used a single cycle, the alert appear only one shot or always depending on how the code is implemented.

The result after implementation are same of this:

Alert message

Colored cells where there are different values

Below carry-over the complete code of all. You can copy and paste.

/**
* Global variable <b>colorError</b> - This variable contain the hexadecimal code for to color cells
* of the Data Entry Form in <b>red</b> when the value of actual is different from budget.
* @example
* var colorError = "#FF6633";
* 
* @param colorError
*/
var colorError = "#FF6633";

/**
 * function - ValidateForm()
 *
 * This function is called from process when user click the save button.
 * @methodOf validateForm
 * @throws NoException
 * 
 * @return {valid|boolean} The function return a value TRUE or FALSE.
 *
 * @author Flavio De Pedis
 * @since Version 1.0
 */
function validateForm(){

var valid = true;

//Enter if the name of application is "MyApplication"
if (equalsIgnoreCase(applicationName,"MyApplication")){

//Enter if the name of Data Entry Form is "MyDEF"
if (equalsIgnoreCase(formName,"MyDEF")){
valid = ctrlForm_MyDEF();
}
}
return valid;
}

/**
 * function - ctrlForm_MyDEF()
 *
 * This function is called when the users reside into Data Entry Form 
 * <b>"MyDEF"</b> and save it through the save button. At this moment the control are running and verify if the column of
 * actul and budget are different. If this occurrence is TRUE the cells of actual and budget are colored.
 * @methodOf validateForm
 * @throws NoException
 * 
 * @return {valid|boolean} The function return a value TRUE or FALSE.
 *
 * @author Flavio De Pedis
 * @since Version 1.0
 *
 */
function ctrlForm_MyDEF(){

var valid = true;

var row = currentDataGrid.startRow;
var col = currentDataGrid.startCol;
var colActual = col;
var colBudget = col + 1;

//if you have more then one grid in DEF, through this set up you can select which grid you perform the control
// 0 - first grid
// 1 - second grid, and so on
/*
currentDataGrid = dataGridArray[1];
currentGridIndex = 1;
setCurrentGrid(currentGridIndex);
*/

//1° ROUND - VERIFY IF THE CELLS VALUE ARE DIFFERENT AND ALERT ONE SHOT
for(row; row <= currentDataGrid.endRow; row++){
if( getCellVal(row,colActual)!=getCellVal(row,colBudget) ){
alert("ATTENTION!!! - Is present the different value between actual and budget");
valid = false;
break;
}
}

//2° ROUND - COLOR CELLS
for(row = currentDataGrid.startRow; row <= currentDataGrid.endRow; row++){
if( getCellVal(row,colActual)!=getCellVal(row,colBudget) ){

//color cells in "red"
currentCell = getCell(row,colActual);
currentCell.style.backgroundColor = colorError;
currentCell = getCell(row,colBudget);
currentCell.style.backgroundColor = colorError;
}
}
return valid;

}

N.B.: If you use the JavaDoc for automatically generate documentation, the comment in blue will be compiled. 

Ok guys, also for this post it's all. 

Send me a comment if you need.

Enjoy and I hope that you appreciate.