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.

With this situation in mind, we have created very dynamic Member Lists (MDX Lists) that are based on the standard MDX language. We can now use all of the SET related MDX functions to create a Member list with all of the dynamics of hierarchies, ranges, exclusions while still having the ability to format the lists to the end users preferences. For example, it is possible to make certain levels bold or indented or have the last member of a list formatted as a sum, with the typical bold and overline attributes.

This functionality is not only useful for very large models, but also for specific models such as Hyperion Enterprise derived models. In these applications, you often see accounts that are a subtotal from a range of accounts above itself. With the MDX lists, it is possible to drill down from that account into an account lists based on an MDX list specified as a dynamic range of accounts.

Further in this article you will find out how to create MDX lists and apply MDX Set Functions in CXO-Cockpit.

CONTENT

Example:

What is MDX?

Multidimensional Expressions (MDX) is a query language for OLAP databases, much like SQL is a query language for relational databases.
The MDX language provides a specialized syntax for querying and manipulating the multidimensional data stored in OLAP cubes.

MDX Set Expression

To create an MDX List in CXO-Cockpit we use MDX Set Expressions.

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

To create an MDX List you need to take the following steps:

1. Add a new list from the Manage->Lists Menu

2. In the list type select MDX:

3. Choose a dimension and Create an MDX statement in the statement filed:

After writing the statement, you may check the syntax by clicking on the check button. Also, on the right side, you will see the results from the query.

MDX Set - Main functions and Examples

Function AllMembers

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

[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]

[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]

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

Syntax: Member_Expression : Member_Expression

Parameters: Member_Expression - A valid Multidimensional Expressions (MDX) that returns a member.

Return Value: A set that contains the specified members and all members between the specified members.

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

[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]

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]

BOTTOMCOUNT(
	 {   [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

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

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

 BOTTOMPERCENT(
 	FILTER (
 		[A01].[A01].[Level 03].Members,  
 		NOT ISEMPTY ([MEA].[MEA].[[None]]])
 	) ,
 	10 , 
 	[MEA].[MEA].[[None]]]
 )

Function BottomSum

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:

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:

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

BOTTOMSUM(
	FILTER (
		[A02].[A02].[Level 03].Members, 
		NOT ISEMPTY([MEA].[MEA].None])
	) ,
	1000, 
	([MEA].[MEA].[[None]]])
)

Function CrossJoin

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]:

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:

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 a list of the Decendants of the Account dimension [ACC] for the hierarchy [ACC] for the Member [NetProfit] for the SET [Level 02]:

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:

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]:

Descendants ([ACC].[ACC].[NetProfit], 0,SELF_AND_AFTER)

Optional DESC Flags:

Self
After
Before
Self_and_After
Self_and_Before
Self_Before_After

Function Filter

The Filter function returns a set based on the supplied filter condition. Syntax: FILTER( «Set», «Search Condition» )

Example 1 - 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:

  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 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:

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 a list of the members of the Account dimension [ACC] for the hierarchy [ACC] for the SET [Level 02]:

[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]:

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 % 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:

TOPPERCENT( 
	{ [A01].[A01].AllMembers} ,
	10 , 
	([MEA].[MEA].[[None]]])
)

Example 2 - 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:

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 where the value for the member of the set is NOT Empty:

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 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:

TOPSUM( 
	{ [A02].[A02].AllMembers} ,
	100000 , 
	([MEA].[MEA].[[None]]])
)

Example 2 - 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:

TOPSUM( 
	{ [A02].[A02].[Level 03].AllMembers} ,
	5000000, 
	([MEA].[MEA].[[None]]])
)

Example 3 - 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 where the value for the member of the set is NOT Empty:

TOPSUM( 
	FILTER (
		[A02].[A02].[Level 03].Members, 
		NOT ISEMPTY([MEA].[MEA].[[None]]])
	) ,
	1000, 
	([MEA].[MEA].[[None]]])
)

Use of Variables

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)>>]