Analytics Formula Syntax



Oneserve analytics makes use of a bespoke formula language to allow you to produce sophisticated reports based on the data in your system.



Operators do arithmetic and logical comparisons using the data from specified columns.

Operator Description
- Negation
^ Exponentiation: Power( m, n ). Function returns m raised to the n<sup>th</sup> power
* Multiplication
/ Division
% Modulus: m%n.
+ Addition
- Subtraction
+ String Concatenation


Note: Parameters in square brackets are optional

Function Description Syntax
Abs Returns the absolute value of a number. Abs(number)
Case Returns one value or another, depending on if the expression is evaluates to True or False. Case variable<br> When value<br> Then returnvalue<br> Else othervalue<br> End
Date Returns the current date. Getdate()
DateAdd Adds or subtracts some interval of time from a date or time. DateAdd(interval, number, date)
DateDiff Computes the difference between two dates. DateDiff(interval, date1, date2)
DatePart Returns part of a date. DatePart(interval, date)
DateSerial Combines date parts together to make a date. Cast<br> (Cast(year AS varchar) + '-' + Cast(month AS varchar) + '-' + Cast(day AS varchar)<br> AS DATETIME)
DateValue Returns a date from a date string. The function can convert dates from many different formats. Convert(DATETIME, date_string)
Day Returns the day of the month. Possible return values are from 1-31. Day(date)
FormatCurrency Format a number value into currency. '$' + Convert(varchar(12), NumericValue, 1)
FormatDateTime Formats a date. Convert(varchar, date, 120)
FormatNumber Formats a number. Convert(varchar(12), NumericValue, 1)
FormatPercent Formats a number as a percentage. Convert(varchar,Convert(Decimal(6,2), number)) + '%'
Hour Returns the hour of the day. Possible return values are 0-23. DatePart(hh, date)
Case Returns one value or another, depending on if the expression is evaluates to True or False. Case<br> When Expression<br> Then True<br> Else False<br> END
InString Returns the character location where one string is found within another string. CharIndex(expressionToFind, expressionToSearch, [start_location])
Int Returns the integer portion of a number, removing any decimal places. Round(Floatvalue,0,1)
IsDate Returns True if the text is a date. IsDate(text)
IsNumeric Returns True if the text is a number. IsNumeric(text)
Lower Converts all characters to lower case. Lower(text)
Left Returns the "length" number of characters from the left side of the input text. Left(character_expression, integer_expression)
Len Returnes the number of characters in the text Len(text)
LTrim Removes the space characters from the left side of the text LTrim(text)
Mid Returns the characters from the middle of the text Substring(text,start,length)
Minute Returns the minute of the hour. Possible return values are 0-59 DatePart(mi,date)
Month Returns the month of the year. Possible return values are 1-12 Month(date)
MonthName Returns the name of the month DateName(month,date)
Now Returns the current date andtime Getdate()
Replace Searches textSearch for textFind replacing it with the textReplaceWithValue Replace(string1,string_to_replace,[replacement_string])
Right Returns the "length" number of characters from the right side of the input text Right(str,len)
Rand Returns a random number between 0 and 1 Rand ([seed])
Round Returns a number rounded to a specified number of decimal places Round(number,length)
RTrim Removes any space characters from the right side of the text RTrim(text)
Second Returns the second of the minute. Possible values are 0-59 DatePart(ss,date))
Sign Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0 Sign(number)
Space Returns text consisting of the number of spaces Space(number)
Square Returns the square of a number Square(number)
String Returns text consisting of the character duplicated the number of times Replicate(text,count)
Reverse Returns the text with the characters in reverse order Reverse(text)
TimeValue Returns a time value from a time string. The function an convert dates from many different formats Convert(varchar(8),Convert(datetime,text),108)
Trim Removes space characters from both sides of the text LTrim(RTrim(text))
Upper Converts all characters to upper case Upper(text)
Weekday Returns the number of the day of the week. Possible return values are 1-7 DatePart(wk,date)
WeekdayName Returns the name of the day corresponding to the weekday number DateName(dw,date)
Year Returns the number of the year of the specified date Year(date)