Oracle EPM Groovy Scenarios

Scenario 1: Create Project members on the fly, add an alias, load some values, and create shared members without DB Refresh.

Configuration: in EPBCS Application, Plan1 BSO Cube.

Limitations with the Business rule: We cannot change or add an alias to the members & we cannot create a shared member using the Calc Manager Business rule.

Scenario explanation in detail: Create Project members on the fly and provide some Run time values at the time of member creation like Project No, Alias, Description, Request Date, Project Type & Priority. Also, depending on the type of member a new shared member will get created and it will be added into a respected hierarchy. (Ex. Project members with Project Type IT should go inside IT hierarchy)

Let’s look at the below Project hierarchy in Plan1 cube, we have All Projects as parents of primary hierarchy and Project Type members like Construction, IT, Fleet, Maintenance & Other as shared members’ parent members.

We have Enabled Dynamic child for All Projects parent members.?

Substitution Variables: Below Substitution variables will be used in form and Groovy script.

Variable Designer: In Calculation Manager, we have created the below variables depending on type like String, Member, an integer for Smart list & DateAsNumber.

Smart Lists: We are using the below Smart lists in forms and Groovy rule.

1.??????Priority:

2.??????ProjectType:

Action Menus: Add New Project action menu is created & we have attached the Groovy rule “Add New Projects” to the Menu.

Form: Below is the Add New Project form which has All Projects in Rows, Account, Year, Scenario & Version in Columns & Entity, Product in POVs.

We are not using any Entity or Product so taken No Entity & No Product member.

We have added below Action Menu to the Add New Project form.

After adding the Menu, we can see the Add New Project option is visible in the below form.

Let’s Add New Project by simply right click on member All Projects > Add New Project.

We can see below mandatory fields which need to be filled to Add New Project.

We have filled the mandatory fields like New Project name, Alias, Description, Project No., Priority, Project Type & Date. Once the information is filled click on Launch.

We can see the new project is visible in the form below.

Now let’s check the Dimension to make sure if the shared member is added correctly, we can see the Newly added Construction member CON20220325 has been created under All Projects as a primary stored member and the same member is created under Construction as shared. Here, depending on the Type of Project we have selected at the time of Project creation we have created the shared member inside that Project Type Parent.

Groovy Script Explanation in Summary:

We are taking all the RTPs in and Substitution variable into String and Variables Then checking the condition if the member that we are going to add already exists or not If the member already exists in the system then it will throw an exception saying the member already exists in the system else it will add the new member inside the Parent member as store member.

Once the new member is added we are adding the alias for that and saving it.

The next step is we are loading the values in RTPs using DataGridBuilder and saving the grid.

Once the values are loaded the last step is to create a shared member as per the Project Type. So, we are retrieving the data value of Project Type cross dim with Newly added project member adding it into the list.

Ex. If we have selected IT as Project Type as RTPs while creating a new member, so IT is smart list entry from smart list ProjectType.

We are using DataGridDefinationBuilder to retrieve the Project Type data and add it to the list.

We will get the Smart list value into a list and every time only one value will be there in the list because we are giving only one value at the time of member creation.

We are converting the List’s first member into String and will use it as a Parent member of the shared hierarchy.

We are replacing the Parent first and after that, we are changing the Data Storage type to shared and saving it.

Below are screenshots of Groovy Script along with the line-by-line explanation.

Conclusion: In this way, We can create new members on the fly, can add/change alias, can load some values at runtime, and can create shared members of newly created store members without refreshing the database.