Overview
Expected Effect
You can use the SQL statement ORDER BY A, B in datasets to sort data first by A and then by B. However, you may not know how to achieve the effect of the multi-column sort in the designer. Therefore, this document introduces how to sort multiple columns through Sort After Expansion.
For example, you want to sort data (after expansion) first by Sales Person and then by Sales Volume based on the same sales person, as shown in the following figure.
Implementation Method
1. You can sort character data through INARRAY(Character data, SORTARRAY(UNIQUEARRAY(All expanded character data))).
2. You can directly sort value data by its size.
3. Finally, you can calculate the results of the above two steps to obtain new values and sort the new values.
Example
Data Preparation
Create a general report, create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume where Region='North China'.
Table Design
1. Drag the fields in the dataset into cells from cell A2 to cell E2 respectively, as shown in the following figure.
2. Select cell A2 and set Data Setting to List, as shown in the following figure.
Sort After Expansion Setting
Select cell A2, set Sort Basis to Formula and Sort Rule to Ascend, and enter the formula INARRAY(B2,SORTARRAY(UNIQUEARRAY(B2[!0])))*POWER(10, LEN(MAX(E2[!0])))+E2, as shown in the following figure.
The combination calculations (involved in the formula) of cell B2 and cell E2 can help you to first sort data in cell B2 and then sort data in cell E2. The following table describes the formula.
| Formula | Description |
|---|---|
SORTARRAY(UNIQUEARRAY(B2[!0])) | The SORTARRAY function is used to sort data (text data) in cell B2. |
INARRAY(B2,SORTARRAY(UNIQUEARRAY(B2[!0]))) | Data in cell B2 has been sorted through the SORTARRAY function and saved as arrays. Positions of each text data in arrays are obtained through the INARRAY function. That is, by using the INARRAY function, the sorted texts can be converted into numbers, making it easier to perform combination calculations with data in cell E2 and then sort data in cell E2. |
*POWER(10, LEN(MAX(E2[!0]))) | This formula represents a fixed exponent. The result (obtained by the former formula) needs to be multiplied by the result (obtained by this formula) so that the number (representing the position of the text) can be increased. / If you do not increase the number (representing the position of the text) and only use small numbers such as 1, 2, and 3, the sort of values in cell B2 (after values in cell E2 are added) will be affected. |
+E2 | When the values in cell B2 are the same, the sum of values (obtained by adding the values in cell E2) is sorted in the group. That is, values in cell E2 are sorted after the sort of values in cell B2. |
Effect Display
On PC
Save the template and click Pagination Preview. The effect is shown in section "Expected Effect."
On Mobile Terminals
The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.
Template Download
You can download the example template Multi-column Sort in Ascending Order After Expansion.cpt.
Previous：Sort in the Dataset
