Overview
Version
Report Server Version | Functional Change |
11.0.4 | Added the SORT function. |
Application Scenario
You want to rank the data.
For example, you can rank the scores, as shown in the following figure.
Function Description
The SORT function is added in version 11.0.4, which supports ranking data.
Note:The hierarchical coordinate function only supports cell calculations and does not support cross-sheet calculations, watermarks, JavaScript references, or default parameter values.
Function Explanation
(1) Overview
Syntax | SORT(number,boolean1,boolean2,range) | Calculate the ranking. |
Parameter 1 | number | Set the range of data to be ranked. |
Parameter 2 | boolean1 | Set ascending or descending ranking. This parameter can be omitted, and its default value is true if omitted. true: descending order false: ascending order |
Parameter 3 | boolean2 | Set whether to skip the expected sequence numbers after tied ranks. This parameter can be omitted, and its default value is true if omitted. true: No rank skipping. That is, after a tied rank occurs, the subsequent rank does not skip the expected sequence number. For example: 1, 2, 3, 3, and 4. false: Rank skipping. That is, after a tied rank occurs, the subsequent rank skips the expected sequence number. For example: 1, 2, 3, 3, and 5. |
Parameter 4 | range | Set groups for ranking. That is, whether ranking is performed by group. This parameter can be omitted. Omitted: Group ranking is not performed. That is, rank all data. Not omitted: Ranking is performed by group. |
(2) Example
If cell B1 contains student scores, then SORT(B1) means ranking all student scores in descending order without rank skipping. SORT(B1,false) means ranking all student scores in ascending order without rank skipping.
Example
Report Creation
Create a general report, as shown in the following figure.
Data Preparation
Create a built-in dataset Embedded1, as shown in the following figure.
Report Design
(1) Enter text in cell A1 and drag the corresponding data column to cell A2, as shown in the following figure.
(2) Enter text in cells B1 to D1, enter the formula sort(A2,false) in cell B2, enter the formula sort(A2,true,false) in cell C2, and enter the formula sort(A2,true,true) in cell D2, as shown in the following figure.
(3) The following table explains the formula.
Formula | Description |
sort(A2,false) | Rank the scores in ascending order without rank skipping after tied ranks. |
sort(A2,true,false) | Rank the scores in descending order with rank skipping after tied ranks. |
sort(A2,true,true) | Rank the scores in descending order without rank skipping after tied ranks. |
Effect Display
(1) PC
Save the report and click Pagination Preview. The following figure shows the preview effect.
(2) Mobile terminal
The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.
Template Download
Download the completed template by clicking SORT.cpt.
Previous：MOM — Calculating Comparison with the Same/Previous Period
