Computing values from multiple numeric fields
If a report primarily displays values from numeric fields, it most likely contains computed values as well. An invoice, for example, typically shows the following computed values:
Extended prices that display the product of unit price
∗ quantity for each line item
Sales tax total that displays the product of extended prices
∗ tax rate
Invoice total that displays the sum of extended prices + shipping + sales tax
Order of operations
When a calculation involves more than two numbers and different operators, remember the order of operations, which is the order in which operators are evaluated. Consider the following math expression:
55 + 50 + 45 ∗ 2
If you performed each operation from left to right in the following steps:
55 + 50 = 105
105 + 45 = 150
150 ∗ 2 = 300
the answer would be 300.
If you specify the math expression in a data element, BIRT Report Designer returns 195, which is the correct answer. The difference in answers lies in the order of operations. This concept is one that you might remember from math class. Multiplication and division are evaluated first from left to right across the expression. Then, addition and subtraction are evaluated from left to right across the expression. Using the previous example, the expression is evaluated as follows:
45 ∗ 2 = 90
55 + 50 = 105
105 + 90 = 195
To perform the addition before the multiplication, enclose the addition part within parentheses, as follows:
(55 + 50 + 45) ∗ 2
The following list describes examples of expressions that compute values from multiple numeric fields:
The following expression calculates a total price after deducting a discount and adding an 8% tax that applies to the discounted price:
(row["extendedPrice"] - row["discount"]) + (row["extendedPrice"] - row["discount"]) ∗ 0.08
The following expression calculates an invoice total, which includes the total of all extended prices, an 8% sales tax, and a 10% shipping and handling charge:
row["salesTotal"] + (row["salesTotal"] ∗ 0.08) + (row["salesTotal"] ∗ 0.10)
The following expression calculates a gain or loss in percent:
(row["salePrice"] - row["unitPrice"])/row["unitPrice"] ∗ 100
Division by zero
If you divide the value of one numeric field by another and the denominator value is 0, the result is infinity (
).
For example, if the following expression:
row["total"]/row["quantity"]
evaluates to:
150/0
the data element that contains the expression displays
.
The return value is infinity because dividing a number by zero is an operation that has no answer. Mathematicians consider this operation undefined, illegal, or indeterminate.
If you do not want the infinity symbol to appear in the report, you can replace it with a string value, such as Undefined, or replace it with an empty string ("") to display nothing. The infinity symbol is a numeric value; therefore, you must convert it to a string before replacing it with a different string.
The following expression replaces
with Undefined:
// Convert number to a string
x = row["total"]/row["quantity"] + ""
// Replace
with the word Undefined
x.replace("Infinity", "Undefined")
Alternatively, use the BirtMath function, safeDivide( ). The safeDivide( ) function takes three arguments: the number to divide, the divisor, and the value to return if the result of the division is infinity. The following expression uses safeDivide( ) to divide row["total"] by row["quantity"] and to return Undefined for an infinity value:
BirtMath.safeDivide(row["total"], row["quantity"], "Undefined")