Show TOC

Function documentationMDX Expressions for Sorting and Ranking Locate this document in the navigation structure

 

MDX is a query language used with Microsoft Analysis Services (also know as MS OLAP). It supports the definition and manipulation of multidimensional objects and data. For more information about the MDX language, go to www.microsoft.com and search for Analysis Services. This topic explains some MDX expressions that are commonly used in Planning and Consolidation.

Note Note

Some of the report wizard templates have sorting and ranking built in. The control panel in the templates automatically creates the correct MDX queries to run against the database.

End of the note.

Activities

You can use the following MDX expressions with Set expressions for sorting and ranking:

  • TopCount/BottomCount

  • TopPercent/BottomPercent

  • TopSum/BottomSum

  • Order

In all the sorting and ranking expressions, the Numeric expression must include all the dimensions in the application. The Measures dimension must be specified as Measures:member when using a measure other than PERIODIC.

TopCount/BottomCount

This MDX expression returns a list of top (or bottom) performing members from the dimension specified in the Set parameter.

Example Example

This example returns the top five performing Entity members. The members are returned based on the calculation of the numeric expression. In this example, the numeric expression looks at the numeric value for the intersection of the time period 2001.TOTAL, the account REVENUE, the currency USD and returns the top 5 members.

="TopCount(AddCalculatedMembers(Entity:members),5,(Entity.CurrentMember,2001.TOTAL,REVENUE,ACTUAL,USD,PERIODIC))"

End of the example.
Syntax

TopCount(<Set>, <Count>[, <Numeric Expression>])

BottomCount(<Set>, <Count>[, <Numeric Expression>])

The TopCount function sorts a set according to the value of <Numeric Expression> and returns the top <Count> members, where <Count> is a numeric expression. The BottomCount function sorts a set according to the value of <Numeric Expression> and returns the bottom <Count> members, where <Count> is a numeric expression.

TopPercent/Bottom Percent

This MDX expression behaves the same as the TopCount expression except that the members returned are based on a percentage.

Example Example

This example returns the Entity members that make up the top 50% of Revenue in USD, for the time period 2001.TOTAL.

="TopPercent(AddCalculatedMembers(Entity:members),50,(Entity.CurrentMember,2001.TOTAL,REVENUE,ACTUAL,USD,PERIODIC))"

End of the example.
Syntax

TopPercent(<Set>, <Percentage>, <Numeric Expression>)

BottomPercent(<Set>, <Percentage>, <Numeric Expression>)

The TopPercent function sorts a set using <Numeric Expression> and returns the top n elements whose cumulative total of <Numeric Expression> is at least <Percentage>. <Percentage> is a numeric expression. The BottomPercent function sorts a set specified in «Set» and returns the specified number of bottommost elements whose cumulative total of <Numeric Expression> is at least <Percentage>. <Percentage> is a numeric expression.

TopSum/BottomSum

TopSum sorts a set and returns the topmost elements whose cumulative total is at least a specified value. BottomSum sorts a set using a numeric expression and returns the specified number of bottommost elements whose sum is at least a specified value.

Example Example

="TopSum(AddCalculatedMembers(Entity:members),500000,(Entity.CurrentMember,2001.TOTAL,REVENUE,ACTUAL,USD,PERIODIC))"

End of the example.
Syntax

TopSum(<Set>, <Value>, <Numeric Expression>)

BottomSum(<Set>, <Value>, <Numeric Expression>)

The TopSum function sorts on <Numeric Expression> and picks up the top n (the smallest number possible) elements such that their sum is at least <Value>. The BottomSum function sorts on <Numeric Expression> and picks up the specified number of bottommost (the smallest number possible) elements such that their sum is at least <Value>.

Order

The Order formula returns members in a specified order.

Example Example

This example returns Entity members, in descending order, based on their Revenue for 2001.TOTAL.

Order(AddCalculatedMembers(Entity:members),(Entity.CurrentMember,[2001.TOTAL],Revenue,ACTUAL,USD,PERIODIC),DESC)

End of the example.
Syntax

Order(<Set>, {<String Expression> | <Numeric Expression>} [, ASC | DESC | BASC | BDESC])

There are two varieties of Order:

  • Hierarchical (ASC or DESC)

    The hierarchical ordering first arranges members according to their position in the hierarchy. Then it orders each level.

  • Nonhierarchical (BASC or BDESC, where B stands for Break hierarchy).

    The nonhierarchical ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.