I. Overview
1. Problem description
When making a report, it is often encountered that "cells/rows/columns" need to be displayed in different background colors under certain conditions. How to implement this?
2. Solution
This can be achieved by adding conditional formatting > background.
The principle of setting "current cell/current row/current column" is the same. This article takes "current cell" and "current row" as examples to explain briefly.
II. Example1: current cell
1. Data preparation
Create a new normal report and a new built-in dataset "Proportion of e-commerce sales " to record the sales proportion of different products of each e-commerce
2. Table design
Right click cell A1, select cell element > insert slash, enter e-commerce and products, and select top left to bottom right. B1 ~ M1 input the name of e-commerce, drag the data column into the corresponding cells A2 ~ M2, and the table style is shown in the following figure:
Note: the width of the above table has exceeded the default paper size. You need to modify the paper size in template > page setup to make it appear on the same page when previewing.
3. Add conditional formatting
Select cells B2 ~ M2, click conditional formattingon the right, add condition, select background from the attributes, and click Edit to set current cell.
The condition is a value area segment, the condition type is common, the value type is double precision, when there are two conditions, use "and" to connect. The following are greater than or equal to 0.5 and less than 0.7.
Add 6 conditional formatting in turn, as shown in the following table:
| Conditioanl formatting | condition |
|---|---|
| Conditional formatting1 | greater than or equal to 0.5 and less than 0.7 |
| Conditional formatting2 | greater than or equal to 0.3 and less than 0.5 |
| Conditional formatting3 | greater than or equal to 0.1 and less than 0.3 |
| Conditional formatting4 | greater than or equal to 0.05 and less than 0.1 |
| Conditional formatting5 | greater than 0 and less than 0.05 |
| Conditional formatting6 | equal to 0 |
Note: the execution order of conditional formatting is from top to bottom.
4. Effect preview
1) PC terminal
Save the report and click "pagination preview". Different colors will be displayed in different areas to achieve the same effect as the thermal chart. As shown in the figure below:
2) Mobile terminal
Both app and H5 support it, and the effect is shown in the following figure:
III. Example2: current row
1. Data preparation
Create a new normal report, create a new database query DS1, and the SQL query statement is: select * from sales_volume.
2. Table design
Drag all the fields into the cell, add the corresponding title, and set the data in cell A2 to "list", as shown in the following figure:
3. Add conditional formatting
Select cell A2, click conditional formatting on the right, add condition, select background from attributes, click Edit, customize color and set current row.
Select formula for condition type, and click definition to enter formula: E2 > = 500, that is, when the sales volume is greater than 500, the current line will be displayed in green.The steps are as follows:
4. Effect preview
1) PC terminal
Save the report and click pagination preview. The effect is as follows:
2) Mobile terminal
Both app and H5 support it, and the effect is shown in the following figure:
IV. Template download
For completed templates, please refer to:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\diff_Bg_by_row.cpt
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\diff_Bg_by_cell.cpt
Click to download the template: