MicroStrategy ONE

Sorting Data on Reports

Sorting allows you to order the report results 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 Help discusses such quick sorting, which is available in the right-click menu when you select a column.

Advanced sorting allows you to create your own, more complex sorts for rows, columns, and pages. You can sort by both columns and rows at the same time. 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.

One type of advanced sort is multiple-key sorting, or hierarchical sorting, which sorts data according to multiple sorting criteria in a hierarchical manner. This means that the first criterion is the initial 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. 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.

If a report is not sorted, the result set is displayed in the default order. If the report includes multiple attributes on the rows, the report sorts by the attribute farthest to the left. If the attributes are on the columns, the report sorts by the top attribute. The report is sorted by the attribute IDs, in ascending order, of the left or top attribute. If the report displays multiple attribute forms for a single attribute, the report still uses this default order. For more information on sorting with multiple attribute forms, see Example: Sorting on Multiple Attribute Forms.

If a project designer has created a report sort on an attribute form that is displayed on the report, that sort is used. The Project Design Help discusses how to create report sorts.

A project designer can create an attribute form that is used solely for sorting, and is not displayed on the report. The report can be sorted by this attribute form by default, without having to define any advanced sorting for the report. The VLDB Properties chapter of the System Administration Guide discusses the Default Sort Behavior for Attribute Elements setting.

Sorting Options Example

Open the Advanced Sorting report, a subset of which is shown below.

Click Advanced Sorting in the Data menu. The rows are sorted by ascending region and descending fourth quarter 2023 revenue. The columns are sorted by the quarter ID in descending order. Return to the report and examine the sorted data. The columns are in reverse order, fourth quarter 2023 to first quarter 2022. The customized banding makes it easier to view the region separations in the rows. 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 2023 revenue. For example, the rank is 4, 3, 2, 1 in the Central region for Q4 23. For Q3 23, the rank is 2, 3, 4, 1.

Sorting Metrics While Retaining the Hierarchical Structure

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

To Set Up the Sample Report

  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. Choose Data > Subtotals > Total.
  5. Click OK.

To Sort Metrics Hierarchically

  1. Choose Data > Advanced Sorting.
  2. On the Rows tab, click Remove All to delete the previous sort. At the prompt, click Yes.
  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 the Sort metrics hierarchically check box, and choose Total from the drop-down list next to the check box.
  8. Click OK.

    The results are displayed below.

    This report is saved as Advanced Sorting - Hierarchical.

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

Example: Sorting on Multiple Attribute Forms

Attribute display is an OLAP Services feature and you must have the "Use report objects window" and "Set attribute display" privileges to use this feature. For more information, see the In-memory Analytics Help.

If multiple attribute forms are displayed for a single attribute, the report is still initially sorted in the default order, as set in the Attribute Editor. You can change the sorting, as described below; sorting set for a report takes precedence over default sorting set for attribute forms. (For details on attribute forms, see the Attributes chapter of the Project Design Help.) The attribute forms are displayed in the order in which you added the forms.

The following report contains one attribute, Item, but multiple attribute forms. The forms displayed are, from left to right, ID, Description, Long Description, and Price. The report is sorted by ID, by default.

Although the ID is displayed in the leftmost column on this sample, the order that attribute forms are displayed in the report does not affect how the report is sorted. It is placed there to allow you to more easily see how the report is sorted initially. Even if the ID was not displayed on the report, the report would be sorted by ID by default.

The same report is shown below, sorted by Price, then Description. Notice the order of the items with a price of $50.

The following procedure re-creates the example report shown above.

To Set Up the Sample Report

  1. Choose File > New > Report.

    If the New Grid dialog box is displayed, click the Empty Report icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

  2. Add Item to the report.
  3. Create a report filter for Category = Electronics.

To Add Attribute Forms and Change Their Display Order

  1. Choose Data > Attribute Display.
  2. Select Use the following attribute forms.
  3. In the Available forms list, select ID and click > to add it to the Displayed forms and Report objects forms lists. Repeat with Long Desc and Price.
  4. Re-arrange the forms in the Displayed forms list, using the up and down arrows on the right side of the Displayed forms box, until the forms are in the following order:
    • ID
    • Desc
    • Long Desc
    • Price
  5. Click OK.
  6. Save the report and then execute it.

To Sort By attribute Forms

  1. Choose Data > Advanced Sorting.
  2. On the Rows tab, click Add to create a new sort.
  3. Change Criteria to Price.
  4. Click Add to create another sort.
  5. Change Criteria to Desc.
  6. Click OK.

    This same report is shown below, now sorted by Price, then Long Description. Notice the order of the items with a price of $50. In the previous sort, the first $50 item was the portable TV, followed by the portable CD player. Now it is the compact flash, followed by the power TV antenna.

To Change the Attribute Form Sort

  1. Choose Data > Advanced Sorting.
  2. On the Rows tab, click Desc in the Criteria column. From the drop-down list, choose Long Desc.
  3. Click OK.

    If you remove Price from the report display, but not from the report, the sort does not change. The report is still sorted by Price, then Long Description.

To Remove an Attribute Form from the Report Display

  1. In Report Objects, right-click Item, point to Attribute Forms, and select Price.

    Price is no longer displayed on the report, but remains in the Report Objects.

    If you remove Price from the report, the report can no longer be sorted in the same way. It is re-executed, and sorted by Long Description only, as shown below. Notice that the first item is now a CardBus as opposed to the TV antenna of the previous sample.

To Remove an Attribute Form from the Report

  1. In Report Objects, right-click Price and select Remove from Report.
  2. A message is displayed, indicating that the report must be re-executed for this manipulation to take effect.
  3. Click Yes. The report is re-executed and displayed as shown above.

Sorting with Null Values

You can specify a value to use to replace null values when the report is sorted. For example, metric values on a report are sorted in descending order but blank values (or nulls) appear first, as shown below:

You can instead specify that the null values are treated as zeros, so that the null values will then display at the bottom of the report. The null values still appear as blanks on the report, as shown below:

To Specify a Replacement Value for Null Values When the Report is Sorted

  1. Choose Data > Report Data Options.
  2. In the list of Categories on the left, expand Display, select Null Values.
  3. Clear the Use Default check box under Null Display settings.
  4. Select the Set the value to be used in place of empty values when the report data is sorted check box.
  5. Enter the replacement value (such as 0) in the text box below the check box.
  6. Click OK.
  7. If a prompt to re-execute the report is displayed, click Yes.
  8. Save the report.

Sorting the Page-By List

Pages group report data into logical subsets, and allow you to view one subset (or page) at a time. Page-by makes viewing a report easier, since users do not have to scroll through long lists of data. You can specify how the pages are displayed in the drop-down list of pages, by selecting the sorting order, the sorting criteria (for example, the attribute form on which to sort), and the position of the totals in the page list.

For example, the following report contains Region, Employee, the Revenue metric, and the Rank metric. It is paged by Quarter, so that a user can select a particular quarter to display. Notice that the Total is displayed first in the list, followed by quarters from the earliest to the most recent. Sorting the page-by list allowed the report designer to select the order of the quarters and whether the total is displayed at the beginning or the end of the list.

The following procedure describes how to recreate this example by starting with the Advanced Sorting report.

To Sort a Page-By List

To Set Up the Sample Report

  1. Run the Advanced Sorting report.
  2. Move Quarter to the page-by area.
  3. Choose Data > Subtotals > Total.
  4. Click OK.
  5. Click the page-by field and note that the quarters are sorted from the most recent date to the earliest date, with Total at the end of the list.

To Sort the Page-By List

  1. Choose Data > Advanced Sorting.
  2. On the Rows tab, click Remove All to delete the previous sort. At the prompt, click Yes.
  3. Click the Pages tab. Notice that the attribute that the report is paged by, Quarter, is displayed in the Sort By column.
  4. Change the Order to Ascending.
  5. Change the Total Position to Top.
  6. Click OK.

    Click the page-by field and note that the quarters are sorted from the earliest quarter to the latest quarter, with the total at the beginning of the list.

    You can sort the page list of any page field on the report. For example, on the report created above, move Region to the page-by area. By default, the page list is sorted in ascending alphabetical order, from Central to Web. You can change the order to descending alphabetical order, or sort and order by the attribute ID rather than description. The following report sample shows the region page list sorted by ID, in descending order.