The 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

Math Functions

FunctionDescriptionExampleReturn
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 numberSqrt([@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

FunctionDescriptionExampleReturn
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.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])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

Date and Time Functions

FunctionDescriptionExampleReturn
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:

 

datepartReturn value
year, yyyy, yy2007
quarter, qq, q4
month, mm, m10
dayofyear, dy, y303
day, dd, d30
week, wk, ww45
weekday, dw1
hour, hh12
minute, n15
second, ss, s32
millisecond, ms123
microsecond, mcs123456
nanosecond, ns123456700
TZoffset, tz

(requires ISO 8601 format)

310
DatePart(year,[@field:Birthday])Integer
GetDate()Current system timestampGetDate()Date
GetUTCDate()Current system timestamp in UTC time.

Note: Not available in Formula field.

GetUTCDate()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
IsDate(expression/value)1 if the expression is a valid date, time or datetime value, and 0 otherwise.IsDate([@field:Date_verified])

For Formula fields use IsDate(CONVERT(nvarchar,[@field:Date_verified],110))

Integer

Miscellaneous Functions and Expressions

FunctionDescriptionExampleReturn
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.Convert(int, [@field:Sale])Depends on data type specified

 

FunctionDescriptionExampleReturn
CaseEvaluates 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
SelectReturns a results set of records from one or more tables.

Note: Not available in Formula field.

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

FunctionDescription
AvgAverage of all records values.
CountAdds 1 to the total for each record.
Count Non BlankAdds one to the total for each record that contains data.
MinSmallest value in specified field of a record set.
MaxLargest value in specified field of a record set.
SumSum  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)

OperatorDescription
+Add
Subtract
*Multiply
/Divide
%Modulo
&Bitwise AND
|Bitwise OR
^Bitwise XOR
~Bitwise NOT

Comparison Operators

Comparison OperatorDescription
=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