Overview
Version
| Report Server Version |
|---|
| FineReport of versions earlier than 11.0.3 |
Application Scenario
The ordinary sort fails in the face of a report with a multi-level list. So how to sort a multi-level list?
Implementation Method
A multi-level list is a sequence of strings consisting of numbers separated by periods (.) and can be sorted by converting strings into numbers.
Example
Data Preparation
Create a general report, create a built-in dataset Embedded1, and add two fields Project Symbol and Project, as shown in the following figure.
Table Design
Drag the fields in the built-in dataset into cells A2 and B2, and enter the titles, as shown in the following figure.
Sort After Expansion Setting
Select Cell A2 and set Sort After Expansion as shown in the following figure. The entered formula is SUM(MAPARRAY(split(A2, "\\."), item * POWER(100, 4 - index))).
The formula is to split the project symbols in Cell A2 with the period characters (.), multiply the obtained numbers by different squares of 100, and sum the results up.
The following table describes the formula.
| Formula | Description |
|---|---|
| POWER(100,4-index) | Returns the result of a number raised to a power, that is, 100 raised to the power of 4 - index index: Returns the position. Note: |
| SPLIT(A2, "\\.") | Returns a string array that contains the substrings that are delimited by the period characters (.) of values in Cell A2. |
| MAPARRAY() | Process the array in batches by converting the elements in the array results of split(A2, "\\.") using item * POWER(100, 4 - index). |
| SUM() | Sum up the converted arrays. |
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.
Template Download
You can download the example template. Sort a multi-level list.cpt
