EPM GROOVY – Get Evaluated Members Function Cheat Sheet

A powerful method named getEvaluatedMembers() can be used in a Groovy business rule to fetch members from the system depending on various relationships to the current member.

We can retrieve members based on below conditions.

Relationship functions (Children, Parents, etc)
Substitution Variable
User Variables
Attributes
UDAs
Essbase member relationship functions ( @Relative(“Sample”,0) ) – Works now but not sure it will be supported long term )

There aren’t a lot of resources available right now. I therefore decided to make a cheat sheet for quick reference.

Since we must repeatedly call the getEvaluatedMembers() functions, I have written the code below, which we will use repeatedly. I have used the Period and Years dimensions, which most of us can recognize and relate to. I am retrieving the string equivalent of the members.

Groovy

List<String> getMembers(String dimension, String member) {
    Cube cube = operation.application.getCube('Plan1')
    Dimension currDim = operation.application.getDimension(dimension, cube)
    List<String> members = currDim.getEvaluatedMembers("$member", cube)*.name
    return members
}

Few things to keep in mind:

The getEvaluatedMembers() function complies with system security, so it returns only the members where the current user has access to
The relationship query is not case sensitive, including the member names

MEMBER

Groovy

List<String> member = getMembers('Period', 'Q1')
assert member == ['Q1']

ATTRIBUTES

Groovy

List<String> attributesMembers = getMembers('Entity', 'Large Market')
assert attributesMembers == ['420', '422', '430', '450']

UDAs

Groovy

List<String> userDefinedAttributesMembers = getMembers('Period', 'UDA(START_MONTH)')
assert userDefinedAttributesMembers == ['Jan']

SUBVARs

Groovy

List<String> subVarMembers = getMembers('Year', 'LSiblings("&NextYear")') //NextYear == 'FY22'
assert subVarMembers == ['FY15', 'FY16', 'FY17', 'FY18', 'FY19', 'FY20', 'FY21']

USER VARIABLES

Groovy

List<String> userVariableMembers = getMembers('Period', 'Siblings("&Period")') //Period == 'Jan'
assert userVariableMembers == ['Feb', 'Mar']

ESSBASE FUNCTIONS

Groovy

List<String> essbaseFunctions = getMembers('Period', '@Relative(Q1,0)')
assert essbaseFunctions == ['Jan', 'Feb', 'Mar']

ANCESTORS

Groovy

List<String> iAncestors = getMembers('Period', 'IAncestors(Q1)')
assert iAncestors == ['Q1', 'YearTotal', 'Period']

List<String> ancestors = getMembers('Period', 'Ancestors(Q1)')
assert ancestors == ['YearTotal', 'Period']

CHILDREN

Groovy

List<String> iChildren = getMembers('Period', 'IChildren(Q1)')
assert iChildren == ['Jan', 'Feb', 'Mar', 'Q1']

List<String> children = getMembers('Period', 'Children(Q1)')
assert children == ['Jan', 'Feb', 'Mar']

DESCENDANTS

Groovy

List<String> iDescendants = getMembers('Period','Idescendants(Q1)')
assert iDescendants == ['Jan','Feb','Mar','Q1']

List<String> descendants = getMembers('Period','Descendants(Q1)')
assert descendants == ['Jan','Feb','Mar']

SIBLINGS

Groovy

List<String> iSiblings = getMembers('Period','ISiblings(Q1)')
assert iSiblings == ['Q1', 'Q2', 'Q3', 'Q4']

List<String> siblings = getMembers('Period','siblings(Q1)')
assert siblings == [ 'Q2', 'Q3', 'Q4']

PARENTS

Shared Members are excluded.

Groovy

List<String> iParents = getMembers('Period','IParents(Q1)') //shared members excluded
assert iParents == ['Q1', 'YearTotal']

List<String> parents = getMembers('Period','Parents(Q1)') 
assert parents == ['YearTotal']

LEVEL ZERO DESCENDANTS

If ILvl0Descendants is not able to fetch the level zero members, it returns the current member.

Groovy

List<String> levelZeroDescendantsQ1 = getMembers('Period','Lvl0Descendants(Q1)') 
assert levelZeroDescendantsQ1 == ['Jan', 'Feb', 'Mar']

List<String> iLevelZeroDescendantsQ1 = getMembers('Period','ILvl0Descendants(Q1)') 
assert iLevelZeroDescendantsQ1 == ['Jan', 'Feb', 'Mar']

List<String> levelZeroDescendantsJan = getMembers('Period','Lvl0Descendants(Jan)') 
assert levelZeroDescendantsJan == []

List<String> iLevelZeroDescendantsJan = getMembers('Period','ILvl0Descendants(Jan)')
assert iLevelZeroDescendantsJan == ['Jan']

SIBLINGS RELATIVES

Groovy

List<String> iLeftSiblings = getMembers('Year', 'ILSiblings(FY20)')
assert iLeftSiblings == ['FY15', 'FY16', 'FY17', 'FY18', 'FY19', 'FY20']

List<String> leftSiblings = getMembers('Year', 'LSiblings(FY20)')
assert leftSiblings == ['FY15', 'FY16', 'FY17', 'FY18', 'FY19']

List<String> iRightSiblings = getMembers('Year', 'IRSiblings(FY20)')
assert iRightSiblings == ['FY20', 'FY21', 'FY22', 'FY23', 'FY24', 'FY25']

List<String> rightSiblings = getMembers('Year', 'RSiblings(FY20)')
assert rightSiblings == ['FY21', 'FY22', 'FY23', 'FY24', 'FY25']

List<String> prevSibling = getMembers('Year', 'PrevSibling(FY20)')
assert prevSibling == ['FY19']

List<String> nextSibling = getMembers('Year', 'NextSibling(FY20)')
assert nextSibling == ['FY21']

List<String> prevSiblingNull = getMembers('Year', 'PrevSibling(FY15)')
assert prevSiblingNull == []

LEVEL ZERO MEMBERS RELATIVES

Groovy

List<String> nextLevelZeroMember = getMembers('Period', 'NextLvl0Mbr(Mar)')
assert nextLevelZeroMember == ['Apr']

List<String> previousLevelZeroMember = getMembers('Period', 'PrevLvl0Mbr(Jan)')
assert previousLevelZeroMember == ['BegBalance']

GENERATION MEMBERS RELATIVES

Groovy

List<String> previousGenerationMember = getMembers('Period', 'PrevGenMbr(YearTotal)')
assert previousGenerationMember == ['BegBalance']

List<String> nextGenerationMember = getMembers('Period', 'NextGenMbr(Q1)')
assert nextGenerationMember == ['Q2']

By combining multiple functions, it is now possible to fetch any member before executing calc scripts, data maps, grids, data exports, data management loads, metadata updates etc. I hope this cheat sheet comes in handy.