Automating Smart List Updates in Oracle PBCS Without Using "Create from Hierarchy"

Automating Smart List Updates in Oracle PBCS Without Using "Create from Hierarchy"

In Oracle Planning and Budgeting Cloud Service (PBCS), Smart Lists are a powerful way to present users with predefined selections. However, using the "Create from Hierarchy" option can introduce security issues, especially when the planner doesn't have access to other members. To mitigate this, we can dynamically update Smart Lists using Groovy scripting—without relying on the hierarchy creation feature.

This post walks through an automated approach to update a custom Smart List from a hierarchy, using numeric member names as Smart List entry IDs. This method ensures that updates reflect the latest hierarchy structure.

🔍 Why Avoid "Create from Hierarchy"?

• Restricts members which user doesn't have access to.

Instead, we use Groovy to manually extract and write Smart List entries, giving us full control over what gets included.

🧠 Key Concept: Numeric Department Names as IDs

In our case, department names are numeric, which makes them ideal candidates for Smart List entry IDs. This eliminates the need for additional mapping and ensures consistency across forms and rules.

Though not straightforward we may be able to use this approach for other dimensions for which member names are not numberic, in that case we will have to maintain the ID and handle it efficiently in business rule.

🛠️ Groovy Script Overview

Inspired by EPM Radiance's Groovy Cheat Sheet, the script performs the following:

1. Fetches evaluated members from the Department dimension using @Relative("9999", 0).
2. Retrieves aliases for each member from a custom dimension.
3. Writes a CSV file in the Smart List import format.

📄 Sample Code Snippet

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

String customgetAlias(String dimension, String member) {
    Cube cube = operation.application.getCube('CubeName')
    Dimension currDim = operation.application.getDimension(dimension, cube)
    Member mem = currDim.getMember(member)
    return mem.getAlias("Default")
}

List<String> essbaseFunctions = getMembers('Department', '@Relative("9999",0)')

csvWriter('CustomPeriodSmartList.csv').withCloseable() { out ->
    out.writeNext "SmartList Name","Operation","Label","Display Order","Missing Label","Use Form Missing Label","Member Selection","Entry ID","Entry Name","Entry Label","Automatically generate ID"
    out.writeNext "Custom_Dim_List","addsmartlist","Dept_List","id","","TRUE","","","","","FALSE"
    essbaseFunctions.each {
        out.writeNext "Custom_Dim_List","addentry","","","","","","$it","_$it","${it} - ${customgetAlias("Period", it)}",""
    }
}

🔄 Automating the Update

Once the file is generated the Import Metadata job for SmartList can be scheduled to run daily using EPM Automate or a PBCS Job Scheduler, ensuring your Smart List always reflects the latest hierarchy without manual intervention.

✅ Benefits of This Approach

• Security: Only explicitly included members are added.
• Automation: No manual updates required.
• Flexibility: Customize labels, aliases, and entry formats.
• Control: Avoids unintended exposure of hierarchy members.

📌 Final Thoughts

By leveraging Groovy scripting and avoiding the "Create from Hierarchy" option, you can maintain secure, dynamic, and automated Smart Lists in Oracle PBCS. This approach is especially effective when your dimension members (like departments) are numeric and can double as Smart List IDs.