Functions used in computed column expressions
The following list of functions appear when you create expressions to compute column data. Each function entry includes a general description of the function, its syntax, the arguments to the function, the result the function returns, and an example that shows typical usage. Use this reference to find information about a function that you want to use when you insert a computed column to display calculated data in a BIRT design. The following functions are available.
% OF
Calculates the percentage value of a selected measure compared to a selected base group value.
Category
Math
Measure
Select a measure name from the list.
% Base
Select a base group from the list.
Returns
For the selected measure, Interactive Crosstabs calculates the percentage of the base group total and displays the percentage value.
Example
For example, compare a cross tab revenue value to the grand total of all revenue values, using the following calculation:
(<cross tab revenue value>/<cross tab revenue grand total>) * 100%
% OF COLUMN
Calculates the percentage of the column total for each measure value in each column.
Category
Math
Measure
Select a measure name from the list.
Returns
For each measure in each column, Interactive Crosstabs calculates the percentage of the column total and displays the percentage value. The sum of percent‑of‑column values in each column is 100%.
Example
In Measure, select the sales revenue measure. Interactive Crosstabs displays a percentage value in the cell next to each sales revenue value. The percentage value equals the sales revenue value divided by the column sum, and multiplied by 100%, as follows:
(<sales revenue value>/<column sum>) * 100%
% OF DIFFERENCE
Calculates the percentage of the difference between measure values.
Category
Math
Measure
Select the first measure name from the list.
Measure
Select the second measure name from the list.
Returns
Interactive Crosstabs calculates the percentage of the difference between the second measure and the first measure, as follows:
((measureB - measureA)/measureA) * 100%
Interactive Crosstabs displays the percentage of the difference in the cross tab cells.
% OF ROW
Calculates the percentage of the row total for each measure in each row.
Category
Math
Measure
Select a measure name from the list.
Returns
For each measure in each row, Interactive Crosstabs calculates the percentage of the row total and displays the percentage value, as follows:
(<measure value>/<row sum>) * 100%
The sum of percent-of-row values in each row is 100%.
Example
In Measure, select the sales revenue measure. Interactive Crosstabs displays a percentage value in the cell next to each sales revenue value. The percentage value equals the sales revenue value divided by the row total, and multiplied by 100%.
% OF TOTAL
Calculates the percentage of the cross tab grand total for each measure in each column and row.
Category
Math
Measure
Select a measure name from the list.
Returns
For each measure in each column and row, Interactive Crosstabs calculates the percentage of the cross tab grand total and displays the percent value, as follows:
(<measure value>/<cross tab grand total>) * 100%
The sum of all percent-of-total values in the cross tab is 100%.
Example
In Measure, select the sales revenue measure. Interactive Crosstabs displays a percentage value in the cell next to each sales revenue value. The percentage value equals the sales revenue value, divided by the row total, and multiplied by 100%.
ABS( )
Returns the absolute value of a number without regard to its sign. For example, 6 is the absolute value of 6 and ‑6.
Syntax
ABS(num)
Argument
num
The number, or numeric expression that specifies the number for which you want to find the absolute value.
Returns
A number that represents the absolute value of num.
Example
The following example returns the absolute value for each number in the TemperatureCelsius data field:
ABS([TemperatureCelsius])
Adds a specified number of days to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date.
The number of days to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date value that results from adding the specified number of days to the start date.
Example
The following example adds 15 days to each date value in the InvoiceDate data field:
Adds a specified number of hours to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
The number of hours to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date‑and‑time value that results from adding the specified number of hours to the start date.
Example
The following example adds eight hours to each date value in the ShipDate data field:
Adds a specified number of minutes to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
The number of minutes to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date‑and‑time value that results from adding the specified number of minutes to the start date.
Example
The following example subtracts 30 minutes from each date in the StartTime data field:
Adds a specified number of months to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date.
The number of months to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date value that results from adding the specified number of months to the start date. This function always returns a valid date. If necessary, the day part of the resulting date is adjusted downward to the last day of the resulting month in the resulting year. For example, if you add one month to 1/31/08, ADD_MONTH( ) returns 2/29/08, not 2/31/08 or 2/28/08, because 2008 is a leap year.
Example
The following example adds two months to each date value in the InitialRelease data field:
Adds a specified number of quarters to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date.
The number of quarters to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date value that results from adding the specified number of quarters to the start date. A quarter is equal to three months. For example, if you add two quarters to 9/22/08, ADD_QUARTER( ) returns 3/22/09.
Example
The following example adds two quarters to each date value in the ForecastClosing data field:
Adds a specified number of seconds to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
The number of seconds to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date‑and‑time value that results from adding the specified number of seconds to the start date.
Example
The following example adds 30 seconds to each date value in the StartTime data field:
Adds a specified number of weeks to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date.
The number of weeks to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date value that results from adding the number of weeks to the start date.
Example
The following example adds two weeks to each date value in the OrderDate data field:
Adds a specified number of years to a date value.
Syntax
Arguments
date
The date or date expression that represents the start date.
The number of years to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.
Returns
The date value that results from adding the number of years to the start date.
Example
The following example adds five years to each date value in the HireDate data field:
BETWEEN( )
Tests if a value is between two specified values.
Syntax
BETWEEN(value, upperBound, lowerBound)
Arguments
value
The value to test. The value can be a string, numeric, or date value.
upperBound
The first value in the range of values to which to compare. String and date values must be enclosed in double quotation marks (" ").
lowerBound
The second value in the range of values to which to compare. String and date values must be enclosed in double quotation marks (" ").
Returns
True if value is between upperBound and lowerBound, or equal to upperBound or lowerBound; returns false otherwise.
Examples
The following example tests each value in the SalesTotal data field to see if the value is between 10000 and 20000:
BETWEEN([SalesTotal], 10000, 20000)
The following example tests each value in the CustomerName data field to see if the value is between A and M:
BETWEEN([CustomerName], "A", "M")
The following example tests each value in the ReceiptDate data field to see if the value is between 10/01/07 and 12/31/07:
BETWEEN([ReceiptDate], "10/01/07 12:00 AM", "12/31/07 12:00 AM")
The following example uses BETWEEN( ) in conjunction with the IF( ) and ADD_DAY( ) functions to calculate a shipment date. If an orderDate value is in December 2007 (between 12/1/07 and 12/31/07), add five days to the orderDate value. If an orderDate value is in a month other than December, add three days to the orderDate value.
CEILING( )
Rounds a number up to the nearest specified multiple.
Syntax
CEILING(num, significance)
Arguments
num
The numeric value to round up.
significance
The multiple up to which to round num.
Returns
The number that results from the rounding. If num is an exact multiple of significance, no rounding occurs.
Examples
CEILING( ) is commonly used to round up prices. For example, to avoid dealing with pennies, if the Price value is 20.52, CEILING( ) returns 20.55. You can round prices in a Price data field up to the nearest nickel with the following expression:
CEILING([Price], 0.05)
If the Price value is 20.52, CEILING( ) returns 20.60. If the Price value is 20.40, CEILING( ) returns 20.40. No rounding occurs because 20.40 is already a multiple of 0.1. The following example rounds prices up to the nearest dime:
CEILING([Price], 0.1)
The following example rounds prices up to the nearest dollar. If the Price value is 20.30, CEILING( ) returns 21.0.
CEILING([Price], 1)
DAY( )
Returns a number from 1 to 31 that represents the day of the month.
Syntax
DAY(date)
Argument
date
The date or date expression from which you want to extract the day.
Returns
The number of the day of the month for the specified date value.
Example
The following example gets the number of the day for each date value in the ShipDate data field:
DAY([ShipDate])
DIFF_DAY( )
Calculates the number of days between two date values.
Syntax
DIFF_DAY(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of days between date1 and date2.
Example
The following example calculates the number of days between each value in the invoiceDate data field and each value in the paymentDate data field:
DIFF_DAY([invoiceDate],[paymentDate])
The results show how long it takes to pay invoices.
The following example calculates the number of days from an order date to Christmas:
DIFF_DAY([orderDate], "12/25/08")
The following example calculates the number of days from the current date to Christmas. TODAY( ) is a function that returns the current date:
DIFF_DAY(TODAY(), "12/25/08")
DIFF_HOUR( )
Calculates the number of hours between two date values.
Syntax
DIFF_HOUR(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of hours between date1 and date2.
Example
The following example calculates the number of hours between each value in the startTime data field and each value in the finishTime data field:
DIFF_HOUR([startTime],[finishTime])
The following example calculates the number of hours from the current date to Christmas. NOW( ) is a function that returns the current date and time. If you supply a literal date as an argument, you must include the time value, as shown in the following example:
DIFF_HOUR(NOW(), "12/25/08 12:00 AM")
DIFF_MINUTE( )
Calculates the number of minutes between two date values.
Syntax
DIFF_MINUTE(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of minutes between date1 and date2.
Example
The following example calculates the number of minutes between each value in the startTime data field and each value in the finishTime data field:
DIFF_MINUTE([startTime],[finishTime])
The following example calculates the number of minutes from the current date to Christmas. NOW( ) is a function that returns the current date and time. If you supply a literal date as an argument, you must include the time value, as shown in the following example:
DIFF_MINUTE(NOW(), "12/25/08 12:00 AM")
DIFF_MONTH( )
Calculates the number of months between two date values.
Syntax
DIFF_MONTH(date1,date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of months between date1 and date2. The function calculates the difference by subtracting the month number of date1 from the month number of date2. For example, if date1 is 8/1/08 and date2 is 8/31/08, DIFF_MONTH( ) returns 0. If date1 is 8/25/08 and date2 is 9/5/08, DIFF_MONTH( ) returns 1.
Example
The following example calculates the number of months between each value in the askByDate data field and each value in the ShipByDate data field:
The following example calculates the number of months from each value in the hireDate data field to the end of the year:
DIFF_MONTH([hireDate], "1/1/09")
DIFF_QUARTER( )
Calculates the number of quarters between two date values.
Syntax
DIFF_QUARTER(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of quarters between date1 and date2. DIFF_QUARTER calculates the difference by subtracting the month number of date1 from the month number of date2. A difference of three months is equal to one quarter. For example, if date1 is 8/1/08 and date2 is 10/31/08, DIFF_QUARTER( ) returns 0. If date1 is 8/25/08 and date2 is 11/5/08, DIFF_QUARTER( ) returns 1.
Example
The following example calculates the number of quarters between each value in the PlanClosing data field and each value in the ActualClosing data field:
DIFF_QUARTER([PlanClosing],[ActualClosing])
The following example calculates the number of quarters from each value in the orderDate data field to the end of the year:
DIFF_QUARTER([orderDate], "1/1/09")
DIFF_SECOND( )
Calculates the number of seconds between two date values.
Syntax
DIFF_SECOND(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of seconds between date1 and date2.
Example
The following example calculates the number of seconds between each value in the startTime data field and each value in the finishTime data field:
DIFF_SECOND([startTime],[finishTime])
The following example calculates the number of seconds from the current date to Christmas. NOW( ) is a function that returns the current date and time. If you supply a literal date as an argument, you must include the time value, as shown in the following example:
DIFF_SECOND(NOW(), "12/24/08 12:00 AM")
DIFF_WEEK( )
Calculates the number of weeks between two date values.
Syntax
DIFF_WEEK(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of weeks between date1 and date2. The function calculates the difference by subtracting the week number of date1 from the week number of date2. For example, if date1 is 1/3/08 (week 1 of the year), and date2 is 1/7/08 (week 2 of the year), DIFF_WEEK( ) returns 1.
Example
The following example calculates the number of weeks between each value in the askByDate data field and each value in the shipByDate data field:
The following example calculates the number of weeks from each value in the orderDate data field to the end of the year:
DIFF_WEEK([orderDate], "1/1/09")
DIFF_YEAR( )
Calculates the number of years between two date values.
Syntax
DIFF_YEAR(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of years between date1 and date2. The function calculates the difference by subtracting the year number of date1 from the year number of date2. For example, if date1 is 1/1/08 and date2 is 12/31/08, DIFF_YEAR( ) returns 0. If date1 is 11/25/08 and date2 is 1/5/09, DIFF_YEAR( ) returns 1.
Example
The following example calculates the number of years between each value in the HireDate data field and each value in the TerminationDate data field:
DIFF_YEAR([HireDate],[TerminationDate])
The following example calculates the number of years from each value in the HireDate data field to the current date. TODAY( ) is a function that returns the current date:
DIFF_YEAR([HireDate], TODAY())
FIND( )
Finds the location of a substring in a string.
Syntax
FIND(strToFind, str)
FIND(strToFind, str, startPosition)
Arguments
strToFind
The substring to search for. The search is case‑sensitive.
str
The string in which to search.
startPosition
The position in str where the search starts.
Returns
The numerical position of the substring in the string. The first character of a string starts at 1. If the substring is not found, FIND( ) returns 0.
Example
The following example searches for the substring, Ford, in each ProductName value:
FIND("Ford", [ProductName])
If the product name is 1969 Ford Falcon, FIND( ) returns 6.
The following example searches for the first hyphen (‑) in each product code:
FIND("‑", [ProductCode])
If the product code is ModelA‑1234‑567, FIND( ) returns 7.
The following example uses FIND( ) in conjunction with the LEFT( ) function to display the characters that precede the hyphen in a product code. The LEFT( ) function extracts a substring of a specified length, starting from the first character. In this example, the length of the substring to display is equal to the numerical position of the hyphen character.
LEFT([ProductCode], FIND("‑", [ProductCode]))
If the product code is ModelA‑1234, LEFT( ) returns the following string:
ModelA
IF( )
Returns one value if a specified condition evaluates to TRUE, or another value if the condition evaluates to FALSE.
Syntax
IF(condition, doIfTrue, doIfFalse)
Arguments
condition
The condition to test.
doIfTrue
The value to return if condition evaluates to TRUE.
doIfFalse
The value to return if condition evaluates to FALSE.
Returns
Returns the doIfTrue value if condition is TRUE or the doIfFalse value if condition is FALSE.
Example
The following example calculates and displays different discount amounts based on the value in the Total data field. If the Total value is greater than 5000, the discount is 15%. Otherwise, the discount is 10%.
IF([Total]>5000, [Total]*15%, [Total]*10%)
The following example uses IF( ) in conjunction with the BETWEEN( ) and ADD_DAY( ) functions to calculate a shipment date. If an orderDate value is in December 2007 (between 12/1/07 and 12/31/07), add five days to the orderDate value. If a orderDate value is in a month other than December, add three days to the orderDate value.
The following example checks each value in the Office data field. If the value is Boston, San Francisco, or NYC, the computed column displays U.S. If the value is something other than Boston, San Francisco, or NYC, the computed column displays Europe and Asia Pacific.
IF([Office]="Boston" OR [Office]="San Francisco" OR [Office]="NYC", "U.S.", "Europe and Asia Pacific")
IN( )
Tests if a value is equal to a value in a list.
Syntax
IN(value, check1,..., checkN)
Arguments
value
The value to test. The value can be a string, numeric, or date value.
check1, ..., checkN
The value or values to which to compare.
Returns
True if value is equal to one of the check values; returns false otherwise.
Example
The following example tests if New Haven, Baltimore, or Cooperstown are values in the city data field. If any one of the cities is in the data field, IN( ) returns true.
IN([city], "New Haven", "Baltimore", "Cooperstown")
The following example tests if 9/15/08 or 9/30/08 are values in the payDate data field. If you supply a literal date as an argument, you must include the time value, as shown in the following example:
IN([payDate], "9/15/08 12:00 AM", "9/30/08 12:00 AM")
The following example uses IN( ) in conjunction with the IF( ) function to test if Ships or Trains are values in the ProductLine data field. If Ships or Trains is a value in the field, the computed column displays Discontinued Item; otherwise, the product line value is displayed as it appears in the field.
IF(IN([ProductLine], "Ships", "Trains"),"Discontinued Item", [ProductLine])
ISNULL( )
Tests if a value in a specified data field is a null value. A null value means that no value exists.
Syntax
ISNULL(value)
Argument
value
The data field in which to check for null values.
Returns
True if a value in the specified data field is a null value; returns false otherwise.
Example
The following example uses ISNULL( ) in conjunction with the IF( ) function to test for null values in the BirthDate data field. If there is a null value, the computed column displays No date specified; otherwise the BirthDate value is displayed.
IF(ISNULL([BirthDate]), "No date specified", [BirthDate])
LEFT( )
Extracts a substring from a string, starting from the leftmost, or first, character.
Syntax
LEFT(str)
LEFT(str, n)
Arguments
str
The string from which to extract a substring.
n
The number of characters to extract, starting from the first character.
Returns
A substring of a specific length:
If you omit n, the number of characters to extract, the function returns the first character only.
If n is zero, the function returns an empty string.
If n is greater than the length of the string, the function returns the entire string.
Example
The following example displays the first letter of each name in the CustomerName data field:
LEFT([CustomerName])
The following example uses the LEFT( ) and FIND( ) functions to display the characters that precede the hyphen in a product code:
LEFT([ProductCode], FIND("‑", [ProductCode]))
If the product code is ModelA‑1234, LEFT( ) returns the following string:
ModelA
LEN( )
Counts the number of characters in a string.
Syntax
LEN(str)
Argument
str
The string expression to evaluate.
Returns
The number of characters in the specified string.
Example
The following example returns the length of each value in the ProductCode data field:
LEN([ProductCode])
The following example uses LEN( ) in conjunction with the RIGHT( ) and FIND( ) functions to display the characters that appear after the hyphen in a product code. RIGHT( ) extracts a substring of a specified length, starting from the last character. In this example, the length of the entire string returned by LEN( ) minus the length up to the hyphen is the number of characters to display.
RIGHT( [PRODUCTNAME], LEN([PRODUCTNAME] ) - (FIND("-" ,
[PRODUCTNAME] )))
If the product code is ModelA-Ford, RIGHT( ) returns the following string:
A-Ford
LIKE( )
Tests if a string matches a pattern.
Syntax
LIKE(str, pattern)
str
The string to evaluate.
pattern
The string pattern to match. You must enclose the pattern in double quotation marks (" "). The match is case‑sensitive. You can use the following special characters in a pattern:
A percent character (%) matches zero or more characters. For example, %ace% matches any string value that contains the substring ace, such as Facebook, and MySpace. It does not match Ace Corporation because this string contains a capital A, and not the lowercase a.
An underscore character (_) matches exactly one character. For example, t_n matches tan, ten, tin, and ton. It does not match teen or tn.
To match a literal percent (%), underscore (_), precede those characters with two backslash (\\) characters. For example, to see if a string contains M_10, specify the following pattern:
"%M\\_10%"
Returns
True if the string matches the pattern; returns false otherwise.
Example
The following example returns true for values in the customerName field that start with D:
LIKE([customerName], "D%")
The following example returns true for productCode values that contain the substring Ford:
LIKE([productCode], "%Ford%")
The following example uses two LIKE( ) expressions to look for the substrings "Ford" or "Chevy" in each ProductName value. If a product name contains either substring, the computed column displays U.S. Model; otherwise, it displays Imported Model.
IF(((LIKE([ProductName], "%Ford%") = TRUE) OR (LIKE([ProductName], "%Chevy%") = TRUE)), "U.S. Model", "Imported Model")
LOWER( )
Converts all letters in a string to lowercase.
Syntax
LOWER(str)
Argument
str
The string to convert to lowercase.
Returns
The specified string in all lowercase letters.
Example
The following example displays all the string values in the productLine data field in lowercase:
LOWER([productLine])
MATCH( )
Returns a Boolean indicating whether a pattern exists within a string.
Syntax
MATCH(source, pattern)
Arguments
source
The string to evaluate.
pattern
The string pattern to match. The pattern uses ECMAScript (JavaScript) syntax, as defined in Section 15.10 of Standard ECMA-262.
Returns
True if the pattern matches; false otherwise.
Example
The following example uses ECMAScript syntax in the pattern to detect any set of characters followed by the letter C, and returns true:
MATCH("ABC",".*C")
The following example checks whether the string starts with the letter X, followed by any single character, and ending with C. It returns false.
MATCH("ABC","X.C")
MOD( )
Returns the modulo value for a number and a divisor.
Syntax
MOD(number, divisor)
Arguments
number
The number from which to derive the mod value.
divisor
The divisor for the mod function.
Returns
Returns the remainder value of number divided by divisor.
Example
The following example computes the remainder of PriceEstimate data field divided by 12, returning an integer. For example, if the PriceEstimate value is 27365, MOD( ) returns 5.
MOD([PriceEstimate], 12)
MONTH( )
Returns the month for a specified date value.
Syntax
MONTH(date)
MONTH(date, option)
Arguments
date
The date or date expression whose month to get.
option
A number that represents the month format to return. Use one of the following values:
1 to get the month as a number from 1 to 12.
2 to get the full month name, for example, January. The result is locale‑specific.
3 to get the abbreviated month name, for example, Jan. The result is locale‑specific.
If you omit option, MONTH( ) returns the month as a number.
Returns
The month for a specified date value.
Example
The following example returns the month (1 – 12) for each value in the ShipDate data field:
MONTH([ShipDate])
The following example returns the full month name for each ShipDate value:
MONTH([ShipDate], 2)
NOT( )
Negates a Boolean expression.
Syntax
NOT(expression)
Argument
expression
The Boolean value or expression to negate.
Returns
True if the expression evaluates to FALSE, and false if the expression evaluates to TRUE.
Example
The following example uses NOT( ) in conjunction with the IF( ) and LIKE( ) functions. It tests if the value in the State data field is not CA. If the value is not CA, it returns the value of the Markup data field multiplied by 10%. If the value
is CA, it returns the value of the Markup data field multiplied by 15%:
IF(NOT(LIKE([State], "CA")),[Markup]*10%,[Markup]*15%)
The previous IF( ) statement is semantically equivalent to the following statement:
IF(LIKE([State], "CA"),[Markup]*15%,[Markup]*10%)
NOTNULL( )
Tests if a value in a specified data field is a non‑null value.
Syntax
NOTNULL(value)
Argument
value
The data field in which to check for non‑null values.
Returns
True if a value in the specified data field is not a null value; returns false otherwise.
Example
The following example uses NOTNULL( ) in conjunction with the IF( ) function to test for non‑null values in the BirthDate data field. If there is a non‑null value, the BirthDate value is displayed; otherwise the string "No date specified" is displayed.
IF(NOTNULL([BirthDate]), [BirthDate], "No date specified")
NOW( )
Returns the current date and time.
Syntax
NOW( )
Returns
The current date and time. For example:
Sep 23, 2008 11:56 AM
Example
The following example uses the DIFF_MINUTE( ) and NOW( ) functions to calculate the number of minutes from the current date and time to Christmas:
DIFF_MINUTE(NOW(), "12/25/08 12:00 AM")
QUARTER( )
Returns the quarter number for a specified date value.
Syntax
QUARTER(date)
Arguments
date
The date or date expression whose quarter number to get.
Returns
A number from 1 to 4 that represents the quarter for a specified date value. Quarter 1 starts in January.
Example
The following example displays the quarter number for each value in the CloseDate data field:
QUARTER([CloseDate])
The following example displays a string—Q1, Q2, Q3, or Q4—for each value in the CloseDate data field:
"Q" & QUARTER([CloseDate])
RANK( )
Returns the rank of a value in a set of values. The rank of a value ranges from 1 to the number of values in the set. If two values are identical, they have the same rank.
Syntax
RANK(expr)
RANK(expr, ascending, groupLevel)
Arguments
expr
The expression that specifies the values to evaluate. The field can be of string, numeric, or date type.
ascending
Use one of the following values:
0 to rank values in descending order. In descending order, the highest value is ranked 1, and the lowest value is equal to the number of values in the set.
1 to rank values in ascending order. In ascending order, the lowest value is ranked 1, and the highest value is equal to the number of values in the set.
If you omit the ascending argument, RANK( ) assumes 0 (descending order).
groupLevel
The numeric index of the group whose values to use in the calculation. 0 indicates the table, 1 indicates the first group, 2 indicates the second group, and so on. If you do not supply a value for groupLevel, the function performs the calculation over all the values in the table.
Returns
A number that represents the rank of a value in the specified data field in the table or in the specified group.
Example
The following example ranks each value in the Score data field. The ranking is performed over all the values in the table.
RANK([Score])
The following example ranks each value in the Score data field in ascending order. The ranking is performed over all the values in each group at the second level.
RANK([Score], 1, 2)
RATIO
Returns the ratio, MeasureA/MeasureB.
Category
Math
Measure
Select the first measure name from the list.
Measure
Select the second measure name from the list.
Returns
Interactive Crosstabs divides the first measure value by the second measure value and displays the ratio value.
Example
In a product line column, display a revenue column and a customer count column. Use the computed measure, Ratio, to divide revenue by the number of customers. Display the ratio in a Ratio of Revenue to CustomerCount column, as shown in Figure 11‑1. Where the results of division by zero or an empty value occurs, NaN indicates the ratio value is not a number.
Figure 11‑1 Displaying computed ratio values
RIGHT( )
Extracts a substring from a string, starting from the rightmost, or last, character.
Syntax
RIGHT(str)
RIGHT(str, n)
Arguments
str
The string from which to extract a substring.
n
The number of characters to extract, starting from the last character.
Returns
A substring of a specific length.
If you omit n, the number of characters to extract, the function returns the last character only.
If n is zero, the function returns an empty string.
If n is greater than the length of the string, the function returns the entire string.
Example
The following example displays the last four characters of each value in the ProductCode data field:
RIGHT([ProductCode], 4)
The following example uses RIGHT( ) in conjunction with the LEN( ) and FIND( ) functions to display the characters that appear after the hyphen in a product code. This example assumes that the number of characters after the hyphen varies. Therefore, the length of the entire string (returned by LEN( )) minus the length up to the hyphen (returned by FIND( )) is the number of characters to display.
RIGHT([ProductCode], (LEN([ProductCode]) - FIND("-" , [ProductCode])))
If the product code is ModelA-Ford, RIGHT( ) returns Ford. If the product code is ModelB-Toyota, RIGHT( ) returns Toyota.
ROUND( )
Rounds a number to a specified number of digits.
Syntax
ROUND(num)
ROUND(num, dec)
Arguments
num
The number to round.
dec
The number of digits up to which to round num. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded to a specified number of digits.
Example
The following example rounds the numbers in the PriceEstimate data field to return an integer. For example, if the PriceEstimate value is 1545.50, ROUND( ) returns 1546. If the PriceEstimate value is 1545.25, ROUND( ) returns 1545.
ROUND([PriceEstimate])
The following example rounds the numbers in the PriceEstimate data field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUND( ) returns 1545.6. If the PriceEstimate value is 1545.23, ROUND( ) returns 1545.2.
ROUND([PriceEstimate], 1)
The following example rounds the numbers in the PriceEstimate data field to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUND( ) returns 1550. If the PriceEstimate value is 1338.50, ROUND( ) returns 1340.
ROUND([PriceEstimate], ‑1)
ROUNDDOWN( )
Rounds a number down to a specified number of digits.
Syntax
ROUNDDOWN(num)
ROUNDDOWN(num, dec)
Arguments
num
The number to round down.
dec
The number of digits up to which to round num down. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded down to a specified number of digits.
Example
The following example rounds down the numbers in the PriceEstimate data field to return an integer. For example, if the PriceEstimate value is 1545.25, ROUNDDOWN( ) returns 1545. If the PriceEstimate value is 1545.90, ROUNDDOWN( ) returns 1545.
ROUNDDOWN([PriceEstimate])
The following example rounds down the numbers in the PriceEstimate data field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUNDDOWN( ) returns 1545.5. If the PriceEstimate value is 1545.23, ROUNDDOWN( ) returns 1545.2.
ROUNDDOWN([PriceEstimate], 1)
The following example rounds the numbers in the PriceEstimate data field down to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUNDDOWN( ) returns 1540. If the PriceEstimate value is 1338.50, ROUNDDOWN( ) returns 1330.
ROUNDDOWN([PriceEstimate], ‑1)
ROUNDUP( )
Rounds a number up to a specified number of digits.
Syntax
ROUNDUP(num)
ROUNDUP(num, dec)
Arguments
num
The number to round up.
dec
The number of digits up to which to round num up. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded up to a specified number of digits.
Example
The following example rounds up the numbers in the PriceEstimate data field to return an integer. For example, if the PriceEstimate value is 1545.25, ROUNDUP( ) returns 1546. If the PriceEstimate value is 1545.90, ROUNDUP( ) returns 1546.
ROUNDUP([PriceEstimate])
The following example rounds up the numbers in the PriceEstimate data field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUNDUP( ) returns 1545.6. If the PriceEstimate value is 1545.23, ROUNDUP( ) returns 1545.3.
ROUNDUP([PriceEstimate], 1)
The following example rounds up the numbers in the PriceEstimate data field to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUNDUP( ) returns 1550. If the PriceEstimate value is 1338.50, ROUNDUP( ) returns 1340.
ROUNDUP([PriceEstimate], ‑1)
RUNNINGSUM( )
Calculates a running sum for each data row. A running sum is a total accumulated from row to row.
Syntax
RUNNINGSUM(expr)
Arguments
expr
The expression that specifies the values to use in the calculation. The data type must be numeric.
Returns
A cumulative total for each row.
Example
The following example calculates a running sum for each value in the LineItemTotal. For example, if the LineItemTotal field contains 50, 75, 80, 90, and 95, RUNNINGSUM( ) returns 50, 125, 205, 295, and 390 for each row, respectively.
RUNNINGSUM([LineItemTotal])
SEARCH( )
Finds the location of a substring in a string. The substring can contain wildcard characters.
Syntax
SEARCH(pattern, str)
SEARCH(pattern, str, startPosition)
Arguments
pattern
The string pattern to search for. You must enclose the pattern in double quotation marks (" "). You can use the following special characters in a pattern:
An asterisk ( * ) matches zero or more characters, including spaces. For example, t*n matches tn, tin, and teen.
A question mark (?) matches exactly one character. For example, t?n matches tan, ten, tin, and ton. It does not match teen or tn.
str
The string in which to search.
startPosition
The position in str where the search starts.
Returns
The numerical position of the string pattern in the string. The first character of a string starts at 1. If the substring is not found, SEARCH( ) returns 0.
Example
The following example searches for the string pattern, S*A, in each product code. If the product name is KBS5412A, SEARCH( ) returns 3.
SEARCH("S*A", [ProductCode])
The following example uses SEARCH( ) in conjunction with the LEFT( ) function to display the characters that precede the first space character in a product name. The LEFT( ) function extracts a substring of a specified length, starting from the first character. In this example, the length of the substring to display is equal to the numerical position of the space character.
LEFT([ProductName], SEARCH(" ", [ProductName]))
If the product name is 1969 Ford Falcon, the expression returns 1969.
SQRT( )
Calculates the square root of a number.
Syntax
SQRT(num)
Argument
num
The number, or numeric expression that specifies the number, for which you want to find the square root. The number must be a positive number.
Returns
A number that is the square root of num.
Example
The following example calculates the square root of each value in the LotSize data field:
SQRT([LotSize])
The following example uses SQRT( ) to calculate the actual distance traveled uphill, given the base distance and elevation values. This example applies the Pythagorean theorem, which states that . Using this theorem, the actual distance traveled is c, which means we want to calculate:
which translates to the following expression:
SQRT((([Distance] * [Distance]) + ([Elevation] * [Elevation])))
TODAY( )
Returns the current date that includes a time value of midnight, 12:00 AM.
Syntax
TODAY( )
Returns
The current date in the following format:
Sep 25, 2008 12:00 AM
Example
The following example calculates the number of days from the current date to Christmas:
DIFF_DAY(TODAY(), "12/25/08")
The following example calculates the number of years from each value in the HireDate data field to the current date:
DIFF_YEAR([HireDate], TODAY())
TRIM( )
Removes the leading and trailing blanks from a specified string. TRIM( ) does not remove blank characters between words.
Syntax
TRIM(str)
Argument
str
The string from which to remove leading and trailing blank characters.
Returns
A string with all leading and trailing blank characters removed.
Example
The following example uses TRIM( ) to remove all leading and trailing blank characters from values in the FirstName and LastName data fields. The expression uses the & operator to concatenate each trimmed FirstName value with a space, then with each trimmed LastName value.
TRIM([FirstName]) & " " & TRIM([LastName])
TRIMLEFT( )
Removes the leading blanks from a specified string.
Syntax
TRIMLEFT(str)
Arguments
str
The string from which to remove the leading blank characters.
Returns
A string with all leading blank characters removed.
Example
The following example concatenates a literal string with each value in the customerName data field. TRIMLEFT( ) removes all blank characters preceding the customerName value so that there are no extra blank characters between the literal string and the customerName value.
"Customer name: " & TRIMLEFT([customerName])
TRIMRIGHT( )
Removes the trailing blanks from a specified string.
Syntax
TRIMRIGHT(str)
Argument
str
The string from which to remove the trailing blank characters.
Returns
A string with all trailing blank characters removed.
Example
The following example concatenates each value in the Comment data field with a semicolon, then with a value in the Action data field. TRIMRIGHT( ) removes all blank characters after the Comment value so that there are no extra blank characters between the Comment string and the semicolon.
TRIMRIGHT([Comment]) & "; " & [Action]
UPPER( )
Converts all letters in a string to uppercase.
Syntax
UPPER(str)
Argument
str
The string to convert to uppercase.
Returns
The specified string in all uppercase letters.
Example
The following example displays all the string values in the customerName data field in all uppercase:
UPPER([customerName])
WEEK( )
Returns a number from 1 to 52 that represents the week of the year.
Syntax
WEEK(date)
Argument
date
The date or date expression whose week of the year to get.
Returns
A number that represents the week of the year for the specified date value.
Example
The following example gets the week number of the year for each date value in the ShipDate data field:
WEEK([ShipDate])
WEEKDAY( )
Returns the day of the week for a specified date value.
Syntax
WEEKDAY(date, option)
Arguments
date
The date or date expression from which you want to get the day of the week.
option
A number that represents the weekday format to return. Use one of the following values:
1 to get the day as a number from 1 (Sunday) to 7 (Saturday).
2 to get the day as a number from 1 (Monday) to 7 (Sunday).
3 to get the day as a number from 0 (Monday) to 6 (Sunday).
4 to get the full weekday name, for example, Wednesday. The result is locale‑specific.
5 to get the abbreviated weekday name, for example Wed. The result is locale‑specific.
If you omit option, WEEKDAY( ) assumes option 1.
Returns
The day of the week for a specified date value.
Example
The following example gets the full weekday name for each date value in the DateSold data field:
WEEKDAY([DateSold], 4)
YEAR( )
Returns the four‑digit year value for a specified date value.
Syntax
YEAR(date)
date
The date or date expression from which you want to extract the year part.
Returns
The number that represents the four‑digit year for the specified date value.
Example
The following example gets the four‑digit year for each date value in the ShipDate data field, and adds 15 to the four‑digit year. For example, if the ShipDate value is Sep 16, 2008, YEAR( ) returns 2023.
(YEAR([ShipDate]) + 15)