Achieving Multi-Column Sort Through Dynamic Columns-FineReport Help Document

Last update：February 19, 2024

Overview

Expected Effect

Some users want to sort each column individually based on dynamic columns, as shown in the following figure.

Implementation Method

Sort each column after setting expansions based on dynamic columns achieved by functions.

Procedure

Template Preparation

1. Take the template in the document Achieving Dynamic Columns Through Functions as a basis and modify the template.

2. Enter Number in cell A1, as shown in the following figure.

3. Choose Template > Repeat/Freeze, click Pagination Preview, select Repeat Title Row From No. 1 Row to No. 1 Row, and click OK, as shown in the following figure.

4. Modify the SQL statement of ds1 to SELECT * FROM ORDERSDETAIL.

Dynamic Parameter Adding in Cell B1

Select cell B1 and add a hyperlink (dynamic parameter type) to cell B1.

Add two parameters order (value formula: if($order = 1, -1, 1)) and index (value formula: &B1), as shown in the following figure.

Note:

The value of the index parameter is &B1. That is, the value of index is the number of the clicked cell when you click the column header.

Sort After Expansion

Set Sort Basis to Formula and if(isnull($index), A2, if($order = 1, EVAL("B2[;B1:" + $index + "]"), EVAL("-1*B2[;B1:" + $index + "]"))) and Sort Rule to Ascend.

When order is 1, the values of the clicked column obtained by EVAL("B2[;B1:" + $index + "]") are sorted in ascending order. When order is -1, the values of the clicked column are first converted to negative numbers and then sorted in ascending order based on the negative numbers.

The following table describes the formula.

Formula	Description
isnull($index)	Checks whether the value of $index is an empty string. The value of $index is empty during initialization and then is the value of &B1 (the number of the clicked cell) after you click the column header. Note: If the value of $index is empty, you can sort by cell A2 to improve the performance of the first column header click.
"B2[;B1:" + $index + "]"	Returns the value of cell B2 corresponding to the *$index*-the value expanded from cell B1.
EVAL()	Returns a string expression.

Formula

Description

isnull($index)

Checks whether the value of $index is an empty string. The value of $index is empty during initialization and then is the value of &B1 (the number of the clicked cell) after you click the column header.

Note: If the value of $index is empty, you can sort by cell A2 to improve the performance of the first column header click.

"B2[;B1:" + $index + "]"

Returns the value of cell B2 corresponding to the $index-the value expanded from cell B1.

EVAL()

Returns a string expression.