Multi-column Sort of Horizontally Expanded Columns

Application Scenario

You can sort data (in ascending/descending order) in a column by clicking the column header (obtained by the horizontal expansion of a field) when previewing a report, as shown in the following figure.

Implementation Method

You can obtain all values of the clicked column and set Sort After Expansion in Cell Attribute of the cell where the field Sales Person is located, to sort sales persons based on all values of the clicked column.

Data Preparation

1. Create a general report, create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume.

Report Design

The following figure shows the designed table style.

The following table shows the cell attribute setting.

Note:

If you set Data Setting of a data column to Summary, the data column is not expanded automatically. You can view the expansion direction of a cell through the gray arrow in the upper left corner of the cell.

Dynamic Parameter Adding

Add a hyperlink (dynamic parameter type) to cell B1 and rename the hyperlink to Sort.

Add two parameters order (value formula: if($order = 1, -1, 1)) and index (value formula: &B1), as shown in the following figure.

Note:

The value of the index parameter is &B1. That is, the value of index is the number of the clicked cell when you click the column header.

Sort After Expansion Setting

Select cell A2, click the Cell Attribute icon on the attribute panel, click Expansion, set Sort Rule in Sort After Expansion to Ascend, and enter the formula if(isnull($index), A2, if($order = 1, EVAL("B2[;B1:" + $index + "]"), EVAL("-1*B2[;B1:" + $index + "]"))).

When order is 1, the values of the clicked column obtained by EVAL("B2[;B1:" + $index + "]") are sorted in ascending order. When order is -1, the values of the clicked column are first converted to negative numbers and then sorted in ascending order based on the negative numbers.

The following table describes the formula.

Formula	Description
isnull($index)	Checks whether the value of $index is an empty string. The value of $index is empty during initialization and then is the value of &B1 (the number of the clicked cell) after you click the column header. Note: If the value of $index is empty, you can sort by cell A2 to improve the performance of the first column header click.
"B2[;B1:" + $index + "]"	Returns the value of cell B2 corresponding to the $index-th value expanded from cell B1.
EVAL()	Returns a string expression.

Effect Display

On PC

Save the template and click Pagination Preview. The following figure shows the sort effect.

On Mobile Terminals

The preview effects on the DataAnalyst and HTML5 apps are the same, as shown in the following figure.

You can download the example template Multi-column Sort of Horizontally Expanded Columns.cpt.