EPM GROOVY – How To Update User Variable Values Using a Groovy Business Rule

If you’ve been working with EPBCS recently, you may have all encountered the problem of a frozen screen when users attempted to update a new user variable value using the user preference window. This has been fixed now using a recent patch. But I hope it’s good to have a solution where the system prepopulates the values of each user variable in the future, so users don’t have to use the user preference window to update the user variable values every time a form fails to open. Meanwhile, let’s see how we can update it using a Groovy business rule that will update user variable values.

Let’s follow the below steps to populate the user variable values.
1. Retrieve all user variables from the system that have no values.
2. Map user variables to their corresponding dimensions.
3. Fetch all the members from the user variable member selection filter.
4. Assign the first value of the member list to the user variable.

Define or fetch all the system objects

Let’s get all cubes, all user variables, and all dimensions from the application. At this point in time, we don’t know how many user variables are being set or not by the users. I would filter only the user variables that have not been set with any member values.

//define and fetch: cubes, dimensions and userVariables
List<Cube> cubes = operation.application.cubes
List<Dimension> dimensions = operation.application.getDimensions(cubes as Cube[])
List<UserVariable> userVariables = operation.application.userVariables.findAll{!it.value}

Associate user variables with its dimensions

As I mentioned earlier, the Dimension class provides the option to check if it has a user variable association or not. I am making a combinations of all user variables and all dimensions first. Once the combination is set, we will be looking for the dimension it is associated with. I’ve been writing a lot of closures recently because they’re easy to read, and I use a lot of functional programming.

//Map UserVariables with their dimensions
Closure findValidUserVariables = { UserVariable userVariable, Dimension dimension -> dimension.hasUserVariable(userVariable.name)}
Closure collectUserVariables = { UserVariable userVariable, Dimension dimension -> [(userVariable):dimension]}
Map<UserVariable, Dimension> dimensionMapOfUserVariables = [userVariables, dimensions].combinations().findAll(findValidUserVariables).collectEntries(collectUserVariables)

Build the method for finding the first member

We are using the getEvaluatedMembers function to get all the members. One important thing is that we need to get the member selection definition from the user variable. Luckily, the UserVariable object provides access to its member selection. We use that in the getEvaluatedMembers method. I used the find() function, as it returns the first non-null value from a list.

When dealing with members that contain special characters, getEvaluatedMembers throws exceptions. To prevent exceptions, we must enclose member names in double quotes. I made use of the replace function as below.

//traverse through all cubes and get the first valid member
Member findFirstMember(Dimension dimension, String memberSelectionString) {
	Set<Member> members = []        
	operation.application.cubes.each { cube ->
		if(operation.application.hasDimension(dimension.name, cube)) {
			members.addAll (dimension.getEvaluatedMembers(memberSelectionString.replace('(', '("').replace(')','")'), cube))
    return members.find()

Set User Variable values

We have finally got that one member, which we would like to assign to the user variable. We will traverse through each user variable, get the first member, and assign a value as below.

//set first member of getEvaluatedMembers Function
dimensionMapOfUserVariables.each { UserVariable userVariable, Dimension dimension ->			
	Member firstMember = findFirstMember(dimension, userVariable.memberSelection)	    
	if(firstMember) {
		println "Updating $userVariable.name with $firstMember.name"
		operation.application.setUserVariableValue(userVariable, firstMember)    		

I hope Oracle provides us the member selection string from each user variable with double quotes so that we don’t have to worry about finding exceptions. I believe this works on all the applications. Please let me know if you encounter any exceptions with your application.

Before Execution:

Job Console Log:

After Execution:

I hope this information is useful.


2 responses to “EPM GROOVY – How To Update User Variable Values Using a Groovy Business Rule”

  1. Vinish Parmekadan avatar
    Vinish Parmekadan

    Will it fetch the first Non-Null value based on Users Dimension Security?

    1. Raja Stalvin avatar

      Yes, it does. The find() function will return the first non-null value from a list. getEvaluatedMembers() is always based on the theory of user security. The script will simply ignore the update of the user variable if it returns null.