I. Overview
1) Dynamic parameters are hyperlinks that control the values of the parameters.
2) This article introduces how to realize sorting through dynamic parameters and helps you understand the rationale of dynamic parameters through some test steps.
3) You may refer to Basic Settings of Hyperlinks.
II. Sort through dynamic parameters
1. Create a new dataset
1) Create a new DB Query dataset [ds1].
2) Use the following SQL statement to extract all salespersons and their total sales volume.
SELECT Salesperson,SUM(Sales_Volume) AS Sales
FROM Sales_Volume
GROUP BY Salesperson
3) Preview the dataset
2. Design a report
1) Drag data columns in [ds1] into B3 and C3.
2) Input Sales Person in B2 and input Sales in C2.
3) B2~C3: add borders and set center alignment.
3. Insert dynamic parameters
1) Click C2, insert a hyperlink in the right-hand pane and select Dynamic Parameters as the type.
2) The setting interface of [Dynamic Parameters] pops up.
4. Set dynamic parameters
1) Click + to add parameters.
2) Add the first parameter: the parameter is named flag and the type of its value is [Formula], input IF($flag==0,1,0) in the pop-up formula definition box.
3) Add the second parameter: the parameter is named orderBy and the type of its value is [Character] which is the default option, input C3.
5. Set ExpandSort rules
1) Click B2, select [Cell Attributes] in the right-hand pane and click in the [Advanced] toolbar.
2) Click and input IF($flag == 1, EVAL($orderBy), -1 * EVAL($orderBy)) in the pop-up formula definition box. The formula means:
If the value of flag is 1, sort in ascending order of C3.
If the value of flag is 0, sort in descending order of C3.
6. Preview
Sales is displayed as a hyperlink. By clicking on it, data below it is sorted in ascending order; by clicking on it again, data below it is sorted in descending order.
III. Understand the rationale of dynamic parameters
1. Test the value of flag
1) Input “value of flag:” in B5, insert a formula into C5 and input IF(LEN(flag)==0, "null", $flag) in the pop-up formula definition box.
2) In the preview mode, it can be found that flag is initially null. Click to see the change in the value of flag.
Explanation
The value of flag is initially null. Click Sales for the first time and IF($flag==0,1,0) is executed. Since the value of flag is not 0 (is null by now), the result of $flag==0 is False and the return value of the IF function is 0. The IF function, in effect, is to assign a value to flag using its return value, so the value of flag is displayed as 0.
Click Sales for the second time and IF($flag==0,1,0) is executed again. This time, the value of flag is 0, and this makes the result of $flag==0 is True, so the result of the IF function is 1 and the value of flag is displayed as 1.
2. Count through dynamic parameters
1) Click C2, add a new dynamic parameter named sum, select [Formula] as the type of the parameter value and input IF(LEN(total)==0, 0, $total+1) in the pop-up formula definition box.
2) Input “value of total:” in B6, insert a formula in C5, and input IF(LEN(total)==0, "null", $total) in the pop-up formula definition box.
3) Preview. Initially, total is null. Every click will increase value of total: by 1.
Explanation
The value of total is initially null. Click Sales for the first time and IF(LEN(total)==0, 0, $total+1) is executed. As total is null, in which case the length is 0, making the result of LEN(total)==0 being True, so the return value of the IF function is 0 and the value of total is displayed as 0.
Click Sales for the second time and beyond, IF(LEN(total)==0, 0, $total+1) is also executed. In this case, the value of total is not null, making the result of LEN(total)==0 being False, so the return value of the IF function is $total+1 and the value of total is $total+1 either. In other words, value of total will increase by 1 after each click.
