Order (PQL)

Returns an ordered list of elements from a specified list, using a variety of switches and mechanisms.

  • Returned Output: List
  • Library: PQL \ Semantic \ List
  • Version: 2018.00.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

Order( <List> , <Flag> )

or

Order( <List> , <Data Point> , <Flag> )

* Click on the function's arguments above for more details on the input values.

Comments
  • List parameter should be a list or set of elements from an existing hierarchy in the model.
  • The order flags indicate different techniques for ordering the list.
  • The numeric data point or tuple describes the values used to determine the order for the list when the second formulation is used.
  • For details on how to employ and use this function see the semantic calculation overview.

This function is like the MDX order function; it orders a list of members or elements using labels or values.

Order Flags
  • ASC (Ascending) or DESC (Descending) will order the list from low to high, or high to low. The ranking mechanism of the first Order formulation (listed above) is based on the elements' labels, so it's alphabetical. The ranking mechanism of the second formulation uses the numeric data point for each element to provide the ordering.
  • CUSTOM is a flag for the first formulation only, and indicates that the list should be ordered in the way the members or elements are enumerated in the list itself (effectively unordered). This can be very useful for 'un-ordering' a list that would otherwise be ordered by another method.
  • NATURAL is a flag for the first formulation only, and indicates that the list should be ordered according to the way the members or elements are naturally enumerated in the data model or database itself.
  • BASC (Broken Ascending) or BDESC (Broken Descending) will order the list from low to high, or high to low. Both flags are for the second formulation only. Use these flags to ignore any hierarchical groupings of elements when creating the ordering of the list.

Examples

This example orders the list of countries alphabetically and returns United States, Canada, and Australia (using the country names as the labels):

Order( {[Customer].[Country].[Australia], [Customer].[Country].[United States], [Customer].[Country].[Canada]}, DESC )

This example orders the list of countries naturally and returns Australia, Canada, and United States (the order in which they are stored in the data base):

Order( {[Customer].[Country].[Australia], [Customer].[Country].[United States], [Customer].[Country].[Canada]}, NATURAL )

This example orders the list of countries by bike sales, from high to low:

Order( {[Customer].[Country].[Australia], [Customer].[Country].[United States], [Customer].[Country].[Canada]}, ([Measures].[Sales],[Products].[Product Category].[Bikes]) DESC )

Using the grid below, the results for the third example would be United States, Australia, and Canada: