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

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


Returns a random number between 0 and 1 using an optional number as a seed.

When used in Formula Fields a seed is required.



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. 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

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 Return value
year, yyyy, yy 2007
quarter, qq, q 4
month, mm, m 10
dayofyear, dy, y 303
day, dd, d 30
week, wk, ww 45
weekday, dw 1
hour, hh 12
minute, n 15
second, ss, s 32
millisecond, ms 123
microsecond, mcs 123456
nanosecond, ns 123456700
TZoffset, tz

(requires ISO 8601 format)

DatePart(year,[@field:Birthday]) Integer
GetDate() Current system timestamp GetDate() 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))


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. Convert(int, [@field:Sale]) 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’


Select Returns 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

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
* 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