Summary
Oneserve analytics makes use of a bespoke formula language to allow you to produce sophisticated reports based on the data in your system.
Guide
Operators
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 |
Functions
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) |