Define custom hierarchy sorting

Contents[Hide]

1. Overview

This article shows you how to set up more customized sorting for a hierarchy's values in your metric set from the Data Analysis Panel. For each level, you can choose a sort direction and optional members to appear on top or the bottom.

Similar sorting options are also available when editing a hierarchy or time dimension to define their default sorting when added to a metric set or used in a filter.

Tip
The metric set can also be quickly sorted directly from the Data Analysis Panel or the context menu of a visualization. See Metric set analysis tools for details.

2. Defining custom hierarchy sorting

For this example, create or edit a metric set that is configured with one measure and a hierarchy under Rows.

From the toolbar, re-visualize the metric set as a bar chart and observe that the bars are sorted according to Product Category names in ascending or alphabetical order. This is the default sorting behavior for the Product hierarchy and its levels.

Metric set with hierarchy sorted in ascending alphabetical order
Metric set with hierarchy sorted in ascending alphabetical order

In the data analysis panel, click the Edit icon for the Product hierarchy.

Click to edit hierarchy
Click to edit hierarchy

In the Configure Metric Set Element dialog, click Define custom hierarchy sorting.

Configure Metric Set Element dialog
Configure Metric Set Element dialog

In the Set Up Hierarchy Sorting dialog, you'll see list of hierarchy levels (e.g., Product Category, Product Subcategory, and Product).

Set Up Hierarchy Sorting dialog
Set Up Hierarchy Sorting dialog

Click the edit icon for the Product Category level to configure the sorting for this level.

In the Configure Hierarchy Level Sorting dialog, set the following options:

  • Choose a Category. This is the member property you want to use for sorting (e.g., Caption or UniqueName).
  • Choose a Sort Direction. For example, set it to Descending.

Configure Hierarchy Level Sorting dialog
Configure Hierarchy Level Sorting dialog

Click Submit to save your changes.

Observe that the bars are now sorted according to Product Category name in reverse alphabetical order.

Sort by product category name in reverse order
Sort by product category name in reverse order

3. Specify members to be on top or bottom in sort order

In the Configure Hierarchy Level Sorting dialog, there are two additional options which let you choose one or more hierarchy members to always appear on top or bottom in the sort order.

For example, extend the previous example by clicking Add top override.

Click Add top override
Click Add top override

Click the dropdown that appears and select the member (e.g., Accessories) you always want to appear on top.

Choose the member to appear on top in the sort order
Choose the member to appear on top in the sort order

Click Submit to save your changes.

Observe that the bars are now sorted the same as before (reverse alphabetical order) except that the Accessories member appears first in the sort order.

Sort product category names in reverse order with Accessories on top
Sort product category names in reverse order with Accessories on top

You can specify which members appear at the bottom by adding a bottom override in a similar way.

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri