Advanced Sort

Version

Application Scenarios

You may want to display single or multiple columns of data in ascending or descending order. For example, in an order table, the Fee column is sorted in descending order.

Functions

In advanced sort, the following effects can be achieved:

• Single/multiple columns are sorted in the set order during template preview.

• Custom sort.

Advanced sort (sort before expansion) is based on the data and sort rules of one or more data columns.

If Result Set Filter and Sort are used at the same time, the data will be first sorted and then filtered.

FineReport of Versions Earlier than 11.0.3

Note:

General reports and dashboards are supported.

Advanced sort must be set in the top-level parent cell. For example, if the left parent cell of cell C1 is cell B1 and that of cell B1 is cell A1, then cell A1 is the top-level parent cell.

Double-click the top-level parent cell to open the Data Column setting dialog box and click Advanced. The default value of Sort Rule is Unsort and the formula input box cannot be edited.

After selecting Ascend or Descend, you can edit in the formula input box. You can directly input the name of the data column or a formula containing the data column to set the sort. If no formula is inputted, the data is sorted in ascending or descending order based on the current column by default. 

You can also choose Cell Element > Advanced to set the sort.

FineReport 11.0.3 and Later Versions

Note:

1. Ensure that the data setting of the top-level parent cell is set to List, otherwise the sort does not take effect.

2. If the heading sort function in data column sort is used, advanced sort does not take effect during data entry preview/data analysis preview and take effect only for general reports and dashboards. For details, see Sort After Expansion.

Entries are shown in the following figure.

• Single-column sort is set by adding Main Sort Order, and multi-column sort is set by adding Secondary Sort Order after Main Sort Order is added.

• For example, if you want to sort the column where cell B is located, you are advised to set advanced sort in the top-level parent cell or cell B. If cell B is the parent cell of cell C, setting the sort for cell B in cell C may not take effect.

• If you set Sort Area to cell B in Main Sort Area and Sort Area to cell C in Secondary Sort Order for the column where cell A is located, the data will be first sorted by cell B, and then sorted by cell C, achieving the sort by multiple columns after expansion.

The following table describes these settings.

This section illustrates how to set the single-column sort (based on only one data column).

Designing a Report

1. Create a blank general report and create a database query dataset ds1, with the SQL statement: SELECT * FROM Order.

2. Design the table according to the following figure and drag the fields to corresponding cells.

3. Set Format to Date (in yyyy-MM-dd format) for the two date fields in cell C2 and cell D2.

4. Set Format to Number (in #0.00 format, displaying two decimal places) for cell F2.

Setting Sort

1. Select the top-level parent cell A2 and set Data Setting to List for this cell.

2. Select cell A2 and click Cell Element, click the Advanced tab, and click Add Main Sort Order under column sort.

3. Set Sort Area to Fee, Sort Basis to Cell (determining ascending or descending order according to numerical value), and Sort Rule to Descend.

In this case, during preview, the data is sorted in descending order based on the data column Fee.

Demonstration

Save the report. The pagination preview effect is shown in the figure in section "Application Scenarios."

Preview on app and HTML5 terminals is supported.

Multi-column sort can be set in advanced sort, allowing you to perform combined sort based on multiple data columns.

For example, the overall data can be sorted in descending order based on the Order Date field, and the data with the same order date can be sorted in ascending order based on the Fee field.

To realize this, you just need to set the main and secondary sort order. The setting methods are shown in the following figure.

Click to download the template:

Single-Column Sort.cpt

Multi-column Advanced Sort.cpt