Sort After Expansion Renumbering

Expected Effect

After the sort after expansion is performed (by Milk sales), if you directly insert formulas seq() or &cell in a cell to obtain the sequence number, the order of the sequence will be disrupted. In this case, you may wonder how to make it normal, as shown in the following figure. 

Solution

Method one: Set the new value of the condition attribute.

Method two: Set the formula form.

Method three: Add a load end event and solve it with JavaScript code.

Data Preparation

1. Create a general report, create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume.

Report Design

1. Design the table as shown in the following figure, set the left parent cell of cell A2 to B2, and insert the formula seq() in cell A2, to generate a sequence number.

Set the expansion direction of cell C1 to Horizontal, and cells B2 and C2 to Vertical.

2. If sort after expansion is not set, you may see that the sequence number is in normal order when you save the template and click Pagination Preview, as shown in the following figure.

Sort After Expansion Setting

1. Select cell B2, add a sort after expansion, set Sort Basis to Formula, and enter C2{C1 = "Milk"}, which means sort in ascending order based on the sales values in the Milk column. The steps are shown in the following figure.

2. Save the template and click Pagination Preview. You can see that the table has been sorted in ascending order by the Milk column, but the Serial Number column is disrupted, as shown in the following figure.

Method One (Condition Attribute)

Select cell A2 where the serial number is located, delete the original formula, click Add Condition, click the + icon on the pop-up page, set New Value to the formula type, and enter seq(), row()-1, or &cell.

row()-1: row() returns the current row position and -1 refers to the current row position minus one. The value depends on the specific situation.

&cell: cell refers to the cell that has been set sort after expansion. In this example, it is cell B2.

As it needs to take effect in all situations, there is no need to add any condition.

Method Two (Formula Form)

Select cell A2 where the serial number is located, click the Cell Attribute icon, click the Display tab, select Formula Form from the drop-down list, and enter seq(), row()-1, or &cell.

Note:

1. The formula form converts the actual value to the displayed value, so the cell where the serial number is located must have a value.

2. When the original value of the cell where the serial number is located is seq(), the value of the formula form needs to be set to seq(1), otherwise, the serial number will be accumulated.

Method Three (Load End Event)

Choose Template > Web Attribute, click the Pagination Preview tab, select Set for This Template Separately from the drop-down list of Following Settings, add a Load End event in Event Setting, and enter JavaScript code, as shown in the following figure.

JavaScript codes are as follows:

$(function(){
     var len=$('.x-table tr').length;
     for(var i=1;i<len;i++){
//The number of rows in the table is counted from 0 and the following code means that the number of the first cell in the second row is 1.
     $('.x-table tr:eq('+i+') td:first').text(i);
     }
})

Effect Display

On PC

The effects of all three methods are consistent with the normal sort effect in section "Report Design."

On Mobile Terminals

All methods are available on both DataAnalyst and HTML5 apps except the third one, as shown in the following figure.

You can download the example template.

 Sort After Expansion Renumbering1.cpt

Sort After Expansion Renumbering2.cpt

Sort After Expansion Renumbering3.cpt