第1行をクリックしてソート方式を変更

1. Problem description

When making reports, we often encounter the need to switch the display in ascending and descending order by clicking the title. In the Sort in dataset, you can define data set parameters and use dynamic parameters to achieve dynamic sorting by clicking the title.

However, in some cases, data set sorting cannot be used. For example, when the report is made, one column is calculated by other data columns. In the figure below, the profit is derived from (Unit_price-Purchasing_price)*Quantity. How to achieve dynamic sorting by directly clicking on the title?

2. Solutions

It is achieved by adding a Dynamic Parameter type of Hyperlink and adding a formula to the cells in the Sort after 

Expansion, that is, when you click the data column header, the cell where the data column is located will be passed as a parameter, and the expanded sorting formula will receive the Parameters, according to different parameters, realize different sorting.

Note 1: The settings of numeric type and character type are different. See the example for details.

Note 2: This article explains only one-column data transformation and sorting. For multi-column dynamic sorting, please refer to the document Sorting on multiple fields.

1. Data preparation

Create a new report, create a new database query ds1, the SQL query statement is: SELECT * FROM SIndentDetail 

limit 20. As shown below: 

2. Design report

Drag the data in the data set into the table, set the Data Setting of cell A2 to List, insert the formula in cell F2: (C2-E2) * D2, and design the report style as shown in the figure below:

3. Add dynamic parameters

Select the F1 cell where the Profit heading is located, and add a Hyperlink>Dynamic Parameters 'a', the parameter value is the formula: if($a=1,-1,1), as shown in the figure below:

4. Set the sort after expansion

Select cell A2, select Cell Attributes> Expand, set Expandsort to Ascending enter the formula: if($a=1,F2,-F2), F2 means sort according to the value of F2 cell. As shown below:

Note: If the field is empty when sorting a numeric field, you need to assign a value to the null value in the sorting formula. 

Note: Since only numeric data has negative numbers, and character data does not have negative numbers, this sorting method is only applicable to numeric field sorting.

5. Effect preview

1） PC side

Save the report, click Pagination Preview, the effect is consistent with the rendering in section 1.1.Problem description.

1. Data preparation

Create a new general report, create a new database query ds1, the SQL query statement is: SELECT * FROM Sales_ Volume where Region ='North China'. As shown below:

2. Design report

Drag the data in the data set into the table, set the Data Setting of cell A2 to List, the report style is as shown in the figure below:

3. Add dynamic parameters

Select cell B1 where the title of Sales Person is located, and add a Hyperlink>Dynamic Parameters. The parameter settings are as follows:

The steps are shown in the figure below:

4. Set the sort after expansion

Select cell A2, choose Cell Attributes>Expand, set Expandsort to Ascending, enter the formula: INARRAY(EVAL($asc),SORTARRAY(EVAL($asc+"[!0]")))* $a.

The formula description is as follows:

The steps are shown in the figure below: 

5. Effect preview

1） PC side

Save the report, click Pagination Preview, the effect is as shown in the figure below:

The completed template can be found in:

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Click on the title to change the sort order_ex1.cpt

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Click on the title to change the sort order_ex2.cpt

Click to download the template:

Click on the title to change the sort order_ex1.cpt

Click on the title to change the sort order_ex2.cpt