Modifying Cell Values by Condition

Application Scenario

A cell can display different values according to different conditions, for example:

The display value of Discount is a hyphen (-) if a product is not discounted, and a number followed by a text if a product is discounted. 

Alternatively, you can set cells containing no data to display the value 0, as shown in the following figure.

Implementation Method

You can add a New Value condition attribute to change the display values of cells by condition.

Data Preparation

Create a general report, create a built-in dataset, design the table as shown in the following figure, and rename the dataset ds1.

Table Design

Design a table and drag the fields from the dataset into corresponding cells, as shown in the following figure.

Condition Attribute Setting

1. First, to display the value of Discount as a hype (-) if a product is not discounted, perform the following operations:

Select cell D2, and add a New Value condition attribute with New Value set to String - and the condition set to currentValue Equal to '1', as shown in the following figure.

2. Second, to display the value of Discount as a number followed by a text if a product is discounted, perform the following operations:

Select cell D2, add another New Value condition attribute with New Value set to Formula =D2+" Off" and the condition set to currentValue Not Equal to '1', as shown in the following figure.

Effect Display

PC

Save the report. The pagination preview effect is the same as that shown in section "Application Scenario."

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

Data Preparation

Create a general report, create a built-in dataset, design the table as shown in the following figure, and rename the dataset ds1.

Table Design

Design the table in the same way as that in section "Table Design."

Condition Attribute Setting

Select cell D2, and add a New Value condition attribute with New Value set to String 0 and the condition set to the formula len($$$)=0, as shown in the following figure. In this case, 0 is displayed in the cells containing no data.

Effect Display

PC

Save the report. The pagination preview effect is the same as that shown in section "Application Scenario."

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

1. Example One

For details, you can download the template Modifying Cell Values by Condition.cpt.

2. Example Two

Foe details, you can download the template Modifying Cell Values by Condition - Example Two.cpt.

Obtaining New Values of Other Cells

If you want to obtain the display value of a cell for which a new value is set, you are advised not to directly enter a formula in the other cell. Otherwise, you can only obtain the actual value.

This is because the formula is calculated before template loading, while the new value takes effect after template loading. Therefore, the formula set in the other cell still returns the actual value.

If you want to obtain the display value, you can add a new value condition attribute using the method shown in the following figure.

The following figure shows the final effect.

How New Values Affect Other Condition Attributes

The formula (if any) in a cell is calculated before the condition attribute New Value.

The new value does not affect other condition attributes within the same cell.

The new value from the left cell in the same row is incorporated into the calculation of the condition attribute from the right cell.

The new value from the upper cell in the same column is incorporated into the calculation of the condition attribute from the lower cell.

Note:

You cannot use Data Entry widgets and New Value condition attributes simultaneously in Data Entry reports. Otherwise, the new values will not take effect.