INSERTSLICEAXISVALUESORT

This function modifies an existing slice axis with the addition of sorting by value. To add sorting to a slice, use the modified axis when you create a slice definition.

These functions have a similar purpose:

  • ADDSLICEAXISVALUESORT
  • INSERTSLICEAXISVALUESORT

The difference between the two functions is in the order in which they add sorting criteria to the MDX definition of a slice. The order in which criteria are added determines the order in which they are applied and can, therefore, affect how a slice is sorted.

ADDSLICEAXISVALUESORT adds sorting to the end of the list of criteria. INSERTSLICEAXISVALUESORT adds sorting to the beginning of the list of criteria.

Syntax

=ADDSLICEAXISVALUESORT(axis,"tuple",break_hierarchy,"order"[,"ranking"][,ranking_count]).

The function returns #{Axis}.

Use the optional ranking and ranking_count parameters to return, for example, the Top 5 values.

These rankings are valid:

  • None
  • TopCount
  • BottomCount
  • TopPercent
  • BottomPercent
  • TopSum
  • BottomSum

Example

In this example, cell F22 holds the result of a CREATESLICEAXIS function.

This function sorts values by period in descending order. True specifies that the hierarchy is broken:

=ADDSLICEAXISVALUESORT(F22,"[Period].[All Years]",True,"Desc")