Functions for Reports

Functions let you apply standard mathematical functions to the data from your database, and output the modified data in report columns.

For example, using the count() function, you can output the number of contacts associated with an organization. The column’s expression in this example would be count(contacts.c_id). You can also combine different functions and database fields in the same column’s expression.

Note: There’s a limit of 3,500 characters in the column expression field. You can use replacement variables to create expressions that require more characters. See Add Report Variables.

When you drag a function from the data dictionary to the design surface, the Column Definition window opens. This lets you enter the variables used in the function’s expression. You can either enter the variables in the Expression field or, if the variable is an item listed in the Available Columns/Functions field, you can drag the item into the function’s expression.

Functions

Function	Description
Aggregate Functions	Select from the following to use an aggregate function in the expression. Aggregate functions can be used only when defining output.
`avg(expr)`	This function computes an average value for the data set `expr`.
`count(expr)`	This function returns the number of rows included in the data set `expr`.
`first(expr, sort column1[, sort column2][,...])`	This function returns the first value of data set `expr` based on the order of the specified sort columns. For example, the expression first(threads.note, threads.entered, threads.seq) returns the first thread note in the data set based on time entered and thread sequence.
`group_concat(expr, separator[, sort column 1, ASC\|DESC][, sort column 2, ASC\|DESC][,...])`	This function allows you to combine multiple values from data set `expr` into a delimited list in a single row. The list is delimited by `separator` and ordered by the specified sort columns. For example, the expression `group_concat(threads.note, ',', threads.entered, ASC)` returns a comma-delimited list of thread notes ordered by time entered ascending.
`last(expr, sort column1[, sort column2][,...])`	This function returns the last value of data set `expr` based on the order of the specified sort columns. For example, the expression last(threads.note, threads.entered, threads.seq) returns the last thread note in the data set based on time entered and thread sequence.
`max(expr)`	This function returns the largest numerical value, the last string in alphabetical order, or the latest date in the data set `expr`.
`min(expr)`	This function returns the smallest numerical value, the first string in alphabetical order, or the earliest date in the data set `expr`.
`sum(expr)`	This function returns the sum of the values in the data set `expr`.
`sum_distinct(expr, reference)`	This function returns the sum of distinct values in an expression for a particular record (reference) rather than for all records of the same type in a table. For example, if you want to calculate the sum of all quotas for a particular sales person, you could add a function of `sum_distinct(sa_period2accts.quota, sa_period2accts.acct_id)`.
`stddev(expr)`	This function returns the standard deviation of `expr`. For example, the expression `stddev(answers.solved_count)` returns the standard deviation of all the values in the `solved_count` column of the `answers` table.
String Functions	Select from the following to use a string function in the expression:
`concat(str1, str2)`	This function combines input character strings from multiple expressions into a single output string. Two or more expressions can be appended with this function. For example, the expression `concat('www.','global','.com')` returns the value www.global.com.
`instr(str, substr)`	This function returns the numeric position (in characters) of the initial occurrence of string `substr` in string `str`. For example, the expression `instr('globalcellphones.com','cell')` returns the value `7`.
`length(str)`	This function returns the length (in characters) of the string. For example, the expression `length('cell phones')` returns the value `10`.
`lower(str)`	This function returns string `str` in all lowercase characters. For example, the expression `lower('Global.COM')` returns the value `global.com`.
`ltrim(str)`	This function returns the string `str` without leading spaces. For example, the expression `ltrim(' Cell phone')` returns the value `Cell phone`.
`lpad(str1, X, str2)`	This function returns `str1` padded on the left with `str2` until `str1` is `X` characters long. For example, the expression `lpad('phone', 10, '@')` returns the value `@@@@@phone`.
`rpad(str1, X, str2)`	This function returns `str1` padded on the right with `str2` until `str1` is `X` characters long. For example, the expression `rpad('phone', 10, '@')` returns the value `phone@@@@@`.
`rtrim(str)`	This function returns the string `str` without trailing spaces. For example, the expression `rtrim('agarfield@global.com ')` returns the value `agarfield@global.com`.
`substr(str, start_pos, [length])`	This function returns a portion of the string `str` starting at the character defined by `start_pos` (an integer) and ending at the character defined by `length` (an integer). If `length` isn't indicated, it returns the remainder of the string.
`to_char(expr)`	This function converts the numeric `expr` to a character string.
`to_number(str)`	This function converts the character string `str` to a numeric. If the `str` isn't numeric, this function returns zero. If `str` is a combination of numbers and other characters and begins with a number, this function returns only the initial numeric portion. For example, `to_number('123ABC')` returns 123.
`upper(str)`	This function returns string `str` in all uppercase characters. For example, the expression `upper('global.COM')`returns the value `GLOBAL.COM`.
Date Functions	Select from the following to use a date function in the expression. See Reference Information for a list of the date/time format tokens you can use in date functions. Most of the date functions allow you to combine the supported format tokens in any combination you want. Date functions do not support dates earlier than January 1, 1970.
`date_add(date, units, interval, round)`	This function returns the value of `date` plus a specified amount of time where date is a date/time type column or a literal string in the format YYYY-MM-DD or YYYY-MM-DD HH:MI:SS. The amount of time to add is specified by `units` and `interval`. `Units`is an integer or expression referring to an integer database field specifying the number of intervals to add or subtract. `Interval` can be SECONDS, MINUTES, HOURS, DAYS, WEEKS (Sunday through Saturday), IWEEKS (Monday through Sunday), MONTHS, QUARTERS, or YEARS. To round the result to the beginning of the specified interval, set the `round` argument to 1. If you set `round` to 0, the result will not be rounded. For example, the expression `date_add(‘2013-11-25 22:35:00’, 48, HOURS, 1)` returns the value `2013-11-27 22:00:00`.
`to_date(str, format)`	This function converts the value entered in `str` that's in the format specified in `format` to a date or date/time value. For example, the expression `to_date(‘20140215’,’YYYYMMDD’)` returns a value of `02/15/2014`. Unlike other date functions, `to_date` supports only the following date formats. DD MM YYYY HH24:MI:SS DD MM YYYY HH:MI:SS AM DD MON YY DD MON YYYY DD-MM-YY DD-MM-YYYY DD.MM.YY DD.MM.YYYY DD/MM/YY DD/MM/YYYY DD/MM/YYYY HH:MI:SS AM MM-DD-YY MM-DD-YYYY MM/DD/YY MM/DD/YYYY MON DD YY MON DD YYYY MON DD YYYY HH:MI AM MON DD YYYY HH:MI:SS AM YY.MM.DD YY/MM/DD YYMMDD YYYY-MM-DD YYYY-MM-DD HH24:MI:SS YYYY-MM-DDTHHH24:MI:SS YYYY.MM.DD YYYY/MM/DD YYYYMMDD
`date_format(date, format)`	This function converts `date` from a date/time data type to a string and reformats it to match the format specified by `format`. `Date` can be a date/time type column or a literal string in the format YYYY-MM-DD or YYYY-MM-DD HH:MI:SS. For example, the expression date_format('2013-11-25 22:25:00', 'DAY, MONTH DD, YYYY, HH12:MI PM') returns the value Tuesday, November 25, 2013 10:25 PM.
`date_diff(date, date)`	This function returns the number of seconds occurring between two dates. For example, the expression date_diff('2014-07-26 22:25:00', '2014-07-25 10:30:00’) returns the value 129300. Another example is date_diff(sysdate(),incidents.updated). This expression returns the number of seconds between the current time and the time that incidents were last updated. To change the format of the output, use the time_format function. For example, time_format(date_diff('2014-07-26 22:25:00', '2014-07-25 10:30:00’), 'HH24h MIm SSs') returns the value 35h 55m 00s.
`rel_date_diff(date2, date1, rr_id)`	This function returns the number of seconds between two dates, taking holidays and the work hours specified in response requirements into account. The value for `rr_id` is the response requirement ID that you want to use. We recommend using the value from the incidents.rr_id field. For example, if a site’s response requirements are configured with work hours from 9 A.M. to 5 P.M., the expression rel_date_diff('2014-07-26 22:25:00', '2014-07-25 10:30:00’,1) returns the value `52200`. Another example is rel_date_diff(sysdate(),incidents.rel_due,incidents.rr_id). This expression returns the number of seconds between the current time and incidents’ due dates, excluding holidays and taking the work hours configured in the response requirements associated with the incidents into account. This function isn't available for use in variables, filters, or exceptions. In addition, it cannot be nested in other expressions.
`date_trunc(date, units)`	This function truncates `date` to the unit specified by `units`, with `units` being a time interval of SECONDS, MINUTES, HOURS, DAYS, WEEKS (Sunday through Saturday), IWEEKS (Monday through Sunday), MONTHS, QUARTERS, or YEARS. For example, the expression `date_trunc('2013-11-25 22:25:10', HOURS)` returns the value `2013-11-25 22:00:00`.
`sysdate()`	This function returns the current system date in the format YYYY-MM-DD HH:MI:SS.
`time_format (seconds, format)`	This function converts `seconds` to the specified time format. For example, the expression `time_format(86610,'HH24 MI SS')` returns the value `24 03 30.`
`date_group(expr, format)`	This function lets you group records together according to a date range that you specify, and include rows for date ranges that have no associated records. If you do not want to return rows for date ranges with no records, you could use date_format instead. For example, if you have incidents created in January and March, but none in February, and create a report with the expression for column A set to `date_group(incidents.created, ‘YYYY-MM’)`, and the expression for Column B set to `count(*)`, the report would output: 2014-01 10 2014-02 0 2014-03 15 If you changed Column A’s expression to `date_format(incidents.created, ‘YYYY-MM’)` to use the date_format function instead of date_group, the output would be similar, but would not include a row for February. When this function is used in a column, the report designer automatically groups the data in the output level, and sets the output level’s group and sort order options. At least one date filter is required to use this function.
Logical Functions	Select from the following to use a logical function in the expression.
`if(expression, then result, else result)`	This function returns the then result if the expression is true and returns the else result if the expression is false. For example, the expression `if(incident.c$field=1, ‘Yes’, ‘No’)` returns Yes for incidents where the value of `c$field` is 1. If the value isn't 1, No is returned. You can use IS NULL and IS NOT NULL as part of the expression. For example, you could use the expression `if(incident.c$field IS NOT NULL, 'Yes', 'No')`.
`decode(test expression, test value 1, result 1, [, next test value, next result]* [,default])`	This function compares `test expression` to each `test value` in order and returns `result` for the first `test value` that `expression` matches. If `expression` does not match any `test value`, `default` is returned. If `default` is undefined, NULL is returned. The decode function can perform table lookups, allowing you to use text strings as test values rather than requiring coded values (for example, you can use Review as a test value for the expression `faqs.status` even though the table contains code value in the `status` column).
`nvl(expr1, expr2)`	If the value `expr1` is null, this function returns the value `expr2`. However, if the value `expr1` isn't null, then the value `expr1` is returned. The value of `expr2` must match the data type of `expr1`.
Math Functions	Select from the following to use a math function in the expression.
`bitand(X, Y)`	This function returns the bitwise AND of two integers, `X` and `Y`.
`ceiling(X)`	This function returns the smallest integer value greater than or equal to `X`.
floor(X)	This function returns the largest integer less than or equal to `X`.
`power(X, Y)`	This function returns the value of `X` to the power of `Y`. For example, `power(2,3)` would return `8`.
`rand()`	This function generates a random number between 0 and 1. This output can be used to produce a random sampling of data. For example, to generate a random sample of incidents, add a column with an expression of `rand()` to an incidents report and sort on the column. The report will show information for incidents meeting your search criteria, sorted randomly.
`round(X, D)`	This function returns the value `X` rounded to the nearest number with `D` decimals. For example, `round(5.55555,2)` returns the value `5.56`.
`truncate(X, D)`	This function returns the value `X` truncated to the `D` decimal places. For example, the expression truncate`(5.55555, 2)` returns the value `5.55`.
Currency Functions	Select from the following to use a currency function in the expression.
`cvt_currency(expr, str)`	This function converts the currency value `expr` to the specified currency `str`. For example, the expression `cvt_currency(opportunities.rep_value, 'USD')` returns the `rep_value` in U.S. dollars when “USD” is stored as the abbreviation for U.S. dollars in the `currencies` table.
`make_currency(expr, str)`	This function converts an integer or decimal number to the specified currency `str`. For example, the expression `make_currency(100.00, 'USD')` returns the `expr` in U.S. dollars. You can also replace `str` with the value `dflt` to convert the expression to the user’s currency.
`to_currency(expr, str)`	This function converts an integer `expr` to a value in `str` currency. The numeric value is converted to an amount in the user’s default currency. The conversion rate used for this function is the exchange rate defined in the exchange_rates table. For example, for a user with a default currency of `USD`, `to_currency(sa_products.c$price, ‘EUR’)` returns an amount in `USD` that's equal to the number in the `sa_products.c$price` custom field converted to Euros.