I. Overview
1) Serial numbers can be used to identify a column of data.
2) This article introduces how to calculate a serial number using the hierarchical coordinate function.
II. Number rows
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL function:
SELECT Region, Salesperson, SUM(Sales_Volume) AS SalesFROM Sales_VolumeGROUP BY Region, Salesperson
2. Design the report body
1) Type headers.
2) Drag data columns in [ds1] into the cell range D3:F3.
3) Preview.
3. Calculate a serial number
1) Input the following function in B3: SEQ().
2) Set the parent cell of B3 to E3. SEQ() will return an integer indicating how many times it has been executed. At this time, the parent cell of the cell where SEQ() is located is E3, which means for each cell expanded from E3, SEQ() will be executed. Therefore, the number of rows in the data column [Salesperson] equals the number of times SEQ() has been executed.
3) Input the following formula in C3: SEQ(D3).
4) Set the left parent cell of C3 to E3. SEQ(D3) still returns the number of times SEQ() has been executed cumulatively, but only if the values of D3 in the same row are the same; if not, SEQ(D3) restarts from 1. The value of D3 can be regarded as the group number of SEQ() and what SEQ() returns is the number of times it has been executed cumulatively in the group.
4. Preview
Note
The SEQ function is capped at 2047, so it is applicable to sorting a table with no more than 2,047 rows.
Download the template
This template will be directly used in “III. Number rows with letters”.
III. Number rows with letters
Note
In some scenarios, we need to use uppercase or lowercase letters as serial numbers. As there are only 26 letters, so numbering with letters is only applicable to sorting a table with no more than 26 rows.
1. Convert numbers into uppercase letters
Change the formula in B3 to CHAR(SEQ()+64).
The CHAR function converts numbers into characters by reference to the ASCII Table. Uppercase letters A-Z correspond to ASCII codes 65-90 respectively.
2. Convert numbers into lowercase letters
Change the formula in C3 to CHAR(SEQ(D3)+96).
The CHAR function converts numbers into characters by reference to the ASCII Table. Lowercase letters a-z correspond to ASCII codes 97-122 respectively.
3. Preview
Download the template
IV. Number rows hierarchically
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL function:
SELECT Region, Salesperson, SUM(Sales_Volume) AS SalesFROM Sales_VolumeGROUP BY Region, Salesperson
2. Design the report body
1) Type headers.
2) Drag data columns in [ds1] into the cell range C3:E3.
3) Preview.
3. Calculate a serial number
1) Input the following formula in B3: INARRAY(C3,UNIQUEARRAY(C3[!0])) + "." + INARRAY(D3,UNIQUEARRAY(D3[!0]{C3=$C3})).
Formula segment | Meaning |
UNIQUEARRAY(C3[!0]) | Fetch all region names, remove duplicate values and return an array. |
INARRAY(C3,UNIQUEARRAY(C3[!0])) | Calculate which value in the array returned in the previous step is the same with the current region name, i.e., the number of the current region. |
UNIQUEARRAY(D3[!0]{C3=$C3}) | Fetch all salesperson names in a certain region, remove duplicate values and return an array. The region is the one where the current salesperson is located. |
INARRAY(D3,UNIQUEARRAY(D3[!0]{C3=$C3})) | Calculate which value in the array returned in the previous step is the same with the current salesperson name, i.e., the number of the region where the current salesperson is located. |
INARRAY(C3,UNIQUEARRAY(C3[!0])) + "." + INARRAY(D3,UNIQUEARRAY(D3[!0]{C3=$C3})) | Connect the number of the region and the number of the current salesperson in the region with ".". |
2) Set the left parent cell of B3 to D3.
4. Preview
Download the template
V. Number rows on each page separately
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Drag the table [Product] into the DB Query.
2. Design the report body
1) Type headers, with headers of Columns B and C both being Number.
2) Drag data columns [ProductID], [Product_name] and [Unit_price] in [ds1] into the cell range D3:F3.
3) Select Rows 1 and 2, right-click them and choose [Set Repeat Title Rows].
3. Calculate a serial number
1) Input the following formula in B3: SEQ().
2) Set the left parent cell of B3 to D3.
3) Input the following formula in C3: IF(COUNT({B3}=1),1,INARRAY(B3,{B3})).
4) Set the left parent cell of C3 to D3.
Formula segment | Meaning |
COUNT({B3}=1) | {B3} returns an array consisting of all serial numbers. |
1 | If the length of the array is 1, which represents a page only contains a piece of data, then the new serial number is also 1. |
INARRAY(B3,{B3}) | If the length of the array is not 1, then calculate which value in the array equals the current serial number and regard the serial number of the value as the new serial number. |
4. Compare the serial numbers in two columns
1) The last piece of data on Page 1 is numbered 53.
2) On Page 2, serial numbers in Column 1 follow the previous page and start from 54 while those in Column 2 start from 1.
5. Hide a column of serial numbers
Select Column B, right-click it and choose [Hide].
6. Preview
Serial numbers on each page start from 1.
Download the template:
Number Rows in Each Page Individually.cpt