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 | ||

% OF COLUMN | ||

% OF DIFFERENCE | ||

% OF ROW | ||

% OF TOTAL | ||

% 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])

ADD_DAY( )

Adds a specified number of days to a date value.

Syntax

ADD_DAY(date, daysToAdd)

Arguments

date

The date or date expression that represents the start date.

daysToAdd

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:

ADD_DAY([InvoiceDate], 15)

ADD_HOUR( )

Adds a specified number of hours to a date value.

Syntax

ADD_HOUR(date, hoursToAdd)

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.

hoursToAdd

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:

ADD_HOUR([ShipDate], 8)

ADD_MINUTE( )

Adds a specified number of minutes to a date value.

Syntax

ADD_MINUTE(date, minutesToAdd)

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.

minutesToAdd

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:

ADD_MINUTE([StartTime], ‑30)

ADD_MONTH( )

Adds a specified number of months to a date value.

Syntax

ADD_MONTH(date, monthsToAdd)

Arguments

date

The date or date expression that represents the start date.

monthsToAdd

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:

ADD_MONTH([InitialRelease], 2)

ADD_QUARTER( )

Adds a specified number of quarters to a date value.

Syntax

ADD_QUARTER(date, quartersToAdd)

Arguments

date

The date or date expression that represents the start date.

quartersToAdd

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:

ADD_QUARTER([ForecastClosing], 2)

ADD_SECOND( )

Adds a specified number of seconds to a date value.

Syntax

ADD_SECOND(date, secondsToAdd)

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.

secondsToAdd

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:

ADD_SECOND([StartTime], 30)

ADD_WEEK( )

Adds a specified number of weeks to a date value.

Syntax

ADD_WEEK(date, weeksToAdd)

Arguments

date

The date or date expression that represents the start date.

weeksToAdd

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:

ADD_WEEK([OrderDate], 2)

ADD_YEAR( )

Adds a specified number of years to a date value.

Syntax

ADD_YEAR(date, yearsToAdd)

Arguments

date

The date or date expression that represents the start date.

yearsToAdd

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:

ADD_YEAR([HireDate], 5)

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.

IF((BETWEEN([orderDate], "12/1/07 12:00 AM", "12/31/07 12:00 AM")), (ADD_DAY([orderDate], 5)), (ADD_DAY([orderDate], 3)))

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:

DIFF_MONTH([askByDate],[shipByDate])

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:

DIFF_WEEK([askByDate],[shipByDate])

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.

IF((BETWEEN([orderDate], "12/1/07 12:00 AM", "12/31/07 12:00 AM")), (ADD_DAY([orderDate], 5)), (ADD_DAY([orderDate], 3)))

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] )))

[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%:

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)