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. This expansion can be combined with another member expansion to create an inclusive list. This is an example of using Member for a Children Inclusive list: 

E#[Totall GolfStream].Member.Children returns the same as E#[Total GolfStream].ChildrenInclusive:

• Total GolfStream

  • Clubs

  • Golf Balls

  • Accessories & Apparel

  • Course Management

  • Corporate

This is an example of using Member for a Parent Inclusive list: 

E#Clubs.Member.Parents returns Clubs and its parent, Total GolfStream.

This is an example of using Member for an Ancestors Inclusive list: 

E#[NA Clubs].Member.Ancestors returns NA Clubs and its ancestors, Clubs and Total GolfStream.

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 allows further qualification of the results. It adds an additional level of filtering after the list of members has been retrieved. Use cases are provided below.

You can filter members where a property is a certain value:

• X#MyMember.Where(PropertyName= 'Value')

• E#[Total GolfStream].Descendants.Where(IsIC=True)

You can add other member expansions, such as .Children, before and after the Where clause: 

• X#MyMember.MemberExpansion.Where(PropertyName='Value').MemberExpansion

• A#Sales.Descendants.Where(Name Contains '60000').Children

You can filter for multiple values using detailed conditions within .Where() using AND, OR with additional parentheses: 

• X#MyMember.Where((PropertyName='Value') OR (PropertyName='Value'))

• A#60999.Descendants.Where((Name Contains '60000') OR (Name Contains '41000'))

• E#Root.Children.Find(US).Children.Find(Michigan, Texas).Children.Where((Name=Flint) OR (Name=Rochester) OR (Name=Dallas)). This returns Michigan and Texas and only specific child city Entities.

You can use the IN or NotIN qualifiers to return members in or not in a list: 

• X#MyMember.Where(PropertyName In '60000')

• A#60999.Descendants.Where(Name In'60000', '41000') returns 60000 as it is a descendant of 60999. 41000 is not returned as it is not a descendant of 60999.

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

You can also add a filter to another member expansion, such as .Descendants, with or without .Where: 

• A#MyMember.MemberExpansion(MyPropertyName='MyValue')

• A#Sales.Descendants.Where(Name='60000')

• A#Sales.Descendants.(Name='60000')

You can combine criteria within the Where clause, such as filtering for different properties: 

A#Sales.Descendants.Where((HasChildren=True) AND (Name Contains '60000'))

You can use operators other than = within the Where Clause. Below is the list of supported operators: 

• >=

• <==

• <>

• =

• >

• <

• StartsWith

• Contains

• DoesNotContain

• EndsWith

Operators with greater than or less than are not applicable to the text-based fields: 

• >=

• <=

• >

• <

See Member Expansion Where Clause for examples using common member properties.

Options

Use this to reference sub-cubes and specify how dimensions should be processed. Options must immediately follow the expansion function it is being used for.

For example, A#MyAccount.Base might return a different list when looking at an extended dimension associated with a different cube. Therefore, the cube and Scenario Type can be specified using Options: 

A#MyAccount.Base.Options(Cube=CubeName, ScenarioType=ScenarioTypeName, MergeMembersFromReferencedCubes=False)

The following example returns account 19999's children from the Total GolfStream cube and Actual Scenario Type:

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

If you drill to base members in a Cube View or Quick View, you can use Options to ensure you are returning the desired members. For example, A#60000.Base could return different members due to extended dimensions. Using Options ensures you are returning the desired members: 

• A#60000.Base.Options(Cube=Houston, ScenarioType=Actual, MergeMembersFromReferencedCubes=False)

• A#60000.Base.Options(Cube=NewYork, ScenarioType=Actual, MergeMembersFromReferencedCubes=False)

The following example returns tree descendants inclusive of the account based on the workflow cube and Scenario Type property of the workflow:

A#MyAccount.TreeDesendantsInclusive.Options(Cube=|WFCube|, ScenarioType=XFMemberProperty(DimType=Scenario,Member=|WFScenario|, Property=ScenarioType),MergeMembersFromReferencedCubes=False)

Combined Expressions

You can stack multiple Member Expansions to display additional members, add conditions, remove members, and more.

This example shows Children of Children which display the children members and their children members.

E#[Total GolfStream].Children.Children returns:

• Total Golf Stream

  • Clubs

    • NA Clubs

    • Frankfurt

  • Golf Balls

    • Europe Golf Balls

    • NA Golf Balls

• Accessories & Apparel

• Course Management

• Corporate

This example uses the List and Find expressions to display base and parent members:

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

• 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.

In the below example, the Houston Workflow Profile is assigned the Houston Heights and South Houston Entity members. If the Workflow POV is set to Houston, then E#Root.WFProfileEntities returns Houston Heights and South Houston.

WFCalculationEntities (Entity Dimension Only) 

This filters to the Entities encompassed within the Workflow Profile Calculation Filter, which are calculated, translated, and consolidated when the user clicks Process during Workflow.

When used as a member expansion, this expression returns all Entities defined as part of the Calculation Definitions for the selected Workflow Unit.

In the below example, the Houston Workflow Profile defines Houston and the Assigned Entities, South Houston and Houston Heights, within Calculation Definitions. E#Root.WFCalculationEntities returns Houston, South Houston, and Houston Heights.

WFConfirmationEntities (Entity Dimensions Only)

This filters to the Entities encompassed within the Workflow Profile Confirmation Filter, which are checked when the user clicks Confirm during Workflow.

In the below example, the Houston Workflow Profile has the Confirmed Filter selected for the Assigned Entities, South Houston and Houston Heighs, within the Calculation Definitions.

E#Root.WFConfirmationEntities returns South Houston and Houston Heights.

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.

In the below example, the Actual scenario member has a Workflow Tracking Frequency of All Time Periods in a monthly application. If the Workflow POV is Actual and 2024M3 then T#Root.WFTimePeriods returns 2024M3.

In the below example, the Budget_Quarterly scenario member has a Workflow Tracking Frequency of Quarterly. If the Workflow POV is Actual and 2024Q2 then T#Root.WFTimePeriods returns 2024M4, 2024M5, and 2025M6.

In the below example, the ForecastM1 scenario member has a Workflow Tracking Frequency of Range, WFStartTime of 2024M1, and WFEndTime of 2024M12. If the Workflow POV is ForecastM1 and 2024M1, then T#Root.WFTimePeriods returns 12 time members, 2024M1 through 2024M12.

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.

In the below example, the Actual scenario member has calculations monthly. If the Workflow POV is Actual and 2024M3, then T#Root.WFCalculationTimePeriods returns 2024M3.

In the below example, the Forecast_TwoYears scenario member has a range Workflow Unit that spans two years from 2024M1 to 2025M12. If the Workflow POV is Forecast_TwoYears, then T#Root.WFTimePeriods returns 2024M12 and 2025M12 as calculations are needed for each year.

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. See Member Expansions > Where for more information.

Below are examples of Where clauses filtering for common member properties, however, you can filter on additional member properties that are not shown below: 

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

Filter for members based on their Name property.

A#Sales.Descendants.Where(Name Contains '6000') filters the Member list to include only Member names containing ‘6000.’ This could also be written as: 

A#Sales.Descendants(Name StartsWith '6000')

The following examples are based on if an entity hierarchy was the following: 

• Total GolfStream

  • Clubs

    • NA Clubs

    • Frankfurt

  • Golf Balls

    • Europe Golf Balls

    • NA Golf Balls

  • Accessories & Apparel

  • Course Management

  • Corporate

E#[Total GolfStream].Children.Where((Name Contains 'Balls') OR (Name='Clubs'))

This returns the children of Total GolfStream if the member name contains Balls or equals Clubs, which is: 

• Clubs

• Golf Balls

Other Member Expansions can also be added after Where Clause expansions.

E#[Total GolfStream].Member.Children.Where((Name Contains Balls) OR (Name=Clubs)). Children

This returns Total GolfStream due to E#[Total GolfStream].Member. It also returns its children member where Name contains Balls or equals Clubs, and those member's children, which is:

• Total GolfStream

  • Clubs

    • NA Clubs

    • Frankfurt

  • Golf Balls

    • Europe Golf Balls

    • NA Golf Balls

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

Filter for members based on their Description property.

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

MemberDim 

Filter for members within certain dimensions.

A#MyAccount.Base.Where(MemberDim=DimensionName)

A#MyAccount.Base.Where(MemberDim=|WFAccountDim||) filters the member list to the accounts within the workflow's Account Dimension.

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

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

Filter for members with or without children.

A#Root.Children.Where(HasChildren=False) filters the Member list to include only the members without children.

A#Root.Children.Where(HasChildren=True) filters the Member list to include only the members with children.

InUse 

Filter for Entity, Account, Flow and UD1-UD8 members in use or not in use.

A#Root.Children(InUse=True) filters the results to only include the members with the In Use member property set to True.

AccountType

Filter Account members based on their Account Type.

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

Currency

Filter for entity members of a specific currency.

E#[Total GolfStream].Descendants.Where(Currency=EUR) filters the results to only include descendants of Total GolfStream with the currency of EUR.

IsIC

Filter for intercompany members based on the Is IC Entity member property and the Is IC Account member property.

E#[Total GolfStream].Descendants.Where(IsIC=True) filters the results to only include descendants of the Total GolfStream with the Is IC Entity member property set to True.

A#Sales.Descendants.Where(IsIC=True) filters the results to only include descendants of Sales with the Is IC Account member property set to True.

Formula

Filter for Scenario, Account, Flow, and UD1-UD8 members with Member Formulas.

F#Root.TreeDescendants(Formula <> '') filters out Members with an empty value for the Formula property so that only members with Member Formulas show.

User In Security Group Where Clauses

These Where Clauses will restrict the list to only Members the user is allowed to read and 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

UserInReadWriteDataGroup2

UserInAnyDataSecurityGroup

TextN (Text1 through Text8) 

This uses the TextN 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.

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