I. Overview
1. Expected effect
In the document Order switching by number of clicks - Numerical value, it describes how to click the data column title to achieve ascending and descending sorting. Then, what if there are multiple columns want to achieve this ascending and descending function? As shown in the figure below:
2. Implementation ideas
This method is realized by adding a hyperlink of "dynamic parameter" type and adding a formula in the sorting after expansion. That is, when you click the header of a data column, the cell in which the data column is located is passed as a parameter. The parameter is received in the sorting formula after cell expansion, and different sorting is realized according to different parameters.
Note: it is also applicable to dashboard.
II. Example 1
1. Data preparation
Create a new data set DS1 and add SQL query statements: select empid, empname, bithdate, height from employee_copy limit 10.
2. Report design
Drag the empid, empname, birthdate and height fields to cells A3 ~ D3 respectively, and enter the corresponding title. The table style is shown in the following figure:
3. Hyperlink parameter setting
Select cells A2, C2 and D2 respectively, select hyperlink on the right and adddynamic parameters. The parameter settings of each cell are as follows:
Note: in this example method, data of string type cannot be sorted.
| Cell | Parameter | Type | Value | Description |
|---|---|---|---|---|
| A2 | a | Formula | if(a==-1,1,-1) | Parameter a means to replace the value of a once by clicking the hyperlink. 1 represents ascending order and -1 represents descending order. The parameter ASC indicates the cells to be sorted. The date time type data needs special settings. |
| asc | String | A3 | ||
| C2 | a | Formula | if(a==1,-1,1) | |
| asc | String | tointeger(format(C3,'yyyyMMddHHmmss')) Note: if the time type only contains the date, the parameter is: tointeger (replace (C3, "-", ")) | ||
| D2 | a | Formula | if(a==1,-1,1) | |
| asc | String | D3 |
The steps are as follows:
4. Sort after extension
Select cell A3, select expand in cell attributes > ExpandSort, select ascending order, and enter the formula if ($a = = 1, Eval ($ASC), Eval ($ASC) * - 1.
The formula is described in the following table:
| Formula | Formula description |
|---|---|
eval(exp) | Returns the calculated result of the expression exp. exp: a string in the form of an expression |
| $asc | returns the value of the parameter $asc |
| if($a==1,eval($asc),eval($asc)*-1) | if parameter a is 1, it will be sorted in ascending order according to the value of ASC; otherwise, it will be sorted in descending order according to the value of asc |
The steps are as follows:
Note: the following cells are expanded following the first column. Only when "sort after expansion" is set in the top left parent cell is valid.
5. Effect preview
PC terminal
Save the report and click pagination preview. The effect is the same as that expected in Section I.1.
Mobile terminal
For the way to view the report on the mobile terminal, please refer to preview mobile report. Both app and H5 support it, and the effect is shown in the following figure:
III. Example 2
In example 1, data of date time type needs special setting, and data of string type cannot be sorted. So how to sort numbers, dates and strings?
Taking example 1 template as an example, the main difference is the formula of sorting after expansion, which will be explained briefly below.
1. Hyperlink parameter setting
Add hyperlink > dynamic parameters to cells A2, B2, C2 and D2. The parameter settings are as follows:
| Cell | Parameter | Type | Value | Description |
|---|---|---|---|---|
| A2 | a | Formula | if(a==-1,1,-1) | if($a=1,-1,1) means to replace the value of a once by clicking a hyperlink. 1 represents ascending order and - 1 represents descending order. The value of parameter asc is the cell to sort. |
| asc | String | A3 | ||
| B2 | a | Formula | if(a==1,-1,1) | |
| asc | String | B3 | ||
| C2 | a | Formula | if(a==1,-1,1) | |
| asc | String | C3 | ||
| D2 | a | Formula | if(a==1,-1,1) | |
| asc | String | D3 |
2. Sorting after expansion
Select cell A3 and modify the sorting formula after expansion as follows: INARRAY(EVAL($asc),SORTARRAY(EVAL($asc+"[!0]")))*$a
The formula is described in the following table:
| Formula | Description |
|---|---|
EVAL(exp) | Returns the calculated result of the expression exp. exp: a string in the form of an expression |
| $asc+"[!0]" | returns the spliced characters. If the value of parameter $ASC is A3, then A3 [!0] is returned, that is, all the expanded values of A3 cell |
| SORTARRAY(array) | Returns an ordered array Note: when the array elements have inconsistent types or cannot be compared, the original array is returned. |
| INARRAY(co,array) | returns the position of co in the array. If co is not in the array, it returns 0 |
| INARRAY(EVAL($asc),SORTARRAY(EVAL($asc+"[!0]")))*$a | First use SORTARRAY to sort all the values of the obtained cell extension, then use INARRAY to return the position of the current cell value in all the sorted values, and then multiply by the parameter $a to sort in ascending and descending order. |
3. Effect preview
Save the report and click pagination preview. Both app and H5 support it, and the effect is similar. The effect is as follows:
IV. Template download
For completed templates, please refer to:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\MultiField_Sorting1.cpt
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\MultiField_Sorting2.cpt
Click to download the template:
V. Precautions
1. The data is not supported to be empty
If you meet the problem that the sorting is invalid, make sure to check if there is an empty data in the dataset. When the storage type of the field is integer, decimal and other data types, the data is not supported to be empty, otherwise the sorting will be invalid.
2. The dashboard does not support tab block multi tab dynamic sorting
In the dashboard, when multiple tabs in the tab block use the report block to set dynamic sorting, only the first tab page is valid, and the next tab page will return to the first tab page when you click Sort. The effect is as follows:
Previous：Advanced Sort
