Sorting

Sorting allows you to choose the order in which data and items are presented in a discovery. Quick sorting offers a context-driven selection of what to sort and a set of basic sort algorithms through a point-and-click menu option. You can use the Sort Wizard (covered in this topic) to create one or more "sorts" with greater control over the sorting logic and mechanics and also to edit and refine quick sorts created through query interactions.

Sort Logic

Sorting logic is broken into three main constructs:

• List: The first is the listing of elements to sort
• Logic: The second is to choose the sort type
• Values: The last, which is optional, is to describe the values that will drive the sorting operation.

Accessing the Sort Wizard

Open the Sort Wizard, by selecting Sort & Filter > Sort from the Home ribbon, Sort from the Query ribbon, or by right-clicking the background (white space) in your discovery. (Do not confuse this option with the 'quick sort' menu option.)

Sort Targets

To add a sort target (the hierarchies that should be sorted), click the blue plus sign in the Sort Targets header. From the Sort panel, select the hierarchies to be sorted. You can elect to sort a single hierarchy of multi-hierarchy combination. You can even add multiple target definitions to add different sorting logic for each target.

When adding a new sort, the member hierarchies within the query will appear as the basis of the sort. You can base your sorting preferences on any combination of the member hierarchies currently in the query, and any data point in your model even if those data points are not visible in the query.

Sort Settings

Sort Types

The sort type determines the overall sorting approach to be used. The type options change depending on the hierarchies chosen and the data source type.

Note: Hierarchy elements are always presented in their 'natural sort order' by default. The natural order is usually defined in the underlying data model and is typically alphabetical. Specifying a sort, breaks that natural ordering.

• Values: sorts the hierarchy elements based on numerical values. The numerical values or data points can be specified independently. See below.
• Labels: sorts the hierarchy elements using their labels or captions. This is effectively an alphabetical sort unrelated to values.
• Custom: sorts the hierarchy elements in the order they were selected from the elements tree - as opposed to their natural order or logical order. Since this is effectively a "no sort" option, all other sorting options are ignored. See below for more.
• Inverted: this sorts the elements of a multi-level hierarchy such that parent elements appear after child elements. This capability is only available for MS OLAP and SAP BW hierarchies.

Sort Order

Select the direction of the sort.

• Ascending: sort elements in ascending order from the element with the smallest to the largest data point value, or sort labels alphabetically from A to Z.
• Descending: sort elements from highest to lowest, or labels in reverse-alphabetical order Z to A.

Break Hierarchies

When sorting across two or more hierarchies, users to decide if the elements should be evaluated in a nested sequence and sorted in hierarchical context, or whether the sort should be agnostic of such structures and sort elements independently - effectively breaking context.

For example, looking at this simple grid, with the data shown in its 'natural' order, we can see the elements are listed alphabetically: Accessories then Clothing; while the colors are listed alphabetically from black to Silver.

If I build a sort using both product category and color, and choose to sort with the default measure (with the measure currently used in the query itself - margin), the grid will appear as such with the 'break' switch off. This says sort category in ascending alphabetical order, then within that, sort color from high to low margin.

Enabling the 'break' option, the grid changes to the following, with each item appearing in its numerical position. Accessories/NA with 658 is the largest item and therefore appears at the bottom of the list. In the scenario, it is not unusual to see elements appear multiple times in the grid (like Accessories).

Break using hierarchical structures

The break option functions in the same manner when using hierarchical structures. For example the Products hierarchy has Product Category as the top level and Product Sub Category as the next level.

This example contains a sort using the product hierarchy that was expanded down to the next level (sub category). Margin is the default measure used in the query. The 'break' switch was switched off. This causes the query to sort by the products hierarchy (Bikes 23.01%, Accessories 38.28%, and Clothing 38.97%) and then within that, sort the sub category from low to high margin for each sub category.

In this example, the 'break' switch was switched on, causing the query to sort by margin without sorting the product sub category within the product category. This results in the products (bikes, accessories, and clothing) being intermingled in the sub products according to their margin values.

Select Hierarchy & Element

Data Points and Values for Sorting

When sorting elements based a 'values', we are effectively choosing to sort the elements according to some data point in the data model. Using grids above, we are sorting the category-color combinations based on the 'margin' data point or value for each item.

When you use Quick Sorts or the more complete Sorting Wizard, the data point is heuristically determined from the query itself or the context of the query (filters, and so on). However, you are not limited to using these items. You are free to completely define a different data point to use for the sort order. The data point can be a simple measure selection or be a much deeper selection of elements from anywhere in the data model - regardless of whether it can be seen in the query itself or not.

For example, the above grids simply choose margin. The user could decide to sort the items using 'Sales figures for Male customers in 2018' for each category-color combination instead. You can see below that the resulting grid shows the same margin values for category-color combinations, but the order is different to the above.

The flexibility to define an independent data point to drive your sorts is a very powerful option. To pick the items for the data point, we use the hierarchy and element pickers, described below.

Making Data Point Selections for Sorts

By default, sorting is configured according to the measures in the query and anything else driving 'context'. Context means things like slicers and other query functions. To change the metric or data point, optionally select elements from each hierarchy, starting with any other measure.

Once you have selected a hierarchy, then select the specific element or measure from the elements panel.

Note: You can only pick one element from a given hierarchy:

• If you need to pick more than one item, you need to first build a composite or custom member, and then select that from the custom measure / member listing in the elements tree panel.
• You can parameterize the selection using parameterized calculated members or measures.

Custom Sorting

To facilitate the custom ordering of hierarchy elements (or 'members') and measures, users can elect to use the "Custom Sort" option. This switch tells the query engine to sort elements in the order they were picked when using the element trees, rather than their natural order, another sort mechanism or a specific ordering logic as defined and set inside a Custom List definition. Users can adjust this further using the Set Element Order dialog from the context menu.

Hide Query Info

When sorting the query, the sorting will appear on the canvas as a query info caption:

To hide this caption, while still sorting the query, enable Hide Query Info from the Design ribbon:

Position the Sorting

The sorting key is displayed on the right side of the canvas by default, but it can also be moved to the top, bottom, or left of the canvas.

To move your sorting key:

• Hover over the panel - the cursor will show as a move pointer (four arrows)
• Click and drag the cursor to one of the four docking zones
• When the docking zone displays a blue highlight, drop the sorting key panel

Report Based Sorting Wizard vs Sorting in a Custom List

Users are sometimes confused by these 2 capabilities and how they interoperate.

The Sort Wizard (and the 'Quick Sort') allow the user to sort the results of a query and save those settings into the report definition. Sorting steps added in Custom Lists designs are saved into the list definition, independent of its use in a report. Custom List sorting is therefore very useful for building highly specific, curated element lists that will be reused over and over, while sorts from a report are more ad-hoc in nature and usually deployed based on the needs of the specific report only.

Obviously, there is a logical overlap in both approaches - and the ability to choose from both strategies offers tremendous scope for designing the smartest analytic reporting to match your business needs.

Both mechanisms can be employed into the same report, each with multiple sorting instances if needed (multiple report sorts, multiple list sorts etc). Generally, if the sorts clash, the reporting sort will override the sorts of the lists.

Examples