Multi-Level Sequence Number

Preview Effect

During report design, you may need to generate multi-level sequence numbers based on multiple columns of data. The preview effect is as shown in the following figure.

Implementation Method

First obtain the deduplicated sequence number of each data record in the first column.

Then obtain the sequence number of each data record in the second column after data records whose corresponding value in the first column are the same are deduplicated in the second column.

Concatenate the mapping sequence numbers obtained in the first and second data columns.

Data Preparation

Create a dataset named ds1. The database query statement is SELECT * FROM department.

Report Design

(1) Design the table style as shown in the following figure. Set Data Setting to List for cell B2, and Left Parent Cell to B2 for cell A2.

(2) Enter the formula in cell A2:

INARRAY(B2,UNIQUEARRAY(B2[!0]))+"."+INARRAY(C2,UNIQUEARRAY(C2[!0]{B2=$B2}))

Effect Preview

(1) PC

Save the report and click Pagination Preview. Then the multi-level sequence number effect is shown in the following figure.

(2) Mobile Terminal

For details, you can click to download Multi-Level Sequence Number.cpt.

If sequence numbers need to generated for data in a third column (D2), set Left Parent Cell to C2 for cell A2. The formula is INARRAY(B2,UNIQUEARRAY(B2[!0]))+"."+INARRAY(C2,UNIQUEARRAY(C2 [!0]{B2=$B2}))+"."+INARRAY(D2,UNIQUEARRAY(D2[!0]{B2=$B2&&C2=$C2})).

If sequence numbers need to generated for data in a fourth column (D2), set Left Parent Cell to D2 for cell A2. The formula is INARRAY(B2,UNIQUEARRAY(B2[!0]))+"."+INARRAY(C2,UNIQUEARRAY(C2 [!0]{B2=$B2}))+"."+INARRAY(D2,UNIQUEARRAY(D2[!0]{B2=$B2&&C2=$C2}))+"."+INARRAY(E2,UNIQUEARRAY( E2[!0]{B2=$B2&&C2=$C2&&D2=$D2})).

The rest can be deducted from the above rules.