EPM RADIANCE

EPM GROOVY – Get Evaluated Members Function Cheat Sheet

·

Raja Stalvin

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.

  1. Relationship functions (Children, Parents, etc)
  2. Substitution Variable
  3. User Variables
  4. Attributes
  5. UDAs
  6. 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:

  1. The getEvaluatedMembers() function complies with system security, so it returns only the members where the current user has access to
  2. 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.