Member Expansion Functions

Additional commands can be added at the end of a Member Script to help expand the presented results. For example, E#US will return just the US Entity, while E#US.Descendants will return all of the child Entities that fall under the US Entity such as California, Texas, New York, etc.

Member Expansions

To illustrate these expansion concepts, the following examples will use a portion of the Entity structure from GolfStream Corporation, which is a golf supply company. This is the management rollup, but there is also a geographical rollup where Houston rolls up to Texas, Carlsbad rolls up to California, Frankfurt rolls up to Europe, etc.

Total GolfStream
- Clubs
  - NA Clubs
    - Canada Clubs
      - Montreal
      - Quebec City
    - US Clubs
      - Augusta
      - Carlsbad
      - Houston
  - Frankfurt
- Golf Balls
  - Europe Golf Balls
  - NA Golf Balls
- Accessories & Apparel
- Course Management
- Corporate

Add these expansions after a Member name to return the desired Members like this: E#Houston.Ancestors.

TIP: Use square brackets, [ ], to reference any name with a space. For example, E#[NA Clubs].

Member

This lists the Member requested. E#Frankfurt.Member is the same as stating E#Frankfurt.

Base

E#[NA Clubs].Base queries only the base level Entities and excluding any other aggregate Members:

Montreal
Quebec City
Augusta
Carlsbad
Houston

Children

E#Clubs.Children returns the first level children in a flat list:

NA Clubs
Frankfurt

ChildrenInclusive

E#[NA Clubs].ChildrenInclusive returns both NA Clubs and its children:

NA Clubs
- Canada Clubs
- US Clubs

This can be chained together with another .Children statement to see the next level as well. This may also be referred to as Children.Children elsewhere in the documentation.

E#[NA Clubs].ChildrenInclusive.Children returns:

NA Clubs
- Canada Clubs
  - Montreal
  - Quebec City
- US Clubs
  - Augusta
  - Carlsbad
  - Houston

Descendants

E#[NA Clubs].Descendants lists every Member under NA Club in a flat, non-hierarchical list excluding NA Clubs:

Canada Clubs
Montreal
Quebec City
US Clubs
Augusta
Carlsbad
Houston

DescendantsInclusive

E#[US Clubs].DescendantsInclusive lists every Member under US Clubs in a flat, non-hierarchical list including US Clubs:

Augusta
Carlsbad
Houston Heights
South Houston
Houston
US Clubs

Tree

E#[US Clubs].Tree returns the specified Member and all Members below it in a hierarchy:
This expansion automatically includes expandable rows.

US Clubs
- Augusta
- Carlsbad
- Houston

TreeDescendants

E#Clubs.TreeDescendants lists every Member under Clubs in hierarchical tree excluding Clubs:

NA Clubs
- Canada Clubs
  - Montreal
  - Quebec City
- US Clubs
  - Augusta
  - Carlsbad
  - Houston
Frankfurt

TreeDescendantsInclusive

E#[US Clubs].TreeDescendantsInclusive lists every Member under US Clubs in a hierarchical tree including US Clubs:

US Clubs
- Augusta
- Carlsbad
- Houston

Parents

E#Houston.Parents returns the direct Parents of the given Member regardless of how many hierarchies to which the Member belongs:

US Clubs (from the management rollup)
Texas (from the geographical rollup)

The Parent is derived from the Cube View’s POV setting by default. If a Member is used in multiple hierarchies, specify a specific Parent using the following syntax in order to override it:
E#Houston.Base:P#USClubs or E#Houston.TreeDescendants:P#Texas.

NOTE: P# works differently when using it with an expandable Tree filter because that filter processes the children as individual queries, so the P# will only apply to the top-level Members. Use E#Houston.TreeDescendants:P#Texas instead, or specify a Parent on the Cube View’s POV settings.

Ancestors

E#[NA Clubs].Ancestors returns all Members up the chain from NA Clubs:

Total GolfStream
Clubs

Branch

Expand multiple Members of a given expansion by finding specific items and then performing additional expansions on those items. Refer to the Samples Tab in the Member Filter Builder for an example of the syntax.

E#[Total GolfStream].Children.Branch(Find([Clubs]).Children, Find(Golf Balls).Children) returns Total GolfStream’s Children, it then finds Clubs’ and Golf Balls’ Children and returns them in the hierarchy as well.

Total GolfStream
- Clubs
  - NA Clubs
  - Frankfurt
- Golf Balls
  - Europe Golf Balls
  - NA Golf Balls
- Accessories & Apparel
- Course Management
- Corporate

This is also used with Quick Views in order to expand several hierarchies at one time.

Find

Find will apply Member expansions to a nested subset of results:

A#[Income Statement].Descendants.Find(64000).Children will return all of the descendants of the Income Statement as a flat list, but will find account 64000 (i.e. Earnings Before Taxes in this example) and then indent and present that account’s children.

FindAt

This returns a specific Member of a given expansion using a zero-based position index and performing an additional expansion on the specific item. Refer to the Samples Tab in the Member Filter Builder for an example of the syntax.

E#[Total GolfStream].Children.FindAt(1).Children returns Total GolfStream’s Children, locates the Member in the first position, and returns its children as well.

Total GolfStream
- Clubs
- Golf Balls
  - Europe Golf Balls
  - NA Golf Balls
- Accessories & Apparel
- Course Management
- Corporate

First

First will find the first items in the list of results and allow additional Member Filters to be applied:

E#[Total GolfStream].Children.First.Children will list all of the children of Total GolfStream, go to the first Entity (Clubs) and then show its children:

Clubs
- NA Clubs
- Frankfurt
Golf Balls
Accessories & Apparel
Course Management
Corporate

Last

Similar to First, Last will find the last items in the list of results and allow additional Member Filters to be applied.

Keep

Keep will search the results from a Member Filter and only keep certain values:

E#[Total GolfStream].Children.Keep(Clubs, [Golf Balls]).Children will list all of the first level children of Total GolfStream, only keeping Clubs and Golf Balls and then show their children:

Clubs
- NA Clubs
- Frankfurt
Golf Balls
- Europe Golf Balls
- NA Golf Balls

Remove

This will remove some of the Members from the results:

E#[Total GolfStream].Children.Remove(Corporate).Find(Clubs).Children.Find([NA Clubs]).Children will list several Entities and then remove the Corporate Entity from the results:

Clubs
- NA Clubs
  - Canada Clubs
  - US Clubs
- Frankfurt
Golf Balls
Accessories & Apparel
Course Management

List

This will create a list of specific Members:

A#Root.List(63000, 64000) will return these two accounts.

An Indent Level can also be specified when defining a list of Members. Refer to the Samples Tab in the Member Filter Builder for an example of the syntax.

E#[Total GolfStream].List(Clubs.IndentLevel(0), [Golf Balls].IndentLevel(2), [Course Management].IndentLevel(1))

Clubs
- Golf Balls
  - Course Management

Where

The Where clause in a Member Filter will allow further qualification of the results. Use AND, OR and Parentheses to provide detailed conditions for including Members. Use the IN or NotIN qualifiers to see if Members belong to a certain list:

E#Root.Children.Find(US).Children.Find(Michigan, Texas).Children.Where((Name = Flint) Or (Name = Rochester) Or (Name = Dallas)) will return Michigan and Texas and only specific child city Entities

E#Root.Children.Find(US).Children.Find(Michigan, Texas).Children.Where(Name In Flint, Rochester, Dallas) will return Michigan and Texas and only a few child Entities.

See Member Expansion Where Clause later in this section for more examples.

Options

Use this to reference Sub-Cubes and specify how Dimensions should be processed. Options must immediately follow the expansion function for which it is being used. For example, A#19999.Children might return a different list when looking at an extended dimension associated with a different cube.

A#19999.Options(Cube=[Total GolfStream], ScenarioType=Actual, MergeMembersFromReferencedCubes=False)

Combined Expressions

Stack multiple Member Expressions to display Children and Parent Members. The example below is using the List and Find expressions to display Base and Parent Members.

E#[Total GolfStream].List(Clubs, Corporate, Frankfurt).Find(Clubs).Base.Find(Carlsbad).Parents

Clubs

Montreal
Quebec City
Augusta
Carlsbad

California
US Clubs
Total Product Rollup

Houston Heights
South Houston
Frankfurt

Corporate
Frankfurt

For more Member Expansion examples, refer to the Samples Tab in the Member Filter Builder.

Reverse Order Member Expansions

Functionality to support reversing the direction of the order of results from certain member filters are supported in Cube Views and Quick Views. These reverse order selections include ChildrenInclusiveR, TreeDescendantsR, and TreeDescendantsInclusiveR.

In Cube Views, this functionality can be set in the Member Filter Builder using the Member Expansion Functions tab for Rows (and/or) Columns.

In Quick Views, this can be set in Preferences under the Quick View Double-Click Behavior section in the Default Expansion for Rows (and/or) Columns.

This example will demonstrate the TreeDescendantsR being used in a Quick View. This Gross Income view below has been created using Next Level expanison (available in a Quick View):

To change the double-click behavior, select Preferences under the Administration menu. In the Quick View Double-Click Behavior section, select the drop-down list for Default Expansion For Rows and select TreeDescendantsR. The result is the reverse direction of the originating account on expansion for rows when using the double click:

Time Member Expansions

These Member expansions can be added onto a regular time-based Member Script. They can be used in a Cube View or in the Form Template’s Time Filter for ‘Complete Form’ option, so only the time periods needed will be displayed.

AllPriorInYear

This returns all the time periods before the specified time period excluding the specified time for its frequency. T#2012M6.AllPriorInYear, T#WF.AllPriorInYear return the previous periods in that year, but not the specified period.

AllPriorInYearInclusive

This returns all the time periods before the specified time period including the specified time for its frequency. T#2012M4.AllPriorInYearInclusive returns periods 2012M1 through 2012M4.

AllNextInYear

This returns all the time periods after the specified time period not including the specified time for its frequency. T#2012M8.AllNextInYear returns periods 2012M9 through 2012M12.

AllNextInYearInclusive

This returns all the time periods before the specified time period including the specified time for its frequency. T#2012M8.AllNextInYearInclusive returns periods 2012M8 through 2012M12.

Weeks

This returns all the weeks associated with the specified time filter. T#2017M2.Weeks returns all the weeks in M2. T#2017.Weeks returns all the weeks in 2017. If the POV Time is 2017M7, T#POV.Weeks returns all the weeks in M7.

Months

This returns all the months associated with the specified time filter. T#2017.Months returns all the months in 2017. If the POV Time is set to 2017Q2, T#POV.Months returns all the months in Q2.

MonthsInQuarter

This returns the months in the specified quarter. If the Workflow period is 2012M2, T#WF.MonthsInQuarter will return 2012M1, 2012M2, and 2012M3.

MonthsInHalfYear

This returns the months in the half year of the specified period. If the Global period is 2012M2, T#Global.MonthsInHalfYear will return 2012M1 through 2012 M6.

MonthsInYear

This returns all of the months in the year of the specified period. If the POV period is 2012M7, T#POV.MonthsInYear will return 2012M1 through 2012M12.

Quarters

This returns the quarters associated with the specified year. T#2017.Quarters will return 2017Q1, 2017Q2, 2017Q3, 2017Q4.

QuartersInHalfYear

This returns the quarters in the half year of the specified period. If the Workflow period is 2012M3, T#WF.QuartersInHalfYear will return 2012Q1 and 2012Q2.

QuartersInYear

This returns all of the quarters in the year of the specified period. T#2012M7.QuartersInYear will return 2012Q1, 2012Q2, 2012Q3 and 2012Q4.

HalfYears

This returns the half years associated with the specified year. T#2017.HalfYears returns 2017H1 and 2017H2.

HalfYearsInYear

This returns the half years in the year of the specified period. If the Global period is 2012M5, T#Global.HalfYearsInYear will return 2012H1 and 2012H2.

Prior 1-Prior 12

This returns the prior period(s) in relation to the specified period. T#2010M12.Prior12 will return the 12 months prior to 2010M12 not including 2010M12. T#2017W40.Prior12 will return the 12 weeks prior to 2017W40 not including 2017W40.

Next 1-Next 12

This returns the next period(s) in relation to the specified period. T#2010M12.Next12 will return the 12 months after 2010M12 not including 2010M12. T#2017W40.Next12 will return the 12 weeks after 2017W40 not including 2017W40.

Workflow Member Expansions

These are used in Cube Views that are used in Reports, Forms or Dashboards and presented during the Workflow process. These Method Queries can also be used when setting pop-up Parameters when Dashboards and Reports are run:

WFProfileEntities (Entity Dimensions Only)

This returns all Entities associated with this Workflow Profile.

WFCalculationEntities (Entity Dimension Only)

Filters to the Entities encompassed within the Workflow Profile Calculation Filter and are calculated/translated/consolidated when the user clicks Process during Workflow.

WFConfirmationEntities (Entity Dimensions Only)

Filters to the Entities encompassed within the Workflow Profile Confirmation Filter and are checked when the user clicks Confirm during Workflow.

WFTimePeriods

This returns the time period(s) associated with the Workflow Profile. When using the Standard Workflow Tracking Frequency, it returns the single time period associated with the selected Workflow Unit. When using other Workflow Tracking Frequencies, it returns the range of time periods for the selected Workflow Unit between WFStartTime and WFEndTime.

WFCalculationTimePeriods

This returns the time periods needed to be calculated for a Workflow Unit. For example, if a range-based Workflow Unit spans two years, it would return the last period of the first year and the last period of the second year. Executing a calculation using those two time periods would cause all 24 time periods to be calculated because the calculation engine automatically calculates all prior periods in a year. WFCalculationTimePeriods is intended to be used in Data Management Steps and Business Rules.

WFChannelMembers (User Defined Dimensions Only)

This returns the UD Dimension Members associated with the Workflow Channel that is associated with this Workflow Profile. This can be a one-to-many relationship. For example, UD1 represents Cost Centers for this application and three UD1 Members are associated with the Workflow Channel called Engineering. If the Workflow Profile in the Workflow View is associated with the Workflow Channel of Engineering, UD1#Root.WFChannelMembers will return those three UD1 Members.

Member Expansion Where Clause

The Where Clause adds an additional level of filtering after the list of Members has been retrieved. The list of supported fields is as follows:

Name (e.g., (Name StartsWith ‘Sales’))

A#Sales.Descendants(Name Contains '6000') filters the Member list to include only Member names containing ‘6000.’

Description (e.g., (Description Contains ‘Costs’))

A#Root.Descendants(Name Contains 'Costs') filters the Member list to include only account names containing ‘Costs.’

MemberDim

U2#AllProducts.Descendants(MemberDim='HoustonProducts') filters the Member list to the products in the HoustonProducts Dimension under UD2.

HasChildren (e.g., (HasChildren = True)

A#Root.Children(HasChildren=False) filters the Member list to include only the Members that have children.

InUse

A#Root.Children(InUse=True) filters the results to only the Members in use.

AccountType

A#Root.Base(AccountType = Revenue) filters the results to only the Members under the Account Type of Revenue.

Formula

F#Root.TreeDescendants(Formula <> '') filters out Members with an empty value for the Formula property.

User In Security Group Where Clauses

These Where Clauses will restrict the list to only Members the user is allowed to read and/or write to based on the user security settings. OR expressions can be used to refer to multiple properties:

E#[Total GolfStream].Base.where((UserInReadDataGroup = True) or (UserInReadWriteDataGroup = True))

Choices:

UserInReadDataGroup

UserInReadDataGroup2

UserInReadWriteDataGroup

UserInAnyDataSecurityGroup

UserInReadWriteDataGroup2

Text1 through Text8

This uses the Text1 property in the Scenario, Entity, Account, Flow, and UD1 to UD8 Dimensions. There are eight TextN properties in these types of Members that can be used for any need. These Where Clauses will query those fields for their values:

A#Root.Children(Text1='Blue')

A#Root.Children(Text2 StartsWith 'Strategic')

A#Root.Children(Text3 Contains 'Tax')

A#Root.Children(Text4 EndsWith 'Old')

A#Root.Children(Text5 <> '') filters out the Members that have an empty value for the Text5 property.

This is the list of supported operators. Operators such as < are not applicable to the text-based fields.

StartsWith

Contains

DoesNotContain

EndsWith

For more Where Clause examples, refer to the Samples Tab in the Member Filter Builder.