Overview
Expected Effect
Sometimes you may want to achieve the effect of dynamic non-interference sort in groups in the same report. For example, when you click the sales volume of New York, the table of New York changes its sort order accordingly without affecting the sales volume of California. Similarly, when you click the sales volume of California, the table of California changes its sort order accordingly without affecting the sales volume of New York.
Implementation Method
Step one: Define an array length based on the number of regions and use the elements in the array to store the ascending or descending order of corresponding groups (1 for ascending and -1 for descending).
Step two: When the sales volume is clicked, delete the value corresponding to the clicked position in the array, and insert the value that becomes the opposite number into the corresponding position in the array.
Step three: Set sort after expansion. Obtain the value corresponding to the clicked position in the array, then multiply it by the cell value where the sales volume group is located, and change the sort in ascending order based on positive and negative numbers.
Example
Data Preparation
Create a general report, create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume where Product='Apple Juice'.
Report Design
Merge cells A1 to D1, drag the Region data column into cell A1, enter the titles in cells A2 to D2 as shown in the following figure, and drag the corresponding data columns into cells A3 to D3. Customize the table style and the final effect is shown in the following figure.
Dynamic Parameter Adding
Select cell D2 and add a dynamic parameter to it in Hyperlink. Set the parameter name to a, and the parameter value to the formula type, and enter the formula JOINARRAY(ADD2ARRAY(REMOVEARRAY(IF(LEN($a) = 0, RANGE(COUNT(A1[!0]))*0, SPLIT($a, ",")), &A1, 1), IF(INDEXOFARRAY(SPLIT($a, ","), &A1) = 1, -1, 1), &A1),",").
The dynamic parameter is to return parameters according to the rules defined by the formula when the sales volume is clicked, and then sort data based on the parameters.
The following tables describe the formula.
Step one: IF(LEN($a) = 0, RANGE(COUNT(A1[!0]))*0, SPLIT($a, ","))
Formula | Description |
LEN($a) = 0 | When the report is initializing, parameter a is empty, that is, its length is equal to 0, and it is assigned a value after the first click. |
RANGE(COUNT(A1[!0]))*0 | Calculates the sum of the expanded values of cell A1, defines an array with the same length as the values, and multiplies by 0 to avoid changing the parameter values when the sort is performed for the first time. |
SPLIT($a, ",") | The parameters will be automatically converted to strings during the transmission process, so SPLIT is used to separate parameter a with commas into a string array. |
Step two: REMOVEARRAY(Step one result, &A1, 1)
Formula | Description |
REMOVEARRAY(Step one result, &A1, 1) | Uses &A1 to get the position of the clicked group, then deletes a value at that position in the array returned in step one, and returns the array after deletion. That is, deletes the value corresponding to the clicked position in the array. |
Step three: ADD2ARRAY(Step two result, IF(INDEXOFARRAY(SPLIT($a, ","), &A1) = 1, -1, 1), &A1)
Formula | Description |
INDEXOFARRAY(SPLIT($a, ","), &A1) | Uses &A1 to get the position of the clicked group, and returns the value of this position in the comma-separated string array. |
IF() | Uses IF to determine the value of the array, and uses 1 and -1 to change the order. Each click will change the original value to its opposite value. |
ADD2ARRAY() | Uses &A1 to get the position of the clicked group, and inserts all elements of the opposite value array into the position where the returned array is located in Step two, and then returns that array. |
Step four: JOINARRAY(step three result, ",")
Formula | Description |
JOINARRAY(Step three result,",") | Dynamic parameters cannot return an array in array type, so you need to use an array function to convert the result of Step three into a string with commas as separators. |
Sort After Expansion Setting
Select cell A3 and set Sort After Expansion. Set Sort Rule to Ascend, and Sort Basis to Formula, and enter the formula D3 * INDEXOFARRAY(split($a, ","), &A1).
INDEXOFARRAY(split($a, ","), &A1) represents using &A1 to obtain the position of the clicked group, and returning the value at this position in the comma-separated string array, which is either 1 or -1.
Then, the value of cell D3 is multiplied by 1 or -1. When sorted in ascending order of positive numbers, the Sales Volume column is ascending; when sorted in ascending order of negative numbers, the Sales Volume column is descending.
Effect Display
On PC
Save the template and click Pagination Preview. You can see that the effect is the same as section "Expected Effect."
On Mobile Terminals
The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.
Template Download
You can download the example template.
