Multi-column Dynamic Sort

Note:

This document is a reference for users with FineReport versions earlier than 11.0.3. Users with FineReport 11.0.3 and later versions can refer to Sort After Expansion.

Expected Effect

When designing a report, you may hope to sort data of multiple columns in ascending/descending order by clicking the corresponding column header. For example, you hope to sort data in the first column (ID) in ascending/descending order by clicking the column header ID and sort data in the second column (Name) in ascending/descending order by clicking the column header Name, as shown in the following figure.

Implementation Method

You can add dynamic parameters in Hyperlink and add a formula in Sort After Expansion to dynamically sort data in multiple columns. That is, when you click a data column header, the cell (where the data column is located) is passed as a parameter. Then the parameter is received by the formula in Sort After Expansion to achieve different sorts based on different parameters.

Note:

1. If data to be sorted includes character-string fields, the formula in Sort After Expansion will be different. You can refer to the two examples in this document.

2. The implementation method is also applicable to dashboards.

3. If too many pages or multiple tab pages (in the Tab block of a dashboard) exist in data to be dynamically sorted, the system will refresh the page and jump to the first page after you click the column header to sort data in another page, because the dynamic parameters will refresh the entire page.

Data Preparation

Create a dataset named ds1 and enter the SQL query statement SELECT EMPID, EMPNAME, BIRTHDATE, HEIGHT FROM EMPLOYEE limit 10.

Report Design

Drag the fields EMPID, EMPNAME, BIRTHDATE, and HEIGHT into cells (from cell A3 to cell D3), enter the corresponding column headers, and set the table style, as shown in the following figure.

Dynamic Parameter Adding in Hyperlink

Select cell A2, cell C2, and cell D2 respectively, click the Hyperlink icon on the right, and add dynamic parameters. The following table describes the parameter setting of each cell.

Note:

In this example, character-string data cannot be sorted.

Sort After Expansion Setting

Select cell A3, click the Cell Attribute icon, click Expansion, set Sort Basis to Formula and Sort Rule to Ascend, and enter the formula if($a==1,EVAL($asc),eval($asc)*-1).

The following table describes the formula.

Note:

The subsequent cells are expanded based on the first column. Therefore, the setting of Sort After Expansion is only effective in the top-level left parent cell.

Effect Display

On PC

Save the template and click Pagination Preview. The effect is shown in section "Expected Effect."

On Mobile Terminals

The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.

Character-string data cannot be sorted in example one. Therefore, you need to set date and time data separately. This example mainly introduces how to sort value, date, and character string data together.

Taking the template in example one as an example, this section introduces a different formula in Sort After Expansion.

Dynamic Parameter Adding in Hyperlink

Add dynamic parameters in Hyperlink for cell A2, cell B2, cell C2, and cell D2. The parameter setting is as follows.

Sort After Expansion Setting

Select cell A3 and modify the formula in Sort After Expansion into INARRAY(EVAL($asc),SORTARRAY(EVAL($asc+"[!0]")))*$a.

The following table describes the formula.

Note:

If empty values exist in the data column, the sort of decimal/integer data may fail. You need to perform filtering before sorting.

Effect Display

On PC

Save the template and click Pagination Preview. The preview effect is shown in the following figure.

On Mobile Terminals

The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.

You can download the example templates Multi-column Dynamic Sort 1.cptMulti-column Dynamic Sort 2.cpt.