Clicking a Column Header to Change the Sort Order

Application Scenario

You may usually want to toggle between ascending and descending order by clicking the column header when making a report. As mentioned in Sort in the Dataset, you can achieve dynamic sorting by clicking headers using defined dataset parameters.

However, dataset sorting is not applicable in some scenarios. For example, there is a column that is calculated based on other data columns, such as the Profit column in the figure below, which is derived from (Unit Price - Purchasing Price) * Quantity. This document describes how to achieve dynamic sorting by clicking the table header in this case.

Implementation Method

Add a hyperlink of the dynamic parameter type and a formula in Sort After Expansion under Cell Attribute. In this way, when you click a column header, the cell where the column header is located is passed as a parameter to the formula set in Sort After Expansion, thereby realizing different sorting according to different parameters.

Note:

1. The settings for fields of value and string types are different. See the following examples for details.

2. This document explains how to toggle the sorting order of a single column. To sort multiple columns dynamically, see Multi-column Dynamic Sort

Data Preparation 

Create a general report, create a database query named ds1, and enter the SQL statement Select * From SIndentDetail limit 20. 

Report Design 

Drag fields from the dataset into the table, set Data Setting of Cell A2 to List, insert the formula (C2 - E2) * D2 into Cell E2, and set the style, as shown in the following figure.

Dynamic Parameter Adding 

Select Cell F1 where the column header Profit is located, click Hyperlink, add a dynamic parameter a with the parameter value as Formula, and enter the formula if($a=1,-1,1), as shown in the following figure.

Sort After Expansion Setting 

Set Sort After Expansion on Cell A2. Select Cell A2, choose Cell Attribute > Expansion, click Add Main Sort Order, set Sort Basis to Formula, enter the formula if($a=1,F2,-F2), where F2 means to sort according to the value in Cell E2, and set Sort Rule to Ascend. 

Note:

If there are empty fields when you sort value-type fields, assign values to them in the formula. For details, see Sort FAQs.

Note:

This sorting method applies to value-type fields rather than string-type fields as negative numbers only exist in value-type fields.

Effect Display 

On PC

Save the template and click Pagination Preview. The effect is shown in the section "Application Scenario."

On Mobile Terminals

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

Data Preparation 

Create a general report, create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume where Region = 'New York'. 

Report Design 

Drag data from the dataset into the table, set Data Setting of Cell A2 to List, and set the style, as shown in the following figure.

Dynamic Parameter Adding 

Select Cell B1 where the column header Sales Person is located, click Hyperlink, and add dynamic parameters following the settings in the table below.

The steps are shown in the following figure.

Sort After Expansion Setting 

Select Cell A2, choose Cell Attribute > Expansion, set Sort Basis to Formula, enter the formula INARRAY(EVAL($asc),SORTARRAY(EVAL($asc+"[!0]")))*$a, and set Sort Rule to Ascend.

The following table describes the formula.

Formula	Description
EVAL($asc)	Return the expression $asc, which is the calculated result of Cell B2.
$asc+"[!0]"	Returns the concatenated string (B2[!0]), which is all values expanded from Cell B2.
SORTARRAY(array)	Returns the sorted array. Note: If the type of array elements is inconsistent or array elements cannot be compared, the original array will be returned.
INARRAY(co,array)	Returns the position of co in the array array, if co is not in array, 0 will be returned.

The steps are shown in the following figure.

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 templates.

Click the column header to change the sort order_ex1.cpt

Click the column header to change the sort order_ex2.cpt