Sort After Expansion

Version

Application Scenario

You may want to sort data by a single column or multiple columns in ascending or descending order by clicking table headers. For example, in a table of orders, you may want to sort the Shipping Cost column in ascending or descending order by clicking the header of the column, as shown in the following figure.

Note:

The sorting button of frozen columns does not move with the cell.

Function Description

You can find the Set After Expansion function under Cell Attribute > Expansion.

The function can help you achieve the following effect.

Click the table header to sort data by a single column or multiple columns in ascending or descending order.

Sort data by a single column or multiple columns in the set order during the template preview.

Customize a sort.

If Result Set Filter and Sort After Expansion are applied at the same time, the data is first filtered and then sorted.

As the most flexible sort method, sort after expansion can meet various sort requirements during report creation. However, the sort is performed after expansion and may adjust the positions of all cells, leading to the worst sort performance. You are advised to use this function only when advanced sort cannot meet the requirements.

FineReport of Versions Earlier than 11.0.3

Note:

General reports and dashboards are supported.

Configure Sort After Expansion under Cell Attribute > Expansion > Advanced. It is usually set on the left parent cell. For example, if the left parent cell of cell C1 is B1, and the left parent cell of B1 is A1, then A1 is set to the sort basis.

If you set Sort Rule to Ascend for cell A1 (the left parent cell), and Sort Basis to Formula, and enter C1 in the formula, cell A1 will be sorted based on the calculation result of cell C1, as shown in the following figure.

FineReport 11.0.3 and Later Versions

Note:

1. Ensure that the data setting of the left parent cell is set to List, otherwise the sort after expansion does not take effect.

2. Data Entry Preview and Data Analysis Preview are not supported currently. You can preview the effect, but the header setting does not take effect. The function is available for general reports and dashboards.

The setting page of Sort After Expansion is shown in the following figure.

Add Main Sort Order is to sort by a single column. To sort by multiple columns, you can click Add Main Sort Order and then click Add Second Sort Order.

If Header Area and Header Setting are set, dynamic sort can be achieved during the template preview. If not, the sort will be performed according to the set sort rules during the template preview.

For example, if you want to sort the column where cell B is located, you are advised to set Sort After Expansion on the left parent cell or cell B. If cell B is the parent cell of cell C, setting the sort for cell B on cell C may not take effect.

If you set Sort Area to cell B in Main Sort Order and Sort Area to cell C in Second 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 multi-column sort in Ascending order after expansion.

The following table describes specific settings.

Settings	Introduction
Sort Area	The sorted content. Click the right button to select the data column to be sorted.
Sort Basis	Three options are included: Cell Formula Custom Order Note: Data of the following types can be sorted: time, date, string, number, and boolean.
Sort Rule	Three options are available: Ascend, Descend, and Unsort.
Header Area	Set the header with the same method as that of Sort Area. The header cannot be repeated. The set header turns grey and cannot be selected again.
Header Setting	Tick Allow Users to Click Sort Order to set the sort rule for dynamic sort, including Ascend, Descend, and Unsort. Click the icon in the red box in the following figure to set the color of the sort icon.

This section demonstrates the effect of a single-column sort, and you can click the table header for dynamic sort.

Note:

Skip the section "Header Setting" if dynamic sort is not required.

Report Design

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

2. Drag the fields into corresponding cells, as shown in the following figure.

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

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

Sort Setting

1. Select the left parent cell A2 and set Data Setting to List.

2. Select cell A2, and click Add Main Sort Order under Cell Attribute > Sort After Expansion.

3. Set Sort Area to cell F2, Sort Basis to Cell, and Sort Rule to Descend.

In this way, all data are sorted in descending order by the Shipping Cost field by default when you preview the template.

Header Setting

Set Header Area to cell F1, tick Allow Users to Click Sort Order, and retain the default setting of Sort Basis.

In this way, when you click the header of the Shipping Cost column during the preview, three sort options are available for a dynamic sort.

Effect Display

On PC

Save the template. The pagination preview effect is shown in the section "Application Scenario."

On Mobile Terminals

Click the header icon to sort.

This section demonstrates how to set the main sort order to cell C2 (Order Date), and the second sort order to cell F2 (Shipping Cost), to achieve a preview effect that the data are first sorted by Order Date and then by Shipping Cost for data with the same Order Date. You can click the header of Order Date or Shipping Cost to display the data column in descending or ascending order separately.

Note:

Skip the section "Header Setting" if dynamic sort is not required.

Report Design

For details, see the above section "Report Design."

Sort Setting

1. Select the left parent cell A2 and set Data Setting to List.

2. Select cell A2, and click Add Main Sort Order under Cell Attribute > Sort After Expansion.

3. Select cell A2 and add the main sort order and second sort order.

Main sort order: Set Sort Area to cell C2, Sort Basis to Cell, and Sort Rule to Descend.

Second sort order: Set Sort Area to cell F2, Sort Basis to Cell, and Sort Rule to Ascend.

This step is to sort the data first by Order Date, and then by Shipping Cost for data with the same Order Date during the template preview.

Header Setting

You can add multiple headers in the following ways.

Select cell A2, set Header Area to cell C1, and tick Allow Users to Click Sort Order.

Select cell F2, set Header Area to cell C1, and tick Allow Users to Click Sort Order.

For example, the steps of setting cell F2 are shown in the following figure.

Effect Display

On PC

The following figure shows the effect on PC.

On Mobile Terminals

Click the table header to sort on DataAnalyst and HTML5 apps.

The following GIF shows the effect.

You can download the example template.

Sort After Expansion by a Single Column.cptDynamic Sort After Expansion by Multiple Columns.cpt