Advanced sorting - Microstrategy

Sorting allows you to order the report data set to present your business information in a more informative way. For example, you can alphabetically sort country and region on a report, allowing you to quickly find a particular region. The Basic Reporting Guide discusses such quick sorting, which is selecting a column or row to sort on.

Advanced sorting allows you to create your own, more complex sorts for rows and columns. You can select the object to sort by, the sorting order (ascending or descending), the sorting criteria, and the position of the totals. The options for the sorting criteria depend on the sort object. For example, Employee can be sorted by last name, first name, Social Security Number, or the attribute ID. The sorting criteria do not have to be displayed on the report.

Multiple-key sorting, or hierarchical 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.

Sorting metrics hierarchically allows you to use group totals for sorting. That is, the groups on the report are totaled, and the report is sorted on these totals. An example of hierarchical sorting is explained after the advanced sorting example that follows.

Advanced sorting example

Open the Advanced Sorting report, a subset of which is shown below. While you can create this report yourself, there are many features on it, so it is quicker to just examine the completed report.

Advanced sorting example

Click Advanced Sorting under the Data menu. The rows are sorted by ascending region and descending fourth quarter 2003 revenue. The columns are sorted by the quarter ID in descending order. Return to the report and examine the sorted data. Notice, that the columns are in reverse order, fourth quarter 2003 to first quarter 2002. The customized banding makes it easier to view the region separations in the rows. Notice that the regions are in alphabetical order, Central to Web. The rank metric helps you confirm that within each region, employees are sorted based on fourth quarter 2003 revenue. For example, the rank is 4, 3, 2, 1 in the Central region for Q4 03. For Q3 03, the rank is 2, 3, 4, 1.

Hierarchical sorting example

On the Advanced Sorting report used in the previous example, complete the following steps to prepare for the hierarchical sorting example. These tasks are not needed to sort a report hierarchically, only on these sample reports.

  1. Move Rank to Report Objects.
  2. Move Quarter from the columns to the rows, to the left of Region.
  3. Edit the view filter to remove Northwest and Web from the list of regions.
  4. Add standard totals by choosing Subtotals from the Data menu, then selecting Totals from the list of available subtotals.

The following procedure sorts the report by revenue, in descending order. The totals are placed at the top of each section, rather than more conventionally at the bottom.

To sort metrics hierarchically

  1. Select Advanced Sorting from the Data menu. The Sorting dialog box opens.
  2. On the Rows tab, click Remove All to delete the previous sort.
  3. Click Add to create a new sort.
  4. Change Sort By to Revenue.
  5. Change the Order to Descending.
  6. Change the Total Position to Top.
  7. Select Sort metrics hierarchically and choose Total.
  8. Click OK.

The results are displayed below.

Hierarchical sorting example

This report is saved as Advanced Sorting - Hierarchical.

Notice how the report is sorted. Within the region Southeast in Q4 2002, the employees are sorted by revenue, in the order of the highest revenue producer to the lowest. Within Q4 2002, the regions are also sorted, from Southeast with $376,461 in revenue to South with only $238,364. The quarters are sorted, from Q4 2002 at $932,383 to Q1 2003 at $121, 639. The groups on the report are sorted hierarchically.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics