Conditional Filling by Blank Rows

Problem

The existing filling-by-blank-rows function can only allow you to fill a page by blank rows until the row quantity on the page reaches the minimum multiple of the specified row quantity. How can you fill the page in certain conditions?

For example, fill the page until the row quantity is seven when data on the page is not sufficient to occupy seven rows, and display the expanded rows when data on the page occupies at least seven rows.

Solution

Method 1: Use the Complement function and set a condition attribute to hide the row height when data occupies more than seven rows.

Method 2: When data occupies less than seven rows, fill the page until the row quantity is seven using a formula. When data occupies more than seven rows, no expansion is performed.

Data Preparation

Create a general report and create a data query named ds1. The SQL statement is as follows:

SELECT * FROM SALES_BASIC$ {if(len(Quantity)=0,"","limit "+Quantity)}

Note:

The parameter here is Quantity, which is used with limit to limit the row quantity during data query.

Table Design

Design the table, drag required fields into cells, and set Data Setting to List for cell A2 where Region is located, as shown in the following figure.

Widget Adding

Add the Quantity parameter to the parameter panel, selet Number Widget as the widget type of Quantity, and set the widget value to 3, as shown in the following figure.

Method 1: Filling by Blank Rows

1. Select cell A2, choose Cell Element > Advanced on the right attribute panel, tick Complement, and set Row Quantity to 7, as shown in the following figure.

2. Select cell A2 and click Condition Attribute on the right attribute panel. To add a condition attribute about the row height, set Formula to &A2>7&&LEN(A2)=0 and Row Height to 0, as shown in the following figure. Through this formula, the row height will be 0 when the condition is met.

Note:

The formula is used to hide all blank data added in rows 8 and above.

Method 2: Filling Using a Formula

1. Insert a blank column in the front of the table, set Expansion Direction to Vertical for cell A3, and insert the formula if(count(B2) < 7, RANGE(1, 7 - COUNT(B2)), 0).

2. Select cell A3 and add a condition attribute about the row height in Common Condition. That is to say, when the current value is equal to 0, the row height is 0 (so that the row is hidden), as shown in the following figure.

3. Hide column A.

Effect Display

PC

Save the template and click Pagination Preview. The effect is the same as that shown in section "Problem."

Mobile Terminal

The template can be previewed on both the DataAnalyst and HTML5 apps. The following figure shows the display effect.Condtional Filling by Blank Rows-Method 1.cpt

You can download the example template.

Condtional Filling by Blank Rows-Method 1.cpt

Condtional Filling by Blank Rows-Method 2.cpt