Advanced sorting: hierarchical sorting

If you create multiple sorts on the same report, you are using hierarchical sorting. Hierarchical sorting, or multiple-key sorting, allows you to sort data according to multiple sorting criteria in a hierarchical manner. This means that the first criterion is the basis for sorting. Any ties are resolved using the second criterion, any remaining ties are resolved using the third criterion, and so on. If a tie remains after all the criteria are used, the default sort order is used as the tiebreaker. In a simple example, you can sort by ascending employee last name, then ascending employee first name. If two employees have the same last name, their first names are compared to alphabetically sort them. You can of course create more complex multiple-key sorting.

For example, a report contains Quarter, Region, Employee, and the Revenue metric, and is totaled by Quarter and Region. The report sorts by default in the following manner:

1. The report is sorted by quarter, displaying the earliest date first.

2. Within each quarter, the regions are displayed alphabetically.

3. Within each region, the employees are displayed alphabetically.

For instructions to hierarchically sort report objects other than metrics, see Performing an advanced sort.

Sorting metrics hierarchically

Sorting metrics hierarchically allows you to use group totals for sorting. That is, the groups on the report are totaled, and those totals are how the report is sorted.

You can sort metrics hierarchically to produce the following:

1. The report is sorted by quarter, in the order of the highest revenue quarter to the lowest.

2. Within each quarter, the regions are sorted from greatest revenue producer to the lowest.

3. Within each region, the employees are sorted by revenue, in the order of the highest revenue producer to the lowest.

For an example of this hierarchically sorted report, see the Advanced Reporting Help.

To sort metrics hierarchically

Sorting metrics hierarchically allows you to sort a report in outline mode by subtotal then by value.

1. Run the desired report. (For steps to run a report, see Running a report.)

2. Add subtotals to the report. How?

3. Display the report in Outline mode by selecting Display Outline Results from the Grid menu.

4. In the Report Viewer, right-click the metric to sort by. Choose Sort rows by this column or Sort columns by this row, depending on the metric's location, and then select either Ascending or Descending.

5. From the Data menu, choose Advanced Sorting. The Sorting dialog box opens and displays the Rows tab. The metric sort is displayed in the Currently defined sorts table.

6. Select the Sort metrics hierarchically check box. In the drop-down box next to this check box, select the desired subtotal to sort by.

7. To define where the subtotal appears, select one of the following from the Total Position drop-down list:

   • Top, which positions the subtotal at the top of the report

   • Bottom, which positions the subtotal at the bottom of the report

   • Inherit, which positions the subtotal as set by the report designer

   • Mixed, which sorts the subtotal amounts with the other metric values, instead of placing the subtotals together
     

     You must define the subtotal position here; you cannot define it in the Subtotals dialog box because hierarchical metric sorting takes precedence over subtotal display.

8. Click OK. The report is sorted by subtotal, then by value.

Related Topics

• Advanced sorting

• Sorting a report