Handling Zero Divisors and Zero Dividends

Problem

When designing reports that involve division operations, you may encounter scenarios where the divisor or dividend is zero. The following table shows the default calculation rules of FineReport in such scenarios.

These results are technically correct. However, in practical applications, you often need to handle cases where the divisor is zero or both the divisor and dividend are zero by displaying the quotients as can't calculate or avoiding quotients that are ∞ or -∞, as shown in the following figure.

Solution

• Use the IF formula to check whether both the divisor and dividend are zero. If so, display the quotient as can't calculate.

• Change the quotients that are ∞ or -∞ under Condition Attribute > New Value.

Data Preparation

Create a general report and create a built-in dataset Embedded1. Add two integer-type fields Dividend and Divisor, and fill in some integer values, as shown in the following figure.

Report Design

Drag Dividend to cell A2 and Divisor to cell B2. Click Cell Element, and set Data Setting to List for cell A2. Insert the formula A2/B2 for Quotient in cell C2. The following figure shows the designed table.

Preview the template. The quotients are calculated based on the default calculation rules of FineReport, as shown in the following figure.

Formula Setting

Scenario Where Both the Dividend and Divisor Are Zero

If both the dividend and divisor are zero, the quotient needs to be displayed as can't calculate. Otherwise, the division A2/B2 needs to be performed. In this case, you can modify the formula in cell C2 to IF(A2=0&&B2=0,"can't calculate",A2/B2). The following figure shows the results.

Scenario Where the Divisor Is Zero

If the divisor is zero, the quotient will be ∞ or -∞ normally. To avoid this, you can use the formula IF(B2=0,"0",A2/B2)to display the quotient as 0 when the divisor is zero and perform the division A2/B2 when the divisor is not zero. In this example, you can modify the formula in cell C2 to IF(A2=0&&B2=0,"can't calculate",IF(B2=0,"0",A2/B2)). The following figure shows the results.

Setting New Value in Condition Attribute

If the quotient is ∞ or -∞, the corresponding actual value is Infinity or -Infinity. You can change the cell value under Condition Attribute > New Value.

Note:

The word Infinity must start with an uppercase I.

Select cell C2 and click Condition Attribute on the right attribute panel. To add a condition attribute, select New Value from the drop-down box of Attribute, and set New Value to string 0 (or values of other types as needed). Add two common conditions connected by OR, as shown in the following figure. When the cell value is Infinity (∞) or -Infinity (-∞), the value will be changed to 0.

You can set the cell values in batches using Condition Attribute. If the values of multiple cells in the template are calculated as infinity/-infinity, you can select all these cells, right-click the mouse, select Condition Attribute from the drop-down box, and set the cell values in one step. This eliminates the need for repetitive IF formulas and significantly improves development efficiency.

Effect Display

PC

Save the template. You can view the preview effect in the section "Problem."

Mobile Terminal

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

You can download the template: Handling Zero Divisors and Zero Dividends.cpt.