Example of use of the crystal report formula field and syntax of the formula field [Deep Sea Original]

I. Example of using a formula field:

A. Conversion of measurement units
This is a very simple example to demonstrate how to convert and calculate different variable types in formulas.
Sometimes the unit of the database field is different from the unit displayed in the report. We need to convert the units of measurement. In this case, the use of formulas is inseparable. For example, our database has a length field, which records the length in meters. When printing, the length must be in centimeters. Then we can use the following function.
Totext ({order details. Length} * 100) + "Centimeter"
Here, the totext function converts the numeric type to the numeric type. Commonly used conversion functions include tonumber.

B. Chinese week
In this example, we will demonstrate how to perform string operations.
We often encounter the problem of displaying Chinese week when printing reports. Crystal Reports has the dateofweek function to determine the day of a week. For example, if dateofweek returns 4, it indicates Wednesday. We can use the formula below to display the Chinese Week. Here, the today function is just an example. You can modify it as the date function or field you need.
Stringvar X;
Numbervar N;
X: = "May 15, 1234 ";
N: = dayofweek (today );
X: = "Week" + X [2 * n-1 to 2 * n]
In the first two rows, we define the string variable X and the numeric Variable N. A formula can have many rows, but only the calculation result of the last row is printed in the report as the result of the entire formula.

C. Conditional Calculation
In this example, we will focus on how to use the if-then-else operator.
Conditional computing is widely used in reports. For example, we need to determine the discount based on the total price purchased by the user. We assume that the user will not be at a discount when purchasing products of less than 200 yuan; 200 yuan to 1000 yuan will provide a 5% discount; 1000 discount will be provided for products of more than 7% yuan. We can use the formula below to calculate how much the end user will pay.
Currencyvar cvalue;
Cvalue: = {order details. unitprice} * {order details. Quantity}; // calculate the total price
If cvalue <200 then
Cvalue
Else if cvalue <1000 then
Cvalue * 0.95
Else
Cvalue * 0.93

D. Condition statistics
Crystal Reports provides some basic statistical functions that can be used by group for statistics. However, statistics in practical applications are often more complex. For example, we need to make statistics based on certain conditions. Suppose we want to count the sales volume of goods with a unit price of more than 10 yuan in each group. We can insert a formula in the detail section of the report. Assume that the formula is named conditionsum, and enter the following content in the formula:
If {order details. unitprice} <10 then
0
Else
{Order details. Quantity}

Ii. syntax of the formula field:

Syntax of formula fields is relatively simple, but many operators and functions are supported. Variables and constants can be used in formulas. You can also reference database fields in the format of {database name. Field name}
A. Operator
Crystal Reports support a large number of operators, some of which are common and some are unique to Crystal Reports.

B. Arithmetic Operators
Crystal Reports Support the +,-, *,/, and % (percentage) operators, such as 100% 500 = 20.

C. Conversion Operators
The conversion operator is $, which converts a number to a currency type, so that the crystal report prints data according to the currency format in the report.

D. Comparison Operators
The comparison operators supported by Crystal Reports are the same as those supported by Visual Basic: =, <>, <,>, >=, and <=.

E. String Operators
The operator for string connection is "+ ".
Use the [] operator to take the specified character from the string, which is equivalent to the mid function of Visual Basic. "[]" Is the character position, and the first character is numbered 1. For example: {file. itemnumber} [4 to 5]
Determines whether a string is included in another string using the in operator. For example, "C" in "PCC" returns true.

F. Range Operators
The crystal report allows you to create a range and determine whether the data is included in the specified range. For example, "100.00 to 250.00" contains data between 100 and 250, and "50 in (100 to 250)" can be used to determine whether 50 is within the specified range.

G. boolean operator
Boolean operators include and, not, and or.

H. array Operator
Crystal Reports Support arrays. For example, [100,200,300,400] is an array composed of four elements. You can use the in operator to determine whether the data is an array element, for example, {file. State} in ["ca", "Hi", "AK"].

I. Matching Operators
The matching operators include startswith and like. Startswith checks whether a string starts with a specified string. For example, "PCC" startwith "p" returns true. Similar to Visual Basic, the like operator compares a string expression with a style in a wildcard expression.

J. Other operators
Another important operator is if... Then... Else ..., We are familiar with condition judgment. We can see the power of this operator in the examples below.
To assign a value to a variable, use the ": =" symbol, which is different from Visual Basic.
You can also add comments to the formula to facilitate reading. The comment symbol is "//", and the content after the symbol "//" will be ignored.
The formula can also contain multiple statement rows, which are separated by ";" at the end of the row.

K. Function
The crystal report supports a large number of functions, including mathematical functions, statistical functions, date functions, string functions, and array functions. In the Help file of the crystal report, each function is described in detail.

Note: For some references, see blue deep sea summary and modification! Some are the practical experience of the project!