Sorting

Sorting sorts the elements of a list. The sorting criteria influence the number of elements transferred from the OLAP Server database.

SeeCut-off type and cut-off value.

The Sorting pane is one of the panes of the List Designer.

SeeList Designer.

Buttons of the Sorting pane

Sorting has these buttons:

New Sorting Criterion: Creates a sorting criterion.
Break Hierarchy: Breaks the hierarchy. With this non-hierarchical ordering, the elements are arranged without reference to their position in the hierarchy. In an unbroken hierarchy, elements are first ordered by their position in the hierarchy and then ordered within each level.
Remove: Deletes the sorting definition.
Up, and Down: Change the order of the sorting criteria.

Sorting types

You can use these types of sort:


Type	Description
Caption	Sorts elements by their captions
Unique name	Sorts elements by their unique names
Attribute	Sorts elements by their attributes
Value	Sorts the elements by value. When you sort by value, you can define the slice of data to which the sort applies. SeeFiltering and sorting by value in the Data Area.
Custom MDX	You can create an MDX statement to define how the elements are sorted

Sort order

Select Ascending or Descending from the Order list.

Note: When you sort with multiple criteria on an Analysis Services database, unexpected results can occur unless you break the hierarchy. Click Break Hierarchy on the Sorting toolbar.

Cut-off type and cut-off value

If you sort by value, you must define the cut-off type, the cut-off value and the Data Area.

SeeFiltering and sorting by value in the Data Area.

When you specify the cut-off type and the cut-off value, you can select a part of a list that has been sorted by values. If you select as the cut-off type, the list elements are sorted in ascending or descending order.


Cut-off type	Cut-off value	Example
Top Count/Bottom Count	Specify how many elements with top or bottom values are displayed	Show the top 5 products
Top Sum/Bottom Sum	Specify the sum of the elements that is displayed	Show products which have a total turnover of at least 50000
Top/Bottom Percent	Specify a percentage	Show the products that make up 30% of the total turnover

Example. Create cross-hierarchical sorting with user-defined MDX

This expands the aggregate elements in a dimension to show all its elements without any hierarchical structure. It sorts their captions in descending order.

In the List Designer select the sorting criterion Custom MDX and specify:

[PRODUCT].CurrentMember.name,BDESC.

Example. Intra-hierarchy sorting with user-defined MDX

This sorts the captions of the children of the list in descending order (the All Tires element remains at the top of the list). The hierarchical structure of the list is retained.

In the List Designer choose the sorting criterion Custom MDX and specify:

[PRODUCT].CurrentMember.name,DESC.