MicroStrategy ONE
Sorting a report
Sorting a report is one of the ways you can manipulate data display, without re-executing the report against the warehouse. Sorting allows you to specify the order that the data in a report for a particular row or column is presented in: either ascending or descending. Sorting is processed by the Analytical Engine. You can select what objects you want to sort, the sorting criteria, and the sorting order.
Any object that you can place in a template can be sorted. You can select for a row or column of data to be sorted in ascending or descending order. An ascending sort orders the data from smallest to largest, such as A - Z, or 1-10. If you choose descending order then the data appears from largest to smallest, such as Z - A, or 10 - 1.
If there is no sort information in the report, the result set is by default sorted based on the attribute ID of the attribute in ascending order if there is only one attribute in the report, or the far left attribute on the row or the top attribute in the column if there are multiple attributes in the report.
MicroStrategy offers various sorting capabilities:
-
Quick sort allows you to select a column or row and sort it in either ascending or descending order based on the data displayed. This is available using the Data menu options, the sorting buttons, or the right-click menu option. Instructions are provided below.
-
Advanced sort allows you to create your own, more advanced sorts for rows, columns, and pages. You can sort on attributes, metrics, and custom groups. The sorting criteria do not have to be displayed on the report. Advanced sorting also allows you to perform hierarchical sorts, or multiple-key sorts, which allow you to sort data according to multiple sorting criteria in a hierarchical manner. Advanced sorting is available through the Data menu. For instructions for advanced sorting, see Performing an advanced sort. For examples of advanced sorting, see Advanced sorting: custom group sorting, Advanced sorting: hierarchical sorting, and Advanced sorting: sorting the page-by list.
Sorting and pivoting
When you pivot a sorted object (move it between the rows and the columns of the report), the pivoted object retains its sorting order by default. For example, a report contains the Region and Employee attributes on the rows, with the Revenue metric on the columns. Region is sorted descending from Web to Central. When you pivot Region to the columns of the report, Region remains sorted. The Web region is displayed on the left, and Central on the far right, as shown below:
You can change this behavior, so that the sort is not moved with the pivoted object. In this case, when you pivot Region to the columns, Region reverts to the default sort. Central is displayed on the left, and Web on the far right:
To change this behavior, in the Project Configuration Editor: Report Definition category, Advanced subcategory, clear the Move sort keys with pivoting unit check box.
To quickly sort a report
-
Run the report. (For steps to run a report, see Running a report.)
-
Click the column heading of the column to be sorted. The background area of that column turns dark and the AZ (down arrow) and ZA (down arrow) buttons become active on the Data toolbar.
-
Select the appropriate sorting button, either AZ for ascending or ZA for descending. These two buttons also execute appropriate actions on numeric columns.
If metric values are sorted in descending order and blank values (or nulls) appear first, you can specify that the null values are treated as zeros. The null values will then display at the bottom of the report. For instructions, see Sorting so null values appear at the top or bottom of a report.