JS Click header to sort in the dashboard

In the dashboard, you want to click the title to sort, and you can refresh the report block.

2.1 Single column sort

You can decide whether to sort ascending or descending according to the parameter value. This idea can also be implemented here. Define a parameter. If the parameter value is 1, it will be sorted in ascending order. When the parameter value is 0, the data column will be sorted in ascending order according to the negative number of the data column. That is, the data column will be sorted in descending order; and refresh the report block according to different parameter values.

2.2 Multi-column sort

Set the expanded sorting formula for the leftmost cell; Each time the title is clicked, the report is assigned a value to achieve the effect of dynamic sorting.   

3.1 Report design

Create a new dashboard and drag into the report block to create a new data query ds1: 

SELECT * FROM ORDERS limit 15

Drag in the order ID field to set the report block style, as shown in the figure below:

3.2 Set up hyperlink

Right click on cell A2, add hyperlink-JavaScript, add parameter a, the value is the formula: if($a = 1, 0, 1) , as shown in the figure below:  

JavaScript:

var re=_g().getWidgetByName("report0");
re.gotoPage(1,"{a:"+a+"}",true);

3.3 Set sorting

Double-click cell A3, select Advanced, and select ascending order in the sort order , the formula value is if($a==1,$$$,$$$*-1) , as shown in the figure below:  

3.4 Preview effect

Save the template, and the preview effect is shown in the figure below:

4.1 Data preparation

Create a new dashboard and add dataset ds1: 

SELECT EMPID, EMPNAME, BIRTHDATE, HEIGHT FROM EMPLOYEE limit 10

4.2 Report design

Drag in a report block, and the report block style setting is shown in the figure below:

4.3 Set up hyperlink

1) Right-click cell A2, add a hyperlink- JavaScript, add JS and add parameters a, the formula is if($a = 1, 0, 1) and flag, the value is 1, as shown in the following figure:   

JavaScript:

var re = _g().getWidgetByName("report0"); re.gotoPage(1, "{a:" + a + ",flag:" + flag + "}", true);

2) In the same way, add a hyperlink- JavaScript to cell D2 , add JS and add parameters b (if($a = 1, 0, 1)) and flag (2), as shown in the figure below: 

JavaScript:

var re = _g().getWidgetByName("report0"); re.gotoPage(1, "{b:" + b + ",flag:" + flag + "}", true);

Note: Set JS for ID and height respectively to pass parameters to report block report0. Here use a, b and flag parameters to distinguish which title is clicked and the number of clicks.

4.4 Set sorting

Select cell A2, set cell attributes, select ascending order at the expanded sort, and the formula value is if($flag = 1, if($a = 1, A2, -A2), if($b = 1, D2, D2*-1)) as shown in the figure below:  

Note: Use the if statement to judge the flag value, and then judge the parameter values of a, b, and c to achieve the corresponding cell data to sort.

4.5 Set formula

If the A3 cell of the hyperlink is a string type number, and you need to sort it and need to convert it to a number type, double-click the A3 cell, click Advanced, in the custom display, change the value to the formula: TOINTEGER($$$) 

If you need to sort the date type data, use tointeger(format(I2, "yyyyMMddHHmmss") to convert it to a number type for sorting.

4.6 Preview effect

Click the preview, the effect is shown in the figure below:

5.1 Example 1: Single column sort

Example 1 Single column sort.frm

5.2 Example 2: Multi-column sort

Example 2 Multi-column sort.frm