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.

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


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

ATTRIBUTES

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

UDAs

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

SUBVARs

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

USER VARIABLES

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

ESSBASE FUNCTIONS

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

ANCESTORS

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

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

CHILDREN

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

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

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.

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.

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

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

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

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

GENERATION MEMBERS RELATIVES

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.

«
»
, , , , , , , , ,