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

Comments

  1. Fusion Infotech21 January 2023 at 21:55

    Hi, i have a slamm query, Is this related to Oracle EBS ? if yes then how?
    link text
    As we provide Oracle EBS implementation this will be helpful for us.

    ReplyDelete

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more