getEvaluatedMembers Groovy Cheatsheet for Oracle EPM Cloud

A concise guide to using `getEvaluatedMembers` in Groovy scripts for Oracle EPM Cloud, including Sales Planning Cloud and ePBCS. Updated as of July 07, 2024.

Syntax

List getEvaluatedMembers(String expression, Dimension dimension)

Description: Retrieves a list of member names evaluated from a dimension based on an Essbase-style expression (e.g., "Children", "Descendants").

Parameters:

expression: String defining the member set (e.g., "Descendants(Entity)")
dimension: Dimension object from the cube (e.g., Entity, Period)

Returns: List of member names as strings.

Common Use Cases

1. Retrieve Members from POV

Fetch members dynamically based on a user-selected POV in a form.

Cube cube = operation.application.getCube("Plan1")
Dimension entityDim = operation.application.getDimension("Entity", cube)
String povMember = operation.grid.pov.find{it.dimName == "Entity"}?.essbaseMbrName ?: "TotalEntity"
List entities = cube.getEvaluatedMembers("Descendants($povMember)", entityDim)
entities.each { println "Entity: $it" }

Tip: Use a fallback (e.g., "TotalEntity") for null POVs.

Use Case: Push data to all descendants of a selected region.

2. Filter Members by Level

Get level-specific members (e.g., Level 0) from a dimension.

Cube cube = operation.application.getCube("SalesPlan")
Dimension productDim = operation.application.getDimension("Product", cube)
List level0Products = cube.getEvaluatedMembers("Lev0,Product", productDim)
level0Products.each { println "Product: $it" }

Tip: Combine with "Children" or "Descendants" for more granularity.

Use Case: Validate data at base-level products in Sales Planning Cloud.

3. Combine with Runtime Prompts

Use runtime prompt values to evaluate members dynamically.

Cube cube = operation.application.getCube("Plan1")
Dimension scenarioDim = operation.application.getDimension("Scenario", cube)
String rtpValue = operation.getRuntimePrompt("SelectScenario") ?: "Plan"
List scenarios = cube.getEvaluatedMembers("Children($rtpValue)", scenarioDim)
scenarios.each { println "Scenario: $it" }

Tip: Ensure the RTP is defined in the rule.

Use Case: Copy data across scenario children based on user input.

4. Get Members from a Fixed Parent

Retrieve children of a hardcoded parent member.

Cube cube = operation.application.getCube("Plan1")
Dimension accountDim = operation.application.getDimension("Account", cube)
List revenueAccounts = cube.getEvaluatedMembers("Children(Revenue)", accountDim)
revenueAccounts.each { println "Account: $it" }

Tip: Useful for static hierarchies that rarely change.

Use Case: Aggregate revenue sub-accounts for reporting.

5. Exclude Specific Members

Filter out unwanted members using exclusion operators.

Cube cube = operation.application.getCube("SalesPlan")
Dimension entityDim = operation.application.getDimension("Entity", cube)
List activeEntities = cube.getEvaluatedMembers("Descendants(TotalEntity) - ClosedEntities", entityDim)
activeEntities.each { println "Entity: $it" }

Tip: Ensure excluded members exist in the hierarchy.

Use Case: Process only active sales regions in Sales Planning Cloud.

6. Combine Multiple Dimensions

Evaluate members from one dimension and use them in another context.

Cube cube = operation.application.getCube("Plan1")
Dimension entityDim = operation.application.getDimension("Entity", cube)
Dimension periodDim = operation.application.getDimension("Period", cube)
List entities = cube.getEvaluatedMembers("Lev0,Entity", entityDim)
List months = cube.getEvaluatedMembers("Children(YearTotal)", periodDim)
println "Entities: $entities"
println "Months: $months"

Tip: Use in loops to build multi-dimensional grids.

Use Case: Populate a grid with base entities and months for forecasting.

7. Validate Member Existence

Check if a member or its relatives exist in the hierarchy.

Cube cube = operation.application.getCube("SalesPlan")
Dimension productDim = operation.application.getDimension("Product", cube)
List productCheck = cube.getEvaluatedMembers("Children(NewProduct)", productDim)
if (productCheck.isEmpty()) {
    println "No children found for NewProduct"
} else {
    productCheck.each { println "Product: $it" }
}

Tip: Empty list indicates invalid or leaf-level members.

Use Case: Verify new product lines before data load in Sales Planning.

8. Dynamic Substitution Variables

Incorporate substitution variables for flexible member selection.

Cube cube = operation.application.getCube("Plan1")
Dimension yearDim = operation.application.getDimension("Year", cube)
String currentYear = operation.application.getSubstitutionVariable("CurrYear") ?: "FY25"
List years = cube.getEvaluatedMembers("Children($currentYear)", yearDim)
years.each { println "Year: $it" }

Tip: Set variables in Application settings.

Use Case: Adjust planning periods based on current year settings.

Tips and Tricks

Handling Errors

Wrap in try-catch to manage invalid expressions.

try {
    List members = cube.getEvaluatedMembers("InvalidFunc(Entity)", entityDim)
} catch (Exception e) {
    println "Error: ${e.message}"
}

Performance Optimization

Limit scope for large hierarchies.

List entities = cube.getEvaluatedMembers("Children(Entity) AND Lev0,Entity", entityDim)

Note: Use AND to combine filters for faster evaluation.

Key Resources

Oracle EPM Cloud Groovy API Documentation

Disclaimer

This cheatsheet reflects my personal insights, written independently of my employer or Oracle. EPM Cloud features evolve, so verify with Oracle’s official documentation for the latest details.

WeRun Consulting