1. Designing with Reports for Oracle Enterprise Performance Management Cloud
  2. Defining Members
  3. Assigning Members
  4. Assigning Members Using Functions
  5. Functions Available Through Member Selection

Functions Available Through Member Selection

Functions can be used as shortcuts in Reports.

The functions listed in Table 9-1 can be selected from the Functions tab in the Select Member dialog box.

Table 9-1 Functions Available Through Member Selection

Function Parameters Needed Description
Ancestors Ancestors of member (Inclusive) Members at all levels above the specified member
Bottom Bottom dimension (Inclusive) All Level 0 members
Children Children of member (Inclusive) Members one level below the specified parent
CurrentPOV CurrentPOV Returns a member for a row or a column from the POV for a dimension.
DynamicTimeSeries DTS member (Time Dimension member)

This applies to the Time dimension only.

Depending on the data source, the following DTS members are available. Only the members enabled at the data source will be displayed.

  • HTD (History to date)

  • YTD (Year to date)

  • STD (Season to date)

  • PTD (Period to date)

  • QTD (Quarter to date)

  • MTD (Month to date)

  • WTD (Week to date)

  • DTD (Day to date)

NOTE: Select a Level 0 Time dimension member or CurrentPOV. Member selection functions are not supported as parameters for the Dynamic Time Series function.

Returns period to date values for the selected Level 0 time dimension member and the selected DynamicTimeSeries member (e.g. QTD, YTD, etc.)

For example, YTD (Apr) returns the sum of January through April.

Descendants Descendants of member (Inclusive) Members at all levels below the specified parent
Except Except (member1, member2, etc.)

Select two, which can be fixed members, CurrentPOV, or other member functions.

Evaluates two selections and removes the items in the first selection that also exist in the second selection.

For example, Except ( Children(Q1), Feb ) returns Jan, Mar.

Generation Generation number Members based on a generation number in a hierarchy
Intersect Intersect (member1, member2, etc.)

Select between two and ten members, which can be fixed members, CurrentPOV, or other member functions.

Member or members that are the result of intersecting all specified selections in the function.

For example, Intersect (Children(Q1), Jan,) returns Jan.

Level Level number Members based on a level number in a hierarchy
Match

Include CurrentPOV (No, Member Name as prefix, Member Name as suffix)

Match with (member name, alias, or both)

Members that match a specified pattern or set of characters.

You can append the Match string with the Current POV selection as either a prefix or suffix by setting Include CurrentPOV to either Member Name as prefix or Member Name as suffix. The default setting is No.

Note: This combination is not available when selecting members for a dimension that is already in the Report or Book POV. It is only supported for Report row and column selections, and in Books in either a Report or Section POV.

For more information, see Example: Using the Match function with CurrentPOV.

Note: You can use wildcard characters such as * and ? in your search, using either leading or trailing wildcards.

Parent Parent of member (Inclusive) Member one level above the specified member
PeriodOffset PeriodOffset (member, offset, secondary dimension, periods below member)

Applies to the Time dimension only

Example: PeriodOffset (Jan, -2, Years) returns November of the previous calendar year, where the year dimension is named "Years".

Note: periods below member is an optional parameter that is used when there are additional Level 0 Time dimension members other than months (for example, Beginning Balance), and you want the period offset on descendant Level 0 members under a certain member only.

Returns members an arithmetic offset (positive or negative) away from the specified member. Applies to the Period/Time dimension spanning months and years, where the Year dimension is selected as part of the function.

Note: The Time and Year dimensions cannot exist in opposite axes. They must both be either in the row or column axis.

Property User Defined Attributes (UDAs) or Associated Attribute dimension.

Operator:

equals (=) or not equals (<>)

NOTE: EPM Cloud data sources do not support the "not equals" (<>) operator for UDAs.

Property Value:

For UDA: select a User Defined Attribute.

For Associated Attribute: select the desired attribute dimension member.

 Returns members based on specified User Defined Attributes (UDAs) or Associated Attribute dimension members.
Range Range (frommember, tomember)

The starting and ending members can be fixed members, CurrentPOV, or a prompt.

 Defines a range of members.

For example, you can use the "Year" dimension to specify all months in the first quarter by selecting "January" as the start member and "March" as the end member.

You can use the Range function with either the RelativeMember or PeriodOffset functions to produce a "rolling" report that displays months before and after the "current month" (For example, three months before through three months after the current month). See Example: Using the Range function to create rolling reports.

Note: If the range frommember occurs after the range tomember (for example: from November to August), the data returned varies based on the data source:

  • Narrative Reporting models will return members in reverse order of the outline (November, October, September,…August).

  • All other cloud EPM data sources will return members in forward order and will wrap when the last member in the hierarchy is reached (November, December, January,…August).
RelativeMember RelativeMember (member,offset, Hierarchy under member)

Example: RelativeMember(Mar,-1) returns February.

Note:

Hierarchy under member is an optional parameter that is used when there are additional hierarchies in the dimension and you want the RelativeMember on descendant Level 0 members under a certain member only.

 Members an arithmetic offset (positive or negative) away from the specified member. Applies to any dimension.
SameGeneration Same generation as member Members of the same dimension and generation as the specified member
SameLevel Same level as member Members from the same dimension and on the same level as the specified member
Siblings Siblings of member (Inclusive) Members with the same parent as the specified member