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

 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. CASEWHEN [@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.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 – 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