Overview
Expected Effect
The Custom Sort function helps directly define the order of each data item in the formula, but sometimes it can make the formula difficult to read and maintain.
In this case, you can use built-in datasets to customize the sorting, as shown in the following figure.
Solution
Retrieve the value through the dataset function, and then return the position of the cell value in the built-in dataset through the inarray() function.
Example
Data Preparation
1. Create a general report and create a database query named ds1 with the SQL statement SELECT * FROM Sales.
2. Create a built-in dataset named Custom Order, add a column named Region Order, and enter the data in the order shown in the following figure.


Report Design
Drag the data columns from dataset ds1 into the cells and design the table style, as shown in the following figure.
Sort After Expansion Setting
Select cell A2, click the Cell Attribute icon, set Sort Rule to Ascend, and Sort Basis to Formula, and enter the formula inarray(A2, Custom Order.select(Region Order)).
The following table describes the formula.
Formula | Description |
Custom Order.select(Region Order) | A dataset function that returns all values of the Region Order column in the Custom Order dataset. |
inarray() | Returns the current value of cell A2 in the Region Order. |
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.