12. Functions
12.2 Language elements
12.2 Language elements
The language elements are divided in the following functional groups:
Expressions
Statements
Constants
12.2.1 Expressions
The following expressions can be used inside functions.
Logical or, operator “or”
The result will be true when one of the two operands is true.
if ({Customer.Credit_Limit} = 0 or {Customer.Purchases} = 0) then return 'credit limit or purchases is zero'
else
return 'neither credit limit or purchases is zero' end
Logical and, operator “and”
The result will be True if both operands are True.
if ({Customer.Credit_Limit} = 0 and {Customer.Purchases} = 0) then return 'credit limit and purchases are zero'
else
return 'credit limit and purchases are NOT zero' end
Logical not, operator “not”
The result of the operation is the reverse of expression value.
if (not ({orderhea.status} = ‘Y’)) then return drGreen
else
return drBlack end
Comparison greater than, operator “>”
With this operator you can compare two operands (numbers or strings) with each other where the result is true when the value of the left operand is greater than the value of the right operand.
if ({Customer.CurrentSpeed} > {Road.MaxSpeed}) then return "You get a ticket"
else
return "Pass"
end
Comparison less than, operator “<”
With this operator you can compare two operands (numbers or strings) with each other where the result is true when the value of the left operand is less than the value of the right operand.
if ({Customer.Credit_Limit} < {Customer.Purchases}) then return drRed
else
return drBlack end
Comparison greater than or equal to, operator “>=”
With this operator you can compare two operands (numbers or strings) with each other where the result is true
if ({Customer.Credit_Limit} >= {Customer.Purchases}) then return drRed
else
return drBlack end
Comparison less than or equal to, operator “<=”
With this operator you can compare two operands (numbers or strings) with each other where the result is true when the value of the left operand is less than or equal to the value of the right operand.
if ({Customer.Credit_Limit} <= {Customer.Purchases}) then return drRed
else
return drBlack end
Comparison equal, operator “=”
With this operator you can compare two operands (numbers or strings) with each other where the result is true when the value of the left operand is equal to the value of the right operand.
if ({Customer.Credit_Limit} = {Customer.Purchases}) then return drRed
else
return drBlack end
Comparison not equal, operator “<>”
With this operator you can compare two operands (numbers or strings) with each other where the result is true when the value of the left operand is not equal to the value of the right operand.
if ({Customer.Credit_Limit} <> {Customer.Purchases}) then return 'not equal'
else
return 'equal' end
Arithmetic add, operator “+”
The operator is used to add two numeric values to each other.
return ({Customer.Credit_Limit} + {Customer.Balance})
Arithmetic subtract, operator “-”
The operator is used to subtract two numeric values from each other.
return ({Customer.Credit_Limit} - {Customer.Purchases})
Arithmetic multiply, operator “*”
The operator is used to multiply two numeric values.
return ({OrderHea.Order_Total} * 2)
Arithmetic divide, operator “/”
The operator is used to divide two numeric values by each other.
return ({OrderHea.Order_Total} / 10.1)
Unary negate, operator “-”
You use this operator to reverse a numeric value (of an expression).
return (-{OrderHea.Order_Total})
When used as:
let iNum = -10 return (-iNum)
The result is positive 10.
Unary plus, operator “+”
The operator does not force a sign conversion by itself; it is more a documentation item. If you find:
Let iNum = +10 is more clear than:
Let iNum = 10
you can use the unary plus operator.
12.2.2 Statements
The function language has the following statements defined.
Let
For assigning a value to a variable, where the value can be a constant, a database field or an expression.
It is not necessary to declare the variable first, the type of variable is automatically determined based on the content. The variable name must start with a letter, followed by a number of alpha-numeric characters (including an underscore ‘_’).
let x = {OrderHea.Order_Total}
let y = 0.75 let z = (y * x)
In the above example, variable ‘x’ gets the value of the order amount (database field). The variable ‘y’ is a constant (value 0.75) and variable ‘z’ is the outcome of the multiplication of 0.75 times the order amount (expression).
If then end
if ({OrderHea.Order_Total} > {Customer.Order_Limit}) then return 300
end
The result in this example is 300 if the Total order amount is higher than the order limit of the customer.
If then else end
For conditionally executing statements.
if ({OrderHea.Order_Total} > {Customer.Order_Limit}) then return "!"
else
return "v"
end
The result in this example is an exclamation point (‘!’) if the Total order amount is higher than the order limit of the customer. Otherwise the result is a check-mark (‘v’).
let iRed = rgb(255,0,0) let iGreen = rgb(0,255,0) let iBlack = rgb(0,0,0)
if ({OrderHea.Order_Total} > 0) then return iGreen
The above example illustrates that it is possible to nest statements. The result is green for an amount higher than 0, red for an amount less than 0 and black for equal to 0.
Note: for rgb color-codes, constants drRed, drGreen and drBlack can be used.
Return
Ends the function and sets the result for the function.
return ({Customer.Purchases} - {Customer.Balance})
In the above example the result is the differential between the total amount of purchases of the customer and the balance.
While do end
This command group can be used to execute a loop.
The example below tests if a variable consists only of numbers. The characters are tested one by one. If a character is found that is not a number, the loop is exited.
Note: The while loop automatically terminates when it iterates for more than 1000 times in order to avoid infinite recursion, which causes the program to become unresponsive.
let testval = "1234X5678"
let length = len(testval) let testchar = ""
let stop = 0 let cnt = 1
// test if the variable ‘testval’ only consists of numbers while ((cnt <= length) and (stop = 0)) do
let testchar = mid(testval,cnt,1)
let stop = (Not(instr("1234567890",testchar))) let cnt = (cnt + 1)
end
if (stop = 0) then
return "variable consists only of numbers"
else
return "variable does not only consist of numbers"
end
Dim
This command declares a variable. The variable only exists inside the function. Declaring a variable is optional.
The ‘Let’ statement automatically declares a variable before assigning a value to it. The variable name must start with a letter, followed by any number of alphanumeric characters, including the underscore (‘_’).
dim nAmt
let nAmt = ({OrderDtl.Extended_Price} * 0.75) return nAmt
The result of this function is 75% of the amount.
Dim as global
This command declares a global variable. Global variables are persistent throughout the report. The name of the variable must start with a letter, followed by any number of alpha-numeric characters, including an underscore.
dim gTotal as global let gTotal = 1
return gTotal //The global variable is also available in other functions.
12.2.3 Constants
This part of the language elements shows which constants are defined and accepted in functions.
Category Values
Boolean True
False
Category Values
Date drWindowsLongDate
drWindowsShortDate
Number drWindowsNumber
drCustomNumber
Currency drWindowsCurrency
drCustomCurrency
Weekdays drWindowsDayOfWeek
drSunday
Miscellaneous drNone
Border styles drBorderNone
drBorderSingle
Text alignments drHorzAlignmentLeft
drHorzAlignmentCentered
drHorzAlignmentRight
Category Values
Gradients drHorizontalGradient
drVerticalGradient
The built-in functions are divided in the following functional groups:
String functions
Conversion functions
Date and Time functions
Arithmetic functions
Miscellaneous functions
Database functions