| Table of Contents | ||||
|---|---|---|---|---|
|
Introduction
For most situations current Member List functionality in all its varieties (normal, dynamic) is sufficient. However, in some situations, especially with larger databases and for member lists with multiple dimensions, it can lead to a situation where Normal (Fixed) Member Lists can be difficult to maintain.
...
A MDX Set Expression returns a number of members from one or more dimensions. Set expressions can be used on one of the Axis in an MDX query.
MDX Set Expression can be specified enumerating the tuples, e.g. {([Measures].[Sales], [Time].[Fiscal].[2006]), ([Measures].[Sales], [Time].[Fiscal].[2007])} or returned by MDX function or operator, e.g. Crossjoin, Filter, Order, Descendants etc.
MDX Set Components
The following components are used in MDX Set Functions:
- Tuple - is an ordered collection of one or more members from different dimensions. Tuples can be specified enumerating the members, e.g. ([Time].[Fiscal].[Month].[August], [Customer].[By Geography].[All Customers].[USA], [Measures].[Sales]) or returned by an MDX function, e.g. .Item.
- Dimension/Hierarchy - Dimension is a dimension of a cube. It can be specified by its unique name, e.g. [Time] or it can be returned by an MDX function, e.g. .Dimension. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. [Time].[Fiscal] or it can be returned by an MDX function, e.g. .Hierarchy. Hierarchies are contained within dimensions.
- Level - is a level in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month] or it can be returned by an MDX function, e.g. .Level.
- Member - is a member in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month].[August 2006], by qualified name, e.g. [Time].[Fiscal].[2006].[Q2].[August 2006] or returned by an MDX function, e.g..PrevMember, .Parent, .FirstChild etc. Note that all members are specific to a hierarchy.
- Variables - To specify the use of a variable in the set components use the placeholder << ... >> for example in the case of an @DIM
...
- variable <<@YER_
...
- Current>> or in the case of an @@CUR variable <<@@CUR(DIM)>> in the case of @@CUR the value will be as selected in the POV or derived from the Report Dimensions.
How to create MDX Lists in CXO-Cockpit
...
The AllMembers function returns a set that consists of all members of the supplied level. Syntax: «Level».ALLMEMBERS, «Hierarchy».ALLMEMBERS.
*Example 1 - * Return a list of all members from the Account dimension ACC for the Account Hierarchy ACC
| Panelcode |
|---|
[ACC].[ACC].AllMembers |
Dimension = ACC Hierarchy = ACC Level = Level02
*Example 2 -* Return a list of all members from the Account dimension [ACC] for the Account hierarchy [ACC] and the level [Level02]
| Panelcode |
|---|
[ACC].[ACC].[Level 02].AllMembers |
*Example 3 -* Return a list of all members from the Account dimension [ACC] for the hierarchy [ACC] for the levels 02 and 03 [Level02] [Level03]
| Panel |
|---|
[ACC].[ACC].[Level 02].AllMembers + [ACC].[ACC].[Level 03].AllMembers |
Function : (Range) (MDX)
This function performs a set operation that returns a naturally ordered set, with the two specified members as end points, and all members between the two specified members included as members of the set.
...
Remarks: Both parameters must specify members within the same level and hierarchy of a given dimension. If both parameters specify the same member, the : (Range) operator returns a set that contains just the specified member. If the first parameter is Null, then the set contains all members from the beginning of the level of the member specified in the second parameter, up to and including that member. If the second parameter is Null, then the set contains all members from the member specified in the first parameter, up to and including the last member on the same level.
Example 1
| Panelcode |
|---|
[a01].[a01].[P3000_Phones]:[a01].[a01].[P7000_Phones] |
Function BottomCount
The BottomCount function sorts a set in ascending order, then returns a specified number of members with the lowest values. Syntax: BOTTOMCOUNT( «Set», «Count»[, «Numeric Expression»] )
*Example 1 -* Return a list of the bottom 10 members (lowest values) from the Analytical dimension [A01] for the hierarchy [A01] for the SET AllMembers, where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member [Rolling]
| Panelcode |
|---|
BOTTOMCOUNT({ [A01].[A01].AllMembers} , 10 , ([MEA].[MEA].[Rolling]) ) |
*Example 2 -* Return a list of the bottom 10 members (lowest values) from the Analytical dimension [A01] for the hierarchy [A01] for the SET Level02 [Level02] AllMembers, where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member [Rolling]
| Panelcode |
|---|
BOTTOMCOUNT({ A01.A01.Level 02 { [A01].[A01].[Level 02].AllMembers} , 10 , ([MEA].[MEA].[Rolling]) ) |
Function BottomPercent
The BottomPercent function sorts a set in ascending order, then returns tuples from the bottom of the set with the lowest values whose cumulative total is less than or equal to the supplied percentage. Syntax: BOTTOMPERCENT( «Set», «Percentage», «Numeric Expression» )
*Example 1 -* Return a list of the bottom 10% of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET AllMembers where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None
| Code Block |
|---|
BOTTOMPERCENT( { [A01].[A01].AllMembers} , 10 , ([MEA].[MEA].[[None]]]) ) |
*Example 2 -* Return a list of the bottom 10% of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET Level02 [Level02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None
| Code Block |
|---|
BOTTOMPERCENT(
{ [A01].[A01].[Level 02].AllMembers} ,
10 ,
([MEA].[MEA].[[None]]])
)
|
*Example 3 -* Return a list of the bottom 10% of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET Level02 [Level02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None and where the value for the member of the set is NOT Empty
| Code Block |
|---|
BOTTOMPERCENT( FILTER ( [A01].[A01].[Level 03].Members, NOT ISEMPTY ([MEA].[MEA].[[None]]]) ) , 10 , [MEA].[MEA].[[None]]] ) |
...
The BottomSum function returns a set that is sorted in ascending order. The cumulative sum of this set is less than or equal to the supplied value for the supplied measure. Syntax: BOTTOMSUM( «Set», «Value», «Numeric Expression» )
*Example 1 -* Return a list of the bottom x members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET AllMembers where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
BOTTOMSUM(
{ [A02].[A02].AllMembers} ,
100000 ,
([MEA].[MEA].[[None]]])
)
|
| * |
Example 2 -
...
Return a list of the bottom 10 members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET Level03 [Level03] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
BOTTOMSUM(
{ [A02].[A02].[Level 03].AllMembers} ,
5000000,
([MEA].[MEA].[[None]]])
)
|
*Example 3 -* Return a list of the bottom 10 members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET Level02 [Level 02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None and where the value for the member of the set is NOT Empty
| Code Block |
|---|
BOTTOMSUM( FILTER ( [A02].[A02].[Level 03].Members, NOT ISEMPTY([MEA].[MEA].None]) ) , 1000, ([MEA].[MEA].[[None]]]) ) |
...
The Crossjoin function returns the cross product of one or many sets. Syntax: CROSSJOIN( «Set1», «Set2» )
*Example 1 -* Return a list of the cross product of the Analytical dimension [A01] for the hierarchy [A01] for the SET [Level 03] combines with the Analytical dimension [A02] for the hierarchy [A02] for the SET [Level 02]:
| Panelcode |
|---|
CROSSJOIN( [A01].[A01].[Level 03].Allmembers , [A02].[A02].[Level 02].Allmembers) |
*Example 2 -* An alternative way of writing a CrossJoin is to use the * symbol SO here Return a list of the cross product of the Analytical dimension [A01] for the hierarchy [A01] for the SET [Level 03] combines with the Analytical dimension [A02] for the hierarchy [A02] for the SET [Level 02] Filtering out Empty Values:
| Panelcode |
|---|
NON EMPTY ( [A01].[A01].[Level 03].Allmembers * [A02].[A02].[Level 02].Allmembers) |
Function Descendants
The Descendants function returns a set of descendants of a supplied member at a specified level or distance. DESCENDANTS( «Member»[, «Level»[, «Desc_flags»]] ), DESCENDANTS( «Member»,«Distance»[, «Desc_flags»] )
Example 1 - Return Return a list of the Decendants of the Account dimension [ACC] for the hierarchy [ACC] for the Member [NetProfit] for the SET [Level 02]:
| Panelcode |
|---|
Descendants ([ACC].[ACC].[NetProfit], [ACC].[ACC].[Level 02]) |
*Example 2 -* Return a list of the Decendants of the Account dimension [ACC] for the hierarchy [ACC] for the Member [NetProfit] using the distance method ie 1 level away from the specified member in the given Hierarchy:
| Panelcode |
|---|
Descendants ([ACC].[ACC].[NetProfit], 1) |
*Example 3 -* Return a list of itself and all of the Decendants of the Account dimension [ACC] for the hierarchy [ACC] for the Member [NetProfit]:
| Panelcode |
|---|
Descendants ([ACC].[ACC].[NetProfit], 0,SELF_AND_AFTER) |
Optional DESC Flags:
- Self
- After
- Before
- Self_and_After
- Self_and_Before
- Self_Before_After
...
The Filter function returns a set based on the supplied filter condition. Syntax: FILTER( «Set», «Search Condition» )
Example 1 - Return Return a list of the members of the Account dimension [ACC] for the hierarchy [ACC] for the Given Measure dimension [MEA] hierarchy [MEA] member [None] where the value is greater than 100000:
| Code Block |
|---|
FILTER([ACC].[ACC].Members, [MEA].[MEA].[[None]]] >> 100000) |
Function Hierarchize
The Hierarchize function orders the members of a set in a hierarchy. Syntax: HIERARCHIZE( «Set»[, POST] )
...
The Hierarchize function organizes the members of specified set into hierarchical order. The function always retains duplicates.
If POST is not specified, the function sorts members in a level in their natural order. Their natural order is the default ordering of the members along the hierarchy when no other sort conditions are specified. Child members immediately follow their parent members.
If POST is specified, the Hierarchize function sorts the members in a level using a post-natural order. In other words, child members precede their parents.
Example 1 - Return Return the descendants of the dimension [A01] hierarchy {A01] for the member [Total Products] for both 1 level away and 2 levels away and sort the members so that child members precede their parent:
| Panelcode |
|---|
Hierarchize(DESCENDANTS([A01].[A01].[TotalProducts], 2) + DESCENDANTS([A01].[A01].[TotalProducts], 1), POST) |
Function Members (Set)
The Members(Set) function returns the set of members in a dimension, level, or hierarchy. Syntax: «Hierarchy».MEMBERS, «Level».MEMBERS
Example 1 - Return Return a list of the members of the Account dimension [ACC] for the hierarchy [ACC] for the SET [Level 02]:
| Panelcode |
|---|
[ACC].[ACC].[Level 02].Members |
Function TopCount
The TopCount function sorts a set in descending order, then returns a specified number of members with the highest values. Syntax: TOPCOUNT( «Set», «Count»[, «Numeric Expression»] )
*Example 1 -* Return a list of the top 10 members (by value) from the Analytical dimension [A01] for the hierarchy [A01] for the SET {Level02] for the Measure [MEA] hierarchy [MEA] Member [None]:
| Code Block |
|---|
TOPCOUNT(
{ [A01].[A01].[Level 02].AllMembers} ,
10 ,
([MEA].[MEA].[[None]]])
)
|
Function TopPercent
Description: The TopPercent function sorts a set in descending order, then returns tuples from the top of the set with the highest values whose cumulative total is greater than or equal to the supplied percentage. Syntax: TOPPERCENT( «Set», «Percentage», «Numeric Expression» )
*Example 1 -*Return a list of the top 10 % 10 % of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET AllMembers where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
TOPPERCENT(
{ [A01].[A01].AllMembers} ,
10 ,
([MEA].[MEA].[[None]]])
)
|
Example 2 - Return Return a list of the top 10% of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET Level02 [Level02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
TOPPERCENT(
{ [A01].[A01].[Level 02].AllMembers} ,
10 ,
([MEA].[MEA].[[None]]])
)
|
*Example 3 -*Return a list of the top 10% of members (cumulative total of values less than or equal to 10% of the total) from the Analytical dimension [A01] for the hierarchy [A01] for the SET Level02 [Level02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None and None and where the value for the member of the set is NOT Empty:
| Code Block |
|---|
TOPPERCENT(
FILTER (
[A01].[A01].[Level 03].Members,
NOT ISEMPTY ([MEA].[MEA].[[None]]])
) ,
10 ,
[MEA].[MEA].[[None]]]
)
|
Function TopSum
The TopSum function returns a set that is sorted in descending order. The cumulative sum of this set is greater than or equal to the supplied value for the supplied measure. Syntax: TOPSUM( «Set», «Value», «Numeric Expression» )
Example 1 - Return Return a list of the top x members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET AllMembers where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
TOPSUM(
{ [A02].[A02].AllMembers} ,
100000 ,
([MEA].[MEA].[[None]]])
)
|
Example 2 - Return Return a list of the top x members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET Level03 [Level03] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None:
| Code Block |
|---|
TOPSUM(
{ [A02].[A02].[Level 03].AllMembers} ,
5000000,
([MEA].[MEA].[[None]]])
)
|
Example 3 - Return Return a list of the top x members (lowest values) from the Analytical dimension [A02] for the hierarchy [A02] for the SET Level02 [Level 02] AllMembers for the where the numeric expression or value being evaluated is from the Measure [MEA] dimension hierarchy [MEA] member None and None and where the value for the member of the set is NOT Empty:
| Code Block |
|---|
TOPSUM( FILTER ( [A02].[A02].[Level 03].Members, NOT ISEMPTY([MEA].[MEA].[[None]]]) ) , 1000, ([MEA].[MEA].[[None]]]) ) |
...
Variables - To specify the use of a variable in the set components use the placeholder << ... >> for example in the case of an @DIM variable <<@YER_Current>> or in the case of an @@CUR variable <<@@CUR(DIM)>> in the case of @@CUR the value will be as selected in the POV or derived from the Report Dimensions.
Example @DIM variable
[ENT].[ENT].[<<@ENT_Top>>]
Example @@CUR variable
[ENT].[ENT].[<<@@CUR(ENT)>>]