Customizing Hyperion Planning Data Entry Forms in 11.1.2.3 version

All,

this time I want to share my expertise in customization Hyperion Planning Data Entry Form on the last version 11.1.2.3.

Before to begin, we must analyze how is possible customizing Hyperion Planning forms using JavaScript. Being able to customize forms using JavaScript is a great feature of Hyperion Planning. There are, for example, JavaScript functions that allow you to create custom validations for forms. If you're using version 11.1.2.1, then you're all ready to go. However, if you're using the latest version then you have to enable this functionality. We're going to do that in this post and then show you how to get started working with specific applications and forms.

For this JavaScript implementation, we must follow the next four steps.

Step 1: Enable JavaScript customization for your Hyperion Planning application

Customization can be enabled for each Planning application individually. There is an application property, named ORACLE_ADF_UI, that can be set to true or false. It controls whether or not the application user interface appears using the Oracle Application Development Framework (ADF) or as presented in version 11.1.2.1.

The default in 11.1.2.3 is true. 

The TotPlan demo application is being used for this post. This is how it appears in the ADF UI.

In order to change this to the 11.1.2.3 interface and thereby enable JavaScript customization, we'll open up application properties from the Administration section of the menu.

From Administration menu, select

[Application] > [Properties]

In the Application Properties tab click the add button to add the ORACLE_ADF_UI property, which isn't listed by default.

This will add a new row for the property. In the Property Name column type insert the ORACLE_ADF_UI value. Then, set it to false in the Property Value column, since we want the old 11.1.2.1 interface.

Click Save button and you will get the following message. I've found that simply closing the application and then reopening it suffices to have the setting take effect. Your mileage may vary.

Step 2: Locate and open the ValidateData.js file under 11.1.2.3 installation

Follow the steps below to find the validateData.js file and customize it.

1. If you look in : “<MIDDLEWARE_HOME>\EPMSystem11R1\products\Planning\AppServer\InstallableApps\Common\”
2. There will be a file called HyperionPlanning.ear
3. If you expand this file using something like 7zip, or equivalent, there will be a HyperionPlanning.war
4. and if you expand that there will be the structure you are probably used to with the custom folder.
5. Update ValidateData.js
6. IMPORTANT NOTICE!!! You will need to then pack the files in the same way so you end up with HyperionPlanning.ear, if using 7zip it is quite easy as you can just extract ValidateData.js, update it and then drag it back in.
7. Stop planning and related services (probably also need stop the Foundation service)
8. Go to: “<MIDDLEWARE_HOME>\user_projects\domains\EPMSystem\servers\Planning0\tmp\servers\Planning0\tmp\_WL_user\PLANNING_11.1.2.0\3i5tnd\war\custom” and delete the ValidateData.js tmp file
9. Start planning and related services and the new code should be deployed. Make a test!!!

Under Tomcat application server it was much simpler, but most of Hyperion installations are under WebLogic application server now.

Instead to follow the path describe above, try a simple file search on your operating system should work to find it. It'll be in a folder named custom.

After locate the ValidateData.js file on your application server, you can modify it (best practice is do a copy of original). This is where you can make changes to JavaScript functions to customize your forms.

There are actually three important files for JavaScript customization:

• ValidateData.js
• SampleValidateData.js
• ValidateDataHelper.js

The first is the focus of this post and includes the JavaScript functions we're going to look at to customize forms. The second file gives examples of these same functions modified for customization. The third contains utility functions that can be used to customize forms.

Hereunder, are the functions listed in the ValidateData.js file:

• customEnterPage
• customOnLoad
• validateForm
• customSaveFormPost
• drawCustomButtons
• customCellEnterPre
• customCellEnterPost
• customCellValidatePre
• customCellValidatePost
• customSpread_Pre
• customSpread_Post

Step 3: Specify the application and forms to be customized

The first thing you'll want to do is specify where you want customization to take place. Here is a snip of code that shows how to do this.

The function that I'm using is validateForm(). This function runs whenever a grid is saved. There are four items that require explanation to show how this works.

1. validateForm() – This function return a Boolean value (true or false). This value is stored in a “valid” variable. If “valid” variable is TRUE the Data Entry Form will be saved, otherwise not.
2. applicationName - This is a variable that contains the currently opened form's application's name.
3. formName - This is a variable that contains the currently opened form's name.
4. equalsIgnoreCase - This function has two string parameters. It compares the first string to the second string while ignoring capitalization and then returns a comparison between the two as follows:

      string1==string2

That's why we can use it in an IF clause; if the returned comparison is true, whatever is specified in the IF clause is executed. If the returned comparison is false, the else clause contents is executed.  

The code above executes as follows:

1. First check if we're in the TotPlan application, if not, the function ends.
2. Then check if the "MyDEF" is the opened form.
3. If it is "MyDEF", an alert box opens notifying user that this is the opened form.
4. If it isn't, the “valid” variable is set to FALSE and the function ends. The Data Entry Form will not be saved.

As you can see, it's pretty simple to makes sure you're working on only one specific application and/or form.

Step 4: Stay tuned for future posts

There's much more to explore with JavaScript customization of Hyperion Planning forms. From cell specific prompts to extracting metadata, administrator's can amaze users with some impressive functionality.

View the others posts in my blog on this perspective.

For Data Entry Form customization on 11.1.1.3 version of Hyperion Planning, view the related link below:

http://flavio-depedis.blogspot.it/2014/04/how-to-color-cells-in-data-entry-form.html

Send me a comment if you need.

Enjoy and I hope that you appreciate.

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. 

How to execute ODI 11g Scenario from Java

Hi all,
in this post I want to explain how to run an ODI 11g scenario from Java. To be honest, I had try only with ODI version 11g and not with previously versions.

For this implementation we need of some knowledges of Java and Eclipse develompment tool.

It's amazing how many things you can do with the interaction between Java and ODI.

Well, the steps for this development are:

1. Create a Java project
2. Import the necessary library for connect to ODI SDK
3. Write the sample code for call an ODI scenario
4. Export your Java project such as JAR file
5. Understand what it could do the ODI project

Ok, we analize step by step.

1) 
For create a Java project, I have used the Eclipse tool that you can find here. I use the version of Eclipse IDE for Java EE Developers, but the classic version is also good.

After you downloaded and installed the IDE you must create a new project. Select "File > New > Project" and from the window wizard select "Java" folder and then "Java Project". Enter the name of java project and others information that the wizard require. Click "Finish" button when you have complete.

Create Java Project

2)
The second step require to import the necessary SDK library for connect to ODI and execute the ODI scenario that will develop further.

In Eclipse IDE, expand the project node in Package Explorer pane and create a new folder named "lib" (Right-click on project > New > Folder).

For your convenience, copy in this folder all the files with jar extensions for ODI-SDK that you can find on your ODI installation path

../oracledi.sdk/lib

For the documentation, you can also find the Oracle Data Integrator Java API Reference 11g - Release1 (11.1.1.6.0) here.

After this, you must to reference this library into our project. Right-click on "lib" folder and select "Build Path > Configure Build Path".

Select "Add External JARs" and get all jar files that you have copied in the lib folder.

3) 
Now the core of this post. You must to write the code for call an ODI scenario. See the example below and check where you must change the value for the parameters.

The project is compose from two classes:

- OdiMain (main class)
- OdiInvokeScenario (class that invoke a scenario)

In the "OdiMain" class are defined all variables that you need setup their value for call a scenario ODI. All these variables are passed to "OdiInvokeScenario" class, that actually call a scenario ODI.

/* Main class */

import oracle.odi.runtime.agent.invocation.InvocationException;
import oracle.odi.runtime.agent.invocation.StartupParams;
import odi.sdk.*;
public class OdiMain {

    public static void main(String[] args) throws InvocationException {
       
        //SET PARAMETER HERE
        String odiHost = "http://yourmachine:port/oraclediagent";
        String odiUser = "";  //INSERT ODI USER
        String odiPassword = "";  //INSERT ODI PASSWORD
        String odiScenName =  ""; //INSERT NAME OF SCENARIO ODI
        String odiScenVersion = ""; //INSERT VERSION OF SCENARIO ODI
       
        StartupParams odiStartupParams = new StartupParams();
        
        //IF YOU HAVE PARAMETERS FOR SCENARIO ODI INSERT HERE
        odiStartupParams = null;
        String odiKeywords = null;
 
        //INSERT THE CONTEXT FOR SCENARIO ODI "GLOBAL" SUCH AS DEFAULT
        String odiContextCode = "";
        int odiLogLevel = 5; //INSERT LOG LEVEL
        String odiSessionName = null; 
        boolean odiSynchronous = true; //TRUE FOR SYNC MODE, FALSE ASYNC MODE
        String odiWorkRepName = ""; //INSERT NAME OF YOUR WORK REPOSITORY
       
        OdiInvokeScenario call = new OdiInvokeScenario();
        
        call.runScenario(odiHost,odiUser,odiPassword,odiScenName,odiScenVersion,
                         odiStartupParams,odiKeywords,odiContextCode,
                         odiLogLevel,odiSessionName,odiSynchronous,
                         odiWorkRepName);
    }
}

/*  OdiInvokeScenario class  */

import oracle.odi.runtime.agent.invocation.ExecutionInfo;
import oracle.odi.runtime.agent.invocation.InvocationException;
import oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker;
import oracle.odi.runtime.agent.invocation.StartupParams;

public class OdiInvokeScenario {

    public void runScenario(String odiHost,String odiUser,String odiPassword,
                            String odiScenName,String odiScenVersion,
                            StartupParams odiStartupParams,String odiKeywords,
                            String odiContextCode,int odiLogLevel,
                            String odiSessionName,boolean odiSynchronous,
                            String odiWorkRepName) throws InvocationException {
        try{
       
            //Starting the session on the remote agent.
            RemoteRuntimeAgentInvoker remoteRuntimeAgentInvoker = 
            new RemoteRuntimeAgentInvoker(odiHost,odiUser,
                                          odiPassword.toCharArray());
           
            //Parameter: 
            //pScenName,pScenVersion,pVariables,pKeywords,
            //pContextCode, pLogLevel, pSessionName, pSynchronous, pWorkRepName
            //pVariables ==> startupParams = new StartupParams();
           
            ExecutionInfo exeInfo =
                    remoteRuntimeAgentInvoker.invokeStartScenario(                                         odiScenName,odiScenVersion,odiStartupParams,
                           odiKeywords,odiContextCode,odiLogLevel,
                           odiSessionName,odiSynchronous,odiWorkRepName);
           
           
            //Retrieve the session ID.
            System.out.println(
              "-----------------------------------" + "\n" + 
              "Scenario Started in Session : " + exeInfo.getSessionId() + "\n" +
              "Scenario Session Status : " + exeInfo.getSessionStatus() + "\n" +
              "-----------------------------------" );
        }
        catch (Exception e)    {
            e.printStackTrace();
        }
    }
}

You can copy and paste the code in your environment. If there are some errors, check and resolve it.

4) 
After you complete the java project and test it, you can export the project such as jar file. The jar file is an "Java ARchive" file that allow you execute your project calling this file. You can also to call the file from a shell prompt Windows or Unix/Linux or from a dashboard or other again.

For create your jar file select  "File > Export"

Export project for create a jar file

In the export wizard, open the Java folder and select "Runnable JAR file". Enter the name of JAR file and the path where you want to save it. 

5) 
Finally, if you have just the ODI project developed, you can try now your implementation respect to this post. Conversely, if you must again to develop your ODI project you must understand that you can potentially run your ODI project, throught ODI scenario, maybe from everywhere recalling only your jar file.

Ok guys, for this post is all. I hope that you appreciate that and now you are happy.
Enjoy and keep in touch.

How to execute ODI Scenario with a button in a Data Entry Form from Hyperion Planning 11.1.1.3

The goal of this post is to share with the insiders, the best technical/functional proposal.

This implementation is valid for EPM Suite 11.1.1.3 version (Hyerion Planning).

In particular, the development enables to implement a button into Data Entry Form (DEF) in Hyperion Planning that allows the execution of an Oracle Data Integrator scenario (ODI). 

The scenario ODI, in our case, changes the value of a Substitution Variable in Essbase.

As you can well imagine, we could be doing anything else much more complex than a simple change of the value of a Variable Substitution.

So, the steps to implement are:

1. How to insert a button in Hyperion Planning 11.1.1.3 (Data Entry Form)
2. How to call ODI Scenario
3. Setup the parameters for run a ODI scenario
4. Development ODI package for setup Substitution Variable in Essbase

Let's start...!!!

1. How to insert a button in a Hyperion Planning

To create a button in a form, we have to refer to the JavaScript file called "ValidateData.js" that allows us to create the controls at the level of form.

In this file, we can implement a JavaScript function that is already natively declared:

• drawCustomButtons()

As can be seen, this function requires no input parameter and the implementation of the button occurs within the function itself.

Below is a piece of code of the function mentioned above:

drawCustomButtom() function

I have highlighted in blue how is builded the button. Substantially within the function there is a further called native function:

• DrawCmdBtn(......);

that creates the button itself.

The "DrawCmdBtn" function receives a number of parameters, such as the example above.

After this step, we can see inside a DEF in Hyperion Planning something like this:

Data Entry Form with button (Setup Month)

WARNING!!!

  - If EPM suite installation was made under WebLogic, to see the changes made ​​to ValidateData.js  file you must perform a re-deploy of Hyperion Planning module (or restart the Hyperion Planning service)

  - If the installation was done under Apache, just refresh the page or the Data Entry Form in question.

2. How to call ODI Scenario

To run a scenario ODI we need to refer to the previous case, namely the creation of the button that calls a function which writes the entire contents of a web page in HTML.

Taking, therefore, for example:

• DrawCmdBtn ("PlanningBtn", "Setup Month", null, "submitForm ()", "Setup Month");

will be called "submitForm()" function created specifically to write a new HTML page.

Below the "submitForm()":

submitForm() 1 of 2

and the piece of code for declared variables in ODI

submitForm() 2 of 2

The function starts and ends with an opening and closure tag <html>.

Within define the parameters for the call scenario ODI and related input variables that need to be initialized on startup of the same. In this case there is only one variable called "MONTH", which is enhanced by an HTML select list with the values ​​of the twelve months of the year.

At the end of the function create an additional button in the new web page named "Execute Scenario", as shown in the figure, that initiates the event execution of the scenario ODI.

At its clicks, the form is submitted containing the parameters ODI using the function "submitAction_1(form)"

Form HTML submitted

NOTE: This solution creates a new external web page respect to Hyperion Planning and therefore to Hyperion Workspace. From this page, the event is triggered by the execution of the scenario ODI.

External Web Page

ODI Scenario executed

3. Setup the parameters for run a ODI scenario

To run a scenario ODI via web is necessary to define the parameters in the following list and prepared in an HTML form.

The scenario ODI runs through to the call of Java servlets "StartScen."

• agent_name: Network name or IP address of the machine running the agent.
• agent_port: The listening port for the agent.
• master_driver: JDBC driver used to access the main repository.
• master_url: JDBC URL used to access the main repository.
• master_user: Database user to connect to the main repository.
• master_psw: Password for the database user. This password must be encrypted work_repository. Name of the repository in which the scenario to be executed is stored.
• snps_user: User name ODI.
• snps_psw: Password for the user name ODI.
• scen_name: ODI scenario name.
• scen_version: Version number of the scenario.
• context_code: Context (Default: GLOBAL)
• log_level: Defines the logging level for the execution of the scenario. All activities whose recording level is less than or equal to this value will be kept in the register until the end of execution. However, if the scenario ends abnormally, all activities will be held, regardless of this setting . This parameter is an integer from 0 to 5 (Default: 5).
• http_reply: Type of the HTTP response. This response indicates whether the session has been started or not. Types of responses are possible XML, HTML, and text. The default is XML. The valid values ​​for this parameter are XML | HTML | TXT. In addition to the response in XML, HTML or text format, all responses from the HTTP servlet StartScen are returned as a cookie in the HTTP header.

In our example, the parameters were evaluated as shown below.

NOTE: Many of the values ​​associated with the parameters in question can be traced on the connection within the ODI Designer.

ODI Scenario Parameter 

As can be seen, to perform a scenario ODI it requires a number of parameters, of which two of them are of type password:

• master_psw
• snps_psw

which are respectively the password to log on as a user of the database, the first, and as an user ODI, the second.

These two passwords must be passed, necessarily, as encrypted values​​. To encrypt the passwords we use the utility "agent.bat". This installation ODI at the following path:

• <ODI_HOME>\oracledi\bin

To run this utility, you must then move to the above path, through a shell (Windows/Unix), and run it as follows:

• agent.bat ENCODE <password>

The encrypted value returned can be used as the value to associate with the passaword. Below is an example of its use:

Encrypt password

4. Develop ODI package

To run ODI scenario, we must first create one, then create a package. In this example we will create a package that will allow you to set a value for a substitution variable within Essbase.

Shown below is composed of only one variable called "MONTH" (which take the value that will be set on the substitution variable in Essbase) and from an OdiOSCommand component called "SET_MONTH". The first will be initialized when the scenario starts and the second will invoke a batch file which is passed to the variable initialized at execution time (as shown in the properties of the figure).

To verify the correct execution of the ODI package, we can view the ODI Operator and display the last completed job.

ODI Package

ODI Operator - Scenario to good end

The result of all the process can be viewed in Essbase Administration Services Console, where the substitution variable changes its value, for example from "May" to "Jun" .

Before modify substitution variable "MONTH"

After modify substitution variable "MONTH"

Ok guys, this is all...The first post is complete. If you have any question or comment, about this post or this blog, please write or send me email.

I hope that you appreciate.

Enjoy!!!