In reports, what are the aggregate, string, date, logical, and math functions?
Environment:
Analytics
Resolution:
When creating custom reports, you can define functions of the data to use in a filter or to display in the output column. This allows users to customize both the criteria and output for custom reports and views.
Functions are listed as aggregate, string, date, logical, math, and currency. The string, date, logical, and math functions operate on a single value from the database and can be used both when you define filters or when defining output columns.
For example, you can calculate the number of incidents in a data set using the count(expression) function. This will return the total number of incidents within that data. Aggregate functions are used to summarize a data set and can be used when you define the output columns of a view or report. As a result, aggregate functions are not available when defining a data set.
Aggregate functions return a single total or sum for the dataset of values. Aggregate functions are used quite frequently when defining multiple-level views or reports that allow the user to drill down into specific records.
Note: For text fields, such as contacts.email, the default character limit is 255. For text areas, such as incidents.notes, the default character limit is 4000.
Click the 
next to the appropriate heading below to expand that section for viewing.
avg(expr) -- Computes an average value for the expression listed within the parentheses for the data set defined for the report or view.
count(expr) -- Returns the number of rows or records for the data set defined for the report or view. For example, if we have two incidents with i_id's 100 and 101, the expression count(incidents.i_id) would be 2.
group_concat(expr, separator[, sort column 1, ASC|DESC][, sort column 2, ASC|DESC][,...]) -- Combines 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.
max(expr) -- Returns the largest numerical value, the last string in alphabetical order, or the latest date for the data set defined for the report or view.
min(expr) -- Returns the smallest numerical value, the first string in alphabetical order, or the earliest date for the data set defined for the report or view.
sum(expr) -- Returns the sum of the values for the data set defined for the report or view. For example, if we have two incidents with i_id's 100 and 101, the expression sum(incidents.i_id) would be 201.
sum_distinct(expr, reference) -- 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) -- Returns the standard deviation of the data set for the expression listed. 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.
first(expr, sort column1[, sort column2][,....]) -- Returns the first value of data set expr based on the order of the specified 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.
last(expr, sort column1[, sort column2][,....]) -- 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.
concat(str1, str2) -- Combines input character strings str1 and str2 into a single output string. Two expressions can be appended with this function. For example, the expression concat('www.', 'global', '.com') returns the value www.global.com.
instr(str, substr) -- Returns the numeric position of the initial occurrence of the substring (the second expression) within the full string specified. For example, the expression instr('RightNow','Now') returns the value 6.
length(str) -- Returns the length (in characters) of the string. For example, the expression length('cell phones') returns the value 10.
lower(str) -- Returns the text string in all lowercase characters. For example, the expression lower('RightNow') returns the value rightnow.
ltrim(str) -- Returns the string without leading spaces. For example, the expression ltrim(' RightNow') returns the value RightNow.
lpad(str1, X, str2) -- Returns str1 padded on the left with str2 until str1 is X characters long. For example, the expression lpad('Service', 10, '@') returns the value @@@Service.
rpad(str1, X, str2) -- Returns str1 padded on the right with str2 until str1 is X characters long. For example, the expression lpad('Service', 10, '@') returns the value Service@@@.
rtrim(str) -- Returns the string without trailing spaces. For example, the expression rtrim('RightNow ') returns the value RightNow.
substr(str, start_pos ,length) or substr(str, start_pos) -- Returns a portion of the string starting at the character defined by start_pos (an integer) and ending at the character defined by length (an integer). If length is not indicated, it will return the remainder of the string.
to_char(expr) -- Converts the numeric expression to a character string.
to_number(str) -- Converts the character string to a numeric value. If the string is not numeric, this function will return zero. If the string is a combination of numbers and other characters and begins with a number, this function will return only the initial numeric portion. For example, to_number(123ABC) will return 123.
upper(str) -- Returns the string in all uppercase characters. For example, the expression upper('RightNow')returns the value RIGHTNOW.
date_add(date, units, interval, round) -- Returns the value of date plus a specified amount of time where date is a datetime 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 where units must be a positive integer specifying the number of intervals to add, and interval can be SECONDS, MINUTES, HOURS, DAYS, WEEKS, MONTHS, or YEARS. To round the result to the beginning of the specified interval, set the round argument to 1; otherwise, set round to 0 and the result will not be rounded.
For example, the expression date_add(2007-03-28 22:35:00, 48, HOURS, 1) will return the value 2007-03-30 22:00:00.
date_format(date, format) -- Converts the date expression from a datetime datatype to a string and reformats the date to match the format specified by the format expression. Date is a datetime type column or a literal string in the format YYYY-MM-DD or YYYY-MMDD HH:MI:SS. For example, the expression date_format('2007-11-25 22:25:00', 'DAY, MONTH DD, YYYY, HH12:MI PM') returns the value Tuesday, November 25, 2007 10:25 PM.
date_diff(date, date) Returns the number of seconds occurring between two dates. For example, the expression date_diff('2007-03-26 22:25:00', '2007-03-25 22:25:00') would return the value 86400.
Note: To change the output format, use the date_format or time_format function. For example, time_format(date_diff('2007-03-26 22:25:00', '2007-03-25 22:25:00'), 'HH24h Mim SSs') returns 24h 00m 00s.
date_trunc(date, units) -- Truncates the date to the unit specified by units. For example, the expression date_trunc('2007-11-25 22:25:10', HOURS) returns the value 2007-11-25 22:00:00.
sysdate() -- Returns the current system date in the format YYYY-MM-DD HH:MI:SS.
time_format (seconds, format) -- Converts seconds to the specified time format. For example, the expression time_format(86610,'HH24 MI SS') returns the value 24 03 30.
to_date (str, format) -- Converts the value entered in str that is in the format specified in format to a date or date/time value. For example, the expression to_date('20090215','YYYYMMDD') returns a value of 02/15/2009.
rel_date_diff (date2, date1, rr_id) -- (Available for August 2011 and later releases.) Returns the number of seconds between two dates, taking holidays and the work hours specified in response requirements into account. 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('2011-07-26 22:25:00', '2011-07-25 10:30:00', 1) returns the value 52200.
date_group (seconds, format) -- Groups 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: 2011-01 10, 2011-02 0, 2011-03 15.
if(expression, then result, else result) -- 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. custom_field=1, 'Yes', 'No') would return Yes for a value of 1 and No for a value of 0.
decode(expression, test value 1, result 1[next test value, next result] [,default]) -- Compares the expression to the test value and executes result if expression matches test value. If expression and result do not match, default will be executed. 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 the code value in the status column).
For example, you can use the decode function in the Color Expression field to change the display color of incidents with different escalation levels using the following color expression:(incidents.escllevel, 0, ’default’, 1, ’green’, 2, ’yellow’, 3, ’red’)
nvl(expr1, expr2) -- If the value expr1 is null, this function returns the value expr2. However, if the value expr1 is not null, then the value expr1 is returned. The value of expr2 must match the datatype of expr1.
bitand(X, Y) -- Returns the bitwise AND of two integers, X and Y.
ceiling(X) -- Returns the smallest integer value greater than or equal to X.
floor(X) -- Returns the largest integer less than or equal to X.
power(X,Y) -- Returns the value of X to the power of Y. For example, power(2,3) would return 8.
round(X,D) -- 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) -- Returns the value X truncated to the D decimal places. For example, the expression truncate(5.55555, 2) returns the value 5.55.
rand() -- 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.
cvt_currency(expr, str) -- Converts the currency value expr to the specified currency str. For example, the expression cvt_currency(sa_opportunities.rep_value, 'USD') will return the rep_value in US dollars when "USD" is stored as the abbreviation for US dollars in the currencies table.
make_currency(expr, str) -- Converts expr to the specified currency str. For example, the expression make_currency(100.00, 'USD') will return the expr in U.S. dollars. You can also use replace str with the value dflt to convert the expression to the user's currency.
to_currency(expr, str) -- Converts expr to a value in str currency. The value is returned as 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(opportunities.mgr_value, `EUR') will return an amount in USD that is equal to the number in the mgr_value converted to Euros.
For additional information, refer to the 'Functions' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.
