Special Multi-column Sort

Application Scenario

Based on the effect of the multi-column sort, the report block of the dashboard can sort data in descending, ascending, and unsorted order respectively when you click the column header for consecutive three times. The following figure shows the effect.

Implementation Method

You can achieve the effect of the special multi-column sort by using the Sort After Expansion and the Hyperlink (JavaScript type) functions. Three parameters, which can help you to achieve the effect in this document, exist in JavaScript.

Parameter a: marks the ascending/descending order.

Parameter b: marks the current field.

Parameter c: marks the last click.

Table Design

1. Create a blank dashboard and create a dataset named ds1. The SQL statement is SELECT * FROM ORDERS limit 20.

2. Drag a report block named report0 (that must be consistent with the one used in the following JavaScript codes) into the body of the dashboard. Design the table of the report block, as shown in the following figure.

Sort After Expansion Setting

Select cell A2 and set Sort Basis to Formula and switch($a,0,"",1,eval($c)*-1,2,eval($c)) and Sort Rule to Ascend in Sort After Expansion.

JavaScript Hyperlink Setting

Select cell B1, add a hyperlink, select JavaScript from the drop-down list, add three parameters, and enter JavaScript codes, as shown in the following figure.

The following table describes the added parameters.

JavaScript codes are as follows:

if(a==null||b!=c||a.length==0)
{
_g().getWidgetByName("report0").gotoPage(1, {a:1,b:b,c:b}, true)
}
if(a==1&&b==c)
{
_g().getWidgetByName("report0").gotoPage(1, {a:2,b:b,c:c}, true)
}
if(a==2&&b==c)
{
_g().getWidgetByName("report0").gotoPage(1, {a:'',b:b,c:c}, true)
}

Note:

Remember to modify the name of the report block in the code when you apply the template.

After finishing the setting of cell B1, set similar hyperlinks for cell C1, cell D1, cell E1, and cell F1, for which the values of parameters a and c are the same as those of cell B1.

The values of parameter b in the JavaScript hyperlink for cell C1, cell D1, cell E1, and cell F1 are c2, d2, e2, and f2 respectively.

The following figure shows the final table result.

Effect Display

Save the template and click PC Preview. The effect is shown in section "Application Scenario."

The template can be previewed on the DataAnalyst and HTML5 apps.

You can download the example template Special Multi-column Sort.frm.