Sorting a Multi-level List

Version 

Application Scenario

The ordinary sort fails in the face of a report with a multi-level list. So how to sort a multi-level list?

Implementation Method 

A multi-level list is a sequence of strings consisting of numbers separated by periods (.) and can be sorted by converting strings into numbers.

Data Preparation 

Create a general report, create a built-in dataset Embedded1, and add two fields Project Symbol and Project, as shown in the following figure.

Table Design 

Drag the fields in the built-in dataset into cells A2 and B2, and enter the titles, as shown in the following figure.

Sort After Expansion Setting 

Select Cell A2 and set Sort After Expansion as shown in the following figure. The entered formula is SUM(MAPARRAY(split(A2, "\\."), item * POWER(100, 4 - index))).

The formula is to split the project symbols in Cell A2 with the period characters (.), multiply the obtained numbers by different squares of 100, and sum the results up.

The following table describes the formula.

Formula	Description
POWER(100,4-index)	Returns the result of a number raised to a power, that is, 100 raised to the power of 4 - index index: Returns the position. Note: Use 4 - index because there are three levels in the multi-level list in Cell A2.
SPLIT(A2, "\\.")	Returns a string array that contains the substrings that are delimited by the period characters (.) of values in Cell A2.
MAPARRAY()	Process the array in batches by converting the elements in the array results of split(A2, "\\.") using item * POWER(100, 4 - index).
SUM()	Sum up the converted arrays.

Effect Display 

On PC

Save the template and click Pagination Preview. The preview effect is shown in the following figure.

On Mobile Terminals

The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.

You can download the example template. Sort a multi-level list.cpt