I. Overview
1. Problem description
If you set Different Row Background Color for the report, and set the Column and Row Split, there may be misalignment.
I hope that there is a solution to solve the misalignment problem and realize the color alignment of the peers, as shown in the following figure:
2. Implementation ideas
Use the Formula in the Conditional Formatting to achieve.
First, determine whether the even-numbered column requires special processing based on the number of columns in the column and the total number of data, and then calculate the number of rows that require special processing to set the column.
II. Example
1. Data preparation
Create a new ordinary report, create a new data set ds1, the SQL query statement is: SELECT * FROM SALES_VOLUME.
2. Design report
1) Drag the Salesperson, Product, Sales_Volume data column into cells A2~C2, and design the table style as shown in the figure below:
2) Modify the Data Setting of cell A2 to List, as shown in the figure below:
3. Set column
Click the menu bar Template>Multi-Columns/Rows, and set according to the steps shown in the following figure on the opened report column page:
4. Setting condition formatting
Select cell A2 and click on the property panel on the right to add a Conditional Formatting. Select the attribute as Background and the color as blue to act on the Current Row. Set the condition type toFormula, and enter the formula as:
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 formula description is as follows:
| Formula | Description |
|---|---|
| ROUNDUP(COUNT(A2[!0])/2,0)%2<>0 | A2[!0] refers to all cells expanded by A2, after using COUNT to count, divide by the number of columns in the column , The number of columns in this example is 2. Then use ROUNDUP to round up the calculated number, and the remainder after being divisible by 2 is not equal to 0, indicating that the total number of rows after the report is divided into columns is an odd number. |
| (&A2+(1-ROUNDUP(&A2/ROUNDUP(COUNT(A2[!0])/2,0),0)%2))% 2=0 | &A2 is the serial number to get the extended value of A2, and calculate the result when it equals to 0, the background color of the current line is blue. |
| ROUNDUP(COUNT(A2[!0])/2,0)%2=0 | The calculation result is divisible by 2 and the remainder is equal to 0, indicating that the total number of rows after the report is divided into columns. |
| &A2%2=0 | When the serial number of the expanded value of A2 cell is divided by 2 and the remainder is equal to 0, the background color of the current row is blue. |
Note: The formula consists of 4 conditions. The formulas are connected by && and ||, and the calculation is executed from left to right. When the report satisfies formula 1, the background color will be displayed according to formula 2. When the report satisfies formula 3, the background color will be displayed according to formula 4. The background color is displayed.
The steps are shown in the figure below:
5. Effect preview
1） PC side
Save the report, clickPagination Preview, the interlaced color effect of the row and column is shown in the figure below:
III. Template download
The completed template can be found in:
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Solution to discoloration
and dislocation between rows and columns.cpt
Click to download the template: Solution to discoloration and dislocation between rows and columns.cpt