Top 5 EPM Report Member Functions

When building Reports in Oracle Cloud EPM Planning or FCC, member functions are an incredibly useful way to make reports as user-friendly as possible. Including functions can also help to make reports more dynamic and reduce any required report maintenance going forward.

Functions are accessed via the dropdown at the bottom of the Member Selector:

Here are my top 5 member functions to use when creating EPM reports:

1. CurrentPOV

Purpose: References the current point of view selection for a dimension

Example: Allow the selected Scenario to be visible both in the POV and on the report columns

Syntax: CurrentPOV

Benefits:

• Allows the user to change the selection at run-time, making the report more dynamic
• CurrentPOV can be referenced in other functions to reduce required selections
• POV choices can be restricted to only valid selections

2. Bottom

Purpose: Retrieve the level 0 relatives of a selected member

Example: To see all bottom level account codes only

Syntax: Bottom Members of [MemberParam] (Inclusive), where:

• MemberParam is a selected member to retrieve the bottom level for
• (inclusive) is optional and will include the selected member

Benefits:

• Replicates Ilvl0descendants() form functionality
• Reduces number of members on the report to only those necessary
• Will benefit report performance if all stored bottom level

3. Except

Purpose: To remove a set of members from another set of selected members

Example: If you need all descendants of Total Cost Centre, apart from the Central Cost Centres

Syntax: Except([Member Set 1],[Member Set 2]), where:

• Member Set 1 = the full member set to include (e.g. Descendants of Total Cost Centre)
• Member Set 2 = the member set to exclude from Set 1 (e.g. Descendants of Central)

Benefits:

• Keeps the selection dynamic so new members will automatically be included, rather than having to select the valid members only
• Reduces number of members on the report to only those necessary

4. PeriodOffset

Purpose: Update a period based on another period selection

Example: If column A shows the CurrentPOV period and column B needs to show the prior period then PeriodOffset can be used to dynamically update the prior period based on the POV selection

Syntax: PeriodOffset of CurrentPOV -1

Benefits:

• Reduces required selections for users
• Dynamically updates period based on another period

5. Match

Purpose: Find a member by matching criteria based on another member

Example: If you select the period in the POV and need to then display the selected period (e.g. Jul) alongside the YTD period (Jul YTD)

Syntax: Members matching CurrentPOV Member Name + YTD Member Name or Alias

Benefits:

• Reduces required selections for users
• Ensures selections are always in line and logical