KB20569: Interaction between Sorting and Pivoting in MicroStrategy

• Strategy

This article describes the interaction an differences between sorting and pivoting in MicroStrategy.

Generally there 2 types of sorting: Display sorting and calculation sorting. Display sorting is used to sort data in a template. This document discusses display sorting with the interaction between sorting and pivoting as the focus.
Calculation sorting applies to metric computations that depend on the order of values: Ranking, and OLAP windowing functions such RunningSum or MovingSum. Calculation sorting is not affected by the distribution of report objects into the row, column or page axes, and is not covered in this document.

1. Sorting in General
Sorting is an important function in crosstabbing, where sorting along each axis is independent of the others. Row, column and page-by axes can be sorted according to template units (attributes, metrics, consolidations and custom groups). Hierarchies, however, are first rendered into attributes in the template so sorting can be defined. Except for metrics, the sorting for each axis can only be defined by the template elements appearing on that axis. Sorting by metrics can be defined on row or column; the axis of a metric sort need not be the same as the axis containing the "Metrics" template unit.

Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.
Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.
Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.
Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.
Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.
Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.	Sorting TypeDescriptionAttribute Form SortSort by attribute formAttribute Default SortUse the attribute default sort as the sorting key. KB16623: How does MicroStrategy resolve the default sort order for an attribute)?Metric SortSort by metric valueMetric Hierarchical SortA variant of metric sorting, where sorting is applied to subtotal levels.Subtotal Position in SortDetermines the position of subtotal(s) in sorting, thus metric sorting can be applied without mixing the subtotal rows.

2. Pivoting
After the report is executed, the user might want to perform certain manipulations, such as pivoting. It is important to maintain a reasonable ordering of the headers after pivoting (including page-by). Considering the sort type, there are several rules that can be used to determine how sort definitions can be applied to template objects and, if necessary, moved to another axis to accommodate the pivot operation.

Attribute Form Sort
Since this sort actually depends on an attribute, if that particular attribute is deleted from or added to a certain axis, a corresponding attribute form sort should be deleted or added as well. That way, the sorting information is kept with the attribute and adheres to the sorting style on whatever axis it was moved to (or deleted from the original axis.).
In fact, there is always a default attribute form sort which is unaffiliated with pivoting. Attribute Default sort applies to each attribute and is, by default, used if no other sort is specified.
Metric Sort and Metric Hierarchical Sort
If the Metric sort is defined on the same axis as the metric being sorted, then the sort defined on other metrics may be moved to the target axis when the sorted metric is pivoted. Metric Hierarchical sorting is also affected by pivoting and moves with the metric unit.
However, if the Metric sort is on a different axis than the metric unit, then it will not be affected by the pivot. A metric sort could specify a list of chosen attribute elements in addition to the metric and subtotal tag. When an attribute is moved, the sort can still apply, provided that the sort still specifies an element for all the attributes on the axes other than the one to be sorted. If there is no such element specified, the subtotal element and be selected. (Please refer to an example in section 3.)
Subtotal Position in Sort
Subtotal Position does not depend on any unit. User can define it in "Display Option" tag of subtotal editor for specific subtotal(s).

3. User Scenarios

A simple example of Attribute Form sort
Use a report named Template as an example, which has 4 attributes: Category, Country, Region, and Year, an 2 metrics: Cost and Revenue. Total is also defined as the subtotal.

Figure 3.1 report Template

Step 1: Go to Data > Advanced Sorting, and define a sort on row axis like this:

Figure 3.2 Sort Definition before pivoting

Step 2: Pivot attribute Region to the page-by axis and Year to the column axis.

Figure 3.3 report Template (after pivoting)

Step 3: Now check the sort definition again, will find that the sort on attribute Region and Year are moved to the page-by axis and column axis.

Figure 3.4 Sort Definition after pivoting
Example of Metric sort: Qualify the metric sort according to user preference
If there are multiple pages, the user can either sort the rows according to the current page or according to a specific page. In addition, the user can choose elements from the row axis to qualify the metric sorting.
Use the same report (Template) as an example. Now, the attributes: Country and Region are on the page-by axis, and the Year attribute is on the column axis.
By default, the row sorting definition contains only elements from the column axis. No elements from the page-by axis or row axis can be selected directly. This makes the rows always sorted according to the current page.
As shown in the sort definition editor below: Only elements attributed to the Criteria column (Year) can be selected. In this case, if the user selects '2006', the rows will always be sorted according to the metric 'Cost' on column '2006' when user changes the page.

Figure 3.5 Metric Sort on Element

However, pivoting can be used to fulfill the requirement of sorting on elements from page-by or column axes.

1). Sort the rows according to a specific page.
Preferably, the user may want to always sort the rows according to metrics on one particular page. The user can first put attributes on the column axis, and select elements upon which to sort, and then pivot these attributes to the page-by axis, while the sort is still applied. (see rule for Metric sort).

Step 1: Put attributes Country and Region on the column axis as shown below.

Figure 3.6 Attributes on the Column Axis

Step 2: Define a metric sort as shown below ("USA, Central, Total" is selected):

Figure 3.7 metric sort on element

Step 3: Pivot attributes Country and Region to the page-by axis.
Note: In this example, element 'USA, Central, Total' is selected, the rows will always be sorted according to the metrics in column 'Total' and page 'USA, Central.' These metrics will remain static even if the user changes the page. E.g. If the current element, page-by Region, is changed to, 'Mid-Atlantic,' the rows are still sorted according to page "USA, Central".

Figure 3.8 Report after Pivot (Page-by Changed)

2). Choose elements from the row axis to qualify them for metric sorting.
This is similar to the first case and can be done using pivoting as shown in the previous example: Put the attribute on the column axis, define the sort with a certain element and then pivot the attribute to the row axis.
As shown in the above example, user can take advantage of the rule for Metric sort during pivoting, to personalize the sorting definition.

4. Unusual scenarios and Existing Issues
Some sorting and pivoting scenarios might still present surprising behavior.

Figure 4.5 Sort Definition after last Pivot

Data "lost" in grid
The following report has attribute Year on the page-by axis, attributes Category and Subcategory on the row axis. Also, there is a sort defined on Subcategory.

Figure 4.1 Report before Pivot

Now move the attribute Subcategory to the page-by axis, after the Year attribute. User will see that there is only one element left in the Subcategory dropdown list after the pivot.

Figure 4.2 Report after Pivot - Subcategory Drop-down List

This is because the sort is defined by the Subcategory but now it has been moved to the page-by axis as well. Check the Year attribute's drop-down list and the elements (2006, 2007, 2008) are repeated, each 3-year set corresponds to one element of the Subcategory.

Figure 4.3 Report after Pivot - Year Drop-down List

There is no data loss here. Just the organization of the data has become unnecessarily garbled. To make the report look better, user can move the Subcategory attribute to the left of the Year attribute on the page-by axis, or just remove the sort definition from the page-by axis.
Different Behaviors of Metric and Metric Hierarchical sorts
Users might get confused by the different behaviors of Metric and Metric Hierarchical sorts after pivoting. Sometimes they move to the target axis as the metric unit and sometimes they do not. It depends on whether the sort is defined on the same axis as the metric unit.
Use report in Figure 3.3 as an example.

Step 1: As shown below, Metric Hierarchical sort is defined on the row axis while the metric unit is on the column axis.

Figure 4.4 Sort Definition before Pivot

Step 2: Pivot the metric unit to the page-by axis and check the sort definition again.
Note that it is still on the row axis as shown in Figure 4.4. Because the metric unit was not on the row axis, the sort is not attached to the metric unit, hence it will not move as the metric unit moves.

Step 3: Pivot the metric unit to the row axis. After the pivot, the sort is on the same axis as the metric unit. Thus the sort can be attached to the metric unit and will move to the target axis later if the metric unit is pivoted again.

Step 4: Pivot the metric unit to the column axis. Now the Metric Hierarchical sort is moved to the column axis with the metric unit. Please note here though the "Total Position" is now "Left" and seems to be different with the previous value "Top", but actually the definition remains unchanged: the display name is adjusted according to the axis. For example, on column axis, only "Left" or "Right" can be used to describe the position, while "Top" and "Bottom" are used for row axis.
Page-by not supported in Web
Strategy Web does not allow a sort to be defined on the page-by axis. However, you can solve this problem by pivoting. The user can define sort on row/column axes first, then pivot to the page-by axis. The sort will be moved to the page-by axis.

Comment

0 comments

Details

Knowledge Article

Published:

April 11, 2017

Last Updated:

April 11, 2017