Sorting members of a level

For dimensionally modeled relational metadata, you can specify sort characteristics on a dimension. You can also specify sorting on individual levels within the dimension. When you sort individual levels within a dimension, you can ensure that the order in which the data is delivered to the model is appropriate. The order in which the data is delivered to the model can be particularly important when reporting data over relative time periods.

The default sort order is alphabetical, in ascending order, depending on the level caption. When you specify a sort order, the data is sorted on another column such as the business key.

Procedure

  1. In the Project Viewer pane, select a dimension or level.
  2. Click Actions, Edit Definition.
  3. Click the Member Sort tab.
  4. Select the sorting option to apply.
    Sorting Option Description

    Metadata (as shown in the member tree)

    Used only to specify how members are sorted when displayed in the metadata tree.

    The sort of the metadata tree cannot be changed by report authors.

    Data - Only as Default Report Sort

    Used as the default data sort for members in a report.

    If no data sort is specified, data is retrieved in the order that it was entered in the database.

    Report authors can override this value and apply a different sort to the members displayed in a report.

    Data - Always (OLAP compatible)

    Used to provide member relative functions (MRFs) with a consistent order of the members. This setting should only be used if MRFs are required. Otherwise, the sorting results in unnecessary overhead. Report authors cannot change the order of members as delivered to the MRFs. However, authors can apply a different sort to the members displayed in the report.

    If no sort is specified and MRFs are used, the report author will receive an error when MRFs are processed.

    When this option is set, the members of the level are also sorted in the metadata tree even if the Metadata option is not selected. The sort of the metadata tree cannot be changed by the report author.

    Tips

    • To apply the default sort order to all child levels within the levels in the dimension that do not have a sort option defined, click Detect.
    • To remove sort options from all child levels within the levels in the dimension, click Clear All.
  5. In the Select a Level to Assign Sort Properties box, click a level.
  6. In the Available Data Items box, click an item to sort and click the right arrow to add it to the Level Sort Properties box. Use the up and down arrows to change the order of items.

    Tip: To change the sort order to ascending or descending, click Sort Order.

  7. To specify how null values are sorted in reports, click the Nulls box beside the item and then click First, Last, or Unspecified.

    First places the null values at the beginning, and Last places the null values at the bottom. Unspecified uses the setting defined in the data source.

  8. Click OK.