Overview
Problem
If you have configured Background Color Setting Every Other Row/Column and enabled Report Split in a report, you may encounter the dislocation of interlaced colors. You may want a solution to solve the issue and keep a consistent color in the same row, as shown in the following figure.
Implementation Method
You can add a formula in Condition Attribute to achieve the effect.
First, you should determine the columns requiring special processing based on the number of columns and total data. Then, you can adjust odd-numbered rows in these identified columns and set alternating row colors for all qualified rows.
Example
Data Preparation
Create a general report, and create a database query ds1 with the SQL statement SELECT * FROM Sales_Volume.
Report Design
1. Drag Salesperson, Product, and Sales_Volume data columns into cells A2 to C2. Design the table as shown in the following figure.
2. Select cell A2, and set Data Setting to List, as shown in the following figure.
Splitting Setting
Choose Template > Report Split by Column/Row from the menu bar, and follow the steps on the opened report split page, as shown in the figure.
Condition Attribute Setting
Select cell A2, add a condition attribute on the right attribute panel, set Attribute to Background, select blue as a color, and select Current Row from the drop-down list. Set Type to Formula, and enter the following formula:
Note:ROUNDUP(COUNT(A2[!0])/2,0)%2<>0&&(&A2+(1-ROUNDUP(&A2/ROUNDUP(COUNT(A2[!0])/2,0),0)%2))% 2=0||ROUNDUP(COUNT(A2[!0])/2,0)%2=0&&&A2%2=0
The following table describes the formula.
| Category | Formula | Description |
|---|---|---|
Formula One | A2[!0] | All cells expanded from cell A2. |
COUNT(A2[!0]) | To count total rows in the current data. | |
COUNT(A2[!0])/2 | To divide the total rows by the number of columns (2) to determine the number of rows per column. | |
ROUNDUP(COUNT(A2[!0])/2,0) | To apply the ceiling function to ensure an integer number of rows per column. | |
ROUNDUP(COUNT(A2[!0])/2,0)%2<>0 | If the remainder of dividing the calculation result by 2 is not equal to 0, the number of the column is odd. | |
Formula Two | ROUNDUP(&A2 / ROUNDUP(COUNT(A2[!0])/2,0),0) | To calculate the number of columns crossed by the current row. |
1-ROUNDUP(&A2/ROUNDUP(COUNT(A2[!0])/2,0),0)%2 | To adjust the offset based on whether the number of columns is even or odd. | |
&A2+(1-ROUNDUP(&A2/ROUNDUP(COUNT(A2[!0])/2,0),0)%2) | To add the current row number and the offset. | |
(&A2+(1-ROUNDUP(&A2/ROUNDUP(COUNT(A2[!0])/2,0),0)%2))% 2=0 | To judge whether the number of rows is even after adjustment. | |
Formula Three | ROUNDUP(COUNT(A2[!0])/2,0)%2=0 | If the remainder of dividing the calculation result by 2 is equal to 0, the number of the column row is even. |
Formula Four | &A2%2=0 | To judge if the number of the current row is even. |
The following figure shows the steps.
Effect Display
PC
Save the report and click Pagination Preview. The following figure shows the preview effect.
Mobile Terminal
The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.
Template Download
For details, you can download the template Solution to Dislocation of Interlaced Colors During Report Splitting by Row.cpt
