Column Split based on Result Set Filtering

Application Scenario

Setting row freeze in a template usually causes abnormalities in the column split effect. Is there any good way to ensure the column split works normally when setting row freeze?

Implementation Method

The dataset filtering function can be used to implement the column split. However, this is not the real column split. In fact, data is displayed in the specified position to implement the column split effect in the frontend.

The advantage of this method is that the column split will not conflict with row freeze.

Data Preparation

Create a new built-in dataset ds1, which has two fields, as shown in the following figure.

Designing a Template

(1) Design the table as shown in the following figure. Drag the fields from the dataset to the corresponding cells, set the left parent cell of cells A3 to E3 to cell A2, and set Expansion Direction to Vertical for these cells.

Note:

The Product field is dragged into 5 columns repeatedly because we want to split the data into 5 columns. Set as many columns as you want to split.

(2) Choose Template > Repeat/Freeze from the menu bar, set the repeat title rows to No.1 row and No.2 row, and set the freeze rows to No.1 row and No.2 row, as shown in the following figure.

Configuring Result Set Filtering

(1) Double-click cell A3, and the data column dialog box will pop up. Click Advanced, choose Specify from the Result Set Filter drop-down list, and enter the =if($__count__<5,1,JOINARRAY(GREPARRAY(range($__count__),item % 5 == 1),",")) formula.

Formula explanation: If the total number of current data is less than the number of columns needed for split, the A3 cell will return 1 (to avoid the situation where the total number of current data is less than the number of columns for split, which causes the A3 cell to return blank and thus leads the system to display all data in the A3 cell). Otherwise, an array is returned, which is composed of numbers where the total count leaves a remainder of 1 when divided by 5, for example, 1, 6, 11, 16, 21... Moreover, due to the specified dataset filtering function, only the data with sequence numbers 1, 6, 11, 16, 21... (the numbers in the array returned by the formula) will be displayed in the current cell.

Note:

Do not forget the equal sign at the beginning of the formula.

(2) The specified result set filtering rule also needs to be set for cells B3 to E3. The formulas are slightly different from that of A3. The following table describes the formulas:

Cell B3: =if($__count__<5,2,JOINARRAY(GREPARRAY(range($__count__),item % 5 == 2),","))

Cell C3: =if($__count__<5,3,JOINARRAY(GREPARRAY(range($__count__),item % 5 == 3),","))

Cell D3: =if($__count__<5,4,JOINARRAY(GREPARRAY(range($__count__),item % 5 == 4),","))

Cell E3: =if($__count__<5,5,JOINARRAY(GREPARRAY(range($__count__),item % 5 == 0),","))

Effect Display

Save the report and click Pagination Preview. The effect is shown in the following figure.

The report can be previewed on App and HTML5.