Function Reference
6 minutes to readThe following functions, expressions and operators can be used as indicated in:
- Calculated Fields available in report DataPages.
- Aggregations available in report DataPages.
- Formula field available in table design.
- Calculated Value form elements in submission forms.
Math Functions
| Function | Description | Example | Return |
| Abs(number) | Absolute value of a number. | Abs([@field:Number]) | Number |
| Acos(number) | Arccosine of a number in radians. | Acos([@field:Number]) | Number |
| Asin(number) | Arcsine of a number in radians. | Asin([@field:Number]) | Number |
| Atan(number) | Arctangent of a number in radians. | Atan([@field:Number]) | Number |
| Atn2(x-number, y-number) | Arctangent of the specified x-coordinate and y-coordinate. | Atn2([@field:X],[@field:Y]) | Number |
| Ceiling(number) | A number rounded up to the nearest integer. | Ceiling([@field:Amount]) | Number |
| Cos(number) | Cosine of an angle. | Cos([@field: Number]) | Number |
| Cot(number) | Cotangent of an angle. | Cot([@field: Number]) | Number |
| Degrees(number) | Radians converted to degrees. | Degrees([@field:Incline]) | Number |
| Exp(number) | Constant “e” raised to the power of the given number. | Exp([@field:Number]) | Number |
| Floor(number) | Number rounded down to the nearest integer. | Floor([@field:Amount]) | Number |
| Log(number) | Natural logarithm of a number. | Log([@field:Number]) | Number |
| Log10(number) | Base -10 logarithm of a number. | Log10([@field:Number]) | Number |
| Pi() | Value of Pi. | Pi() | Number |
| Power(number, power) | Number raised to a power. | Power([@field:Number], 2) | Number |
| Radians(number) | Degrees converted to radians. | Radians([@field:Number]) | Number |
Rand() Rand(number) | Returns a random number between 0 and 1 using an optional number as a seed. When used in Formula Fields a seed is required. | Rand() Rand([@field:Seed]) | Number |
| Round(number, precision) | Number rounded to the specified length of precision. A positive precision indicates the number of digits after the decimal point. A negative precision indicates digits before the decimal point. | Round([@field:Margin], 1) | Number |
| Sign(number) | 1, 0 or -1 if the number is positive, zero or negative, respectively. | Sign([field:Number]) | Number |
| Sin(number) | Sine of an angle. | Sin([@field:Number]) | Number |
| Sqrt(number) | Square root of a number | Sqrt([@field:Number]) | Number |
| Square(number) | Square of a number. | Square([@field:Number]) | Number |
| Tan(number) | Tangent of an angle. | Tan([@field:Number]) | Number |
| IsNull(value, replace_value) | Replaces NULL with the specified replacement value, otherwise returns the result of expression. | IsNull([@field:Number], -0) | Number |
| IsNumeric(expression) | 1 if expression is a valid number and 0 otherwise. | IsNumeric([@field:Status]) | Integer |
Text Functions
| Function | Description | Example | Return |
| Charindex(string1, string2, start_location) | Searches string2 for string1 and returns its starting position if found. The search starts at start_location. | Charindex(‘John’, [@field:Full_Name], 0) | Integer |
| Left(string, number) | Left part of a character string with the specified number of characters. | Left([@field:Subject], 2) | Text |
| Len(string) | Number of characters in the string, without trailing space. The function returns 2 for some Unicode characters. | Len([@field:Comment]) | Integer |
| Lower(string) | String converted to lowercase. | Lower([@field:Name]) | Text |
| Ltrim(string) | String without leading spaces. | Ltrim([@field:Comment)) | Text |
| Patindex(‘pattern’, string) | Starting position of the first occurrence of a pattern in a specified string, or zeros if the pattern is not found. | Patindex(‘10’, [@field:Student_ID]) | Integer |
| Replace(string1, string2, string3) | Copy of string1, where all occurrences of string2 are replaced by string3. | Replace([@field:Description], ‘United States’, ‘USA’) | Text |
| Reverse(string) | Reverse of the string. | Reverse([@field:String]) | Text |
| Space(number) | String of repeated spaces. | Space(12) | Text |
| Str(number, length , precision) | Returns the number as a string, with a given length(default 10). Optional precision defines the number of digits after the decimal point, up to 16. | Str([@field:Amount],10,16) | Text |
| Stuff(string1, start, length, string2) | Inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. | Stuff([@field:Comment_1], 2, 3, [@field:Comment_2]) | Text |
| Substring(string, start, length) | Returns a substring starting at the starting position defined by start, with total length defined by length. | Substring([@field:Comment], 2, 2) | Text |
| Upper(string) | String converted to uppercase. | Upper([@field:Flight_ID]) | Text |
| NullIf(expression1, expression2) | First expression if the two expressions are not equal, otherwise a null value. | NullIf([@field:Assigned_To],[@field:Executed_By]) | Text |
| N | Casts a string to nvarchar SQL data type that supports Unicode data. | N’Ausländer’ | Text |
Date and Time Functions
| Function | Description | Example | Return | ||||||||||||||||||||||||||||||
| DateAdd(datepart, number, date) | New date which results from addition of specified number to datepart. | DateAdd(day, 14, [@field:Signed_up_date]) | Date | ||||||||||||||||||||||||||||||
| DateDiff(datepart, startdate, enddate) | Difference between the specified dateparts of two dates. | DateDiff(year, [@field:Hired_Date], [@field:Resigned_Date]) | Integer | ||||||||||||||||||||||||||||||
| DatePart(datepart, date) | Specified part of the date. The following are acceptable values for datepart and sample return value:
| DatePart(year,[@field:Birthday]) | Integer | ||||||||||||||||||||||||||||||
| SysDateTime() | Current system timestamp
| SysDateTime() | Date | ||||||||||||||||||||||||||||||
| SysUTCDateTime() | Current system timestamp in UTC time.
| SysUTCDateTime() | Date | ||||||||||||||||||||||||||||||
| Day(date) | Day of specified date. | Day([@field:Birthday]) | Integer | ||||||||||||||||||||||||||||||
| Month(date) | Month of specified date. | Month([@field:Birthday]) | Integer | ||||||||||||||||||||||||||||||
| Year(date) | Year of specified date. | Year([@field:Birthday]) | Integer |
Miscellaneous Functions and Expressions
| Function | Description | Example | Return |
| Cast(expression/value AS new data type) | Expression converted to desired data type. | Cast([@field:Sale]) AS int) | Depends on data type specified |
| Convert(new data type, expression/value) | Expression converted to desired data type. See SQL documentation. | Convert(int, [@field:Sale]) | Depends on data type specified |
| Try_Convert(new data type, expression/value) | Returns converted value of the type specified if the expression can be converted into it, and Null otherwise. | Try_Convert(datetime, [@field:dob]) | Depends on data type specified |
| Function | Description | Example | Return |
| Case | Evaluates a list of conditions and returns one of multiple possible result expressions. | CASE WHEN [@field:Age] >= 65 THEN ‘Senior’ WHEN [@field:Age] >= 18 AND [@field:Age] < 65 THEN ‘Adult’ ELSE ‘Minor’ END | Vary |
| Select | Returns a results set of records from one or more tables.
| SELECT Count(Country) FROM Customers WHERE Country=’Canada’ | Vary |
Logical Operators such as ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR and SOME are supported in CASE and SELECT statements.
Aggregation Functions
| Function | Description |
| Avg | Average of all records values. |
| Count | Adds 1 to the total for each record. |
| Count Non Blank | Adds one to the total for each record that contains data. |
| Min | Smallest value in specified field of a record set. |
| Max | Largest value in specified field of a record set. |
| Sum | Sum total of all record values. |
Aggregate functions are only available in Aggregation Fields in report DataPages. They do not work in Calculated fields or Formula fields.
Scalar Functions (mathematical operations)
| Operator | Description |
| + | Add |
| – | Subtract |
| * | Multiply |
| / | Divide |
| % | Modulo |
| & | Bitwise AND |
| | | Bitwise OR |
| ^ | Bitwise XOR |
| ~ | Bitwise NOT |
Comparison Operators
| Comparison Operator | Description |
| = | Equals |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
| != | Not equal to |
| !< | Not less than |
| !> | Not greater than |
