Version 2021

Sort Data

When you use a REST API request to create a cube or report, you can sort the data that is returned. This is particularly helpful when you are using incremental fetch. You define the kind of sorting to apply in the body parameter of the request.

You can sort the results of the following requests:

• POST /reports/{reportId}/instances
• POST /cubes/{cubeId}/instances

The two requests do the same thing. They create a report instance (based on either a cube or a report), get the instance information, and return the results of the first paging. Both endpoints support simple sorting and nested (or hierarchical) sorting. Simple sorting has only one sorting criteria; nested sorting has multiple sorting criteria, which are applied in the order in which they are listed. When sorting criteria are applied to the results of either request, it works the same for reports and cubes.

Currently, you can only sort the results for reports that have attributes on rows and metrics on columns; this is because MicroStrategy REST APIs only support these kinds of reports. You can use derived elements in the sorting definition for both reports and cubes.

• Sorting criteria that can be applied

  Describes the three ways that report results can be sorted—by attribute form, by metric, and using the default attribute sort.

• Sample body parameter

  Provides the model for the body parameter of the request (with code for sorting shown in bold) and two versions of a sample body parameter with actual data.

• Sample JSON output

  Provides sample JSON output for the request (with code for the sorting definition shown in bold).

• Possible sorting errors

  Provides possible errors that can be returned when sorting fails.

Sorting criteria that can be applied

There are three kinds of sorting, which can be applied in either ascending or descending order:

• Sorting by attribute form

  You specify an attribute form whose value will be used to sort the report contents; you provide the ID of the attribute and the ID of the attribute form. You also specify the order in which the contents will be sorted.

• Sorting by metric

  You specify a metric whose value will be used to sort the report contents; you provide the ID of the metric. You also specify the order in which the contents will be sorted.

• Sorting using the default attribute sort

  In MicroStrategy Developer, you can set a default attribute sort property, which is saved in the metadata. To set this property, you specify an attribute form whose value will be used to sort the contents of any report that includes the attribute; you also specify whether the sort order will be ascending or descending. This default attribute sort property is automatically applied whenever the attribute is used in a report.

  • If you are creating a report instance that includes an attribute whose form has the default attribute sort property set and it is the only sorting criteria that will be used in the report, you do not have to specify the sort. This is because the default attribute sort is automatically applied whenever the attribute is used in a report.
  • If you are creating a report instance that includes an attribute whose form has the default attribute sort property set and it is one of multiple sorting criteria that will be used in the report, you must specify the sort in the hierarchical order where it should be applied. You provide the ID of the attribute; you do not have to specify the attribute form to use or the sort order because they are part of the default attribute sort property.

  You cannot set the default attribute sort property in MicroStrategy Web; it can only be set in MicroStrategy Developer.

Sample body parameter

You define the sorting criteria to apply in the body parameter of the request, together with other values that determine the data included in the report instance—such as attribute values, metric values, and view filters. You can use derived elements in the sorting definition for both reports and cubes.

The model for the request body parameter is provided below, with the code for sorting shown in bold.

The sorting code for a sample body parameter with actual data is shown below. This code includes all the properties for each kind of sorting.

And here is slightly different sorting code that produces the same results. This code does not include properties that are not required, such as type and name, but it produces the same JSON output as the previous code.

Sample JSON output

When you use the sorting parameter in the request body, the output of the endpoint includes the sorting definition, which is shown in bold in the JSON output shown below.

Possible sorting errors

Sorting can fail for a number of reasons. The following errors can be returned.