Overview
Expected Effect
You may want to adjust the order of the report according to your needs during the preview. In this case, you can move the current row by clicking the up and down buttons on the page, as shown in the following figure.
Implementation Method
Define an array to store the sort order. When you click the up or down button, the values at the corresponding positions in the array will be exchanged. Then, you can sort the data according to this array.
Example
Dataset Creation
Create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume.
Report Design
Enter the formula &B2 in Cell A2, and set the left parent cell to B2. Set Data Setting of cell E2 to Summary-Sum and then hide column A.
Dynamic Parameter Adding
1. Select cell E2, add a dynamic parameter named a, and set the parameter value to the formula type, as shown in the following figure.
let(arr, if(len($a) = 0, range(count(B2[!0;!0])), $a), let(idx, inarray(A2, arr), if(idx = 1, arr, maparray(arr, if(index = idx, indexofarray(arr, idx - 1), if(index = idx - 1, indexofarray(arr, idx), item)))))
2. Select cell F2, add a dynamic parameter named a, and set the parameter value to the formula type, as shown in the following figure. let(arr,if(len($a) = 0,range(count(B2[!0;!0])),$a),let(idx,inarray(A2,arr),if(idx = len(arr),arr,maparray(arr,if(index = idx,indexofarray(arr,idx + 1),if(index = idx + 1,indexofarray(arr,idx),item))))))
Formula description: Take the formula of cell E2 as an example.
Formula | Description |
let(arr, if(len($a) = 0, range(count(B2[!0;!0])), $a), ……) | Determines if array $a is empty. If so, expands a new array with the total number of rows in cell B2, otherwise, it is $a itself. Then assigns the final result to the variable arr. |
let(idx,inarray(A2,arr),……) | Calculates the position of the value in cell A2 in the array arr and assigns the result to the variable idx. |
if(idx = 1,arr,maparray(arr,if(index = idx,indexofarray(arr,idx - 1),if(index = idx - 1,indexofarray(arr,idx),item)))) | If idx is equal to 1, arr is returned, which means that the order will not change when you click the first row. If idx is not equal to 1, the value at the idx-th position in the arr array is replaced with the value at the idx-1 position, and the value at the idx-1 position is replaced with the value at the idx-th position. |
Sort After Expansion Setting
Select cell B2 and set it to Sort After Expansion. Set Sort Basis to Formula and INARRAY(&B2,$a), and Sort Rule to Ascend.
Effect Display
On PC
Save the template and click Pagination Preview. The effect is the same as that shown in section "Expected Effect."
On Mobile Terminals
The preview effects on the DataAnalyst and HTML5 apps are the same, as shown in the following figure.
Template Download
You can download the template.Dynamic Row Moving on Preview Page.cpt