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.

Math

Select a measure name from the list.

Select a base group from the list.

For the selected measure, Interactive Crosstabs calculates the percentage of the base group total and displays the percentage value.

For example, compare a cross tab revenue value to the grand total of all revenue values, using the following calculation:

Calculates the percentage of the column total for each measure value in each column.

Math

Select a measure name from the list.

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

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:

Calculates the percentage of the difference between measure values.

Math

Select the first measure name from the list.

Select the second measure name from the list.

Interactive Crosstabs calculates the percentage of the difference between the second measure and the first measure, as follows:

Interactive Crosstabs displays the percentage of the difference in the cross tab cells.

Calculates the percentage of the row total for each measure in each row.

Math

Select a measure name from the list.

For each measure in each row, Interactive Crosstabs calculates the percentage of the row total and displays the percentage value, as follows:

The sum of percent-of-row values in each row is 100%.

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

Calculates the percentage of the cross tab grand total for each measure in each column and row.

Math

Select a measure name from the list.

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:

The sum of all percent-of-total values in the cross tab is 100%.

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

Returns the absolute value of a number without regard to its sign. For example, 6 is the absolute value of 6 and ‑6.

A number that represents the absolute value of num.

The following example returns the absolute value for each number in the TemperatureCelsius data field:

Adds a specified number of days to a date value.

The date value that results from adding the specified number of days to the start date.

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.

The date‑and‑time value that results from adding the specified number of hours to the start date.

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.

The date‑and‑time value that results from adding the specified number of minutes to the start date.

The following example subtracts 30 minutes from each date in the StartTime data field:

Adds a specified number of months to a date value.

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.

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.

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.

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.

The date‑and‑time value that results from adding the specified number of seconds to the start date.

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.

The date value that results from adding the number of weeks to the start date.

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.

The date value that results from adding the number of years to the start date.

The following example adds five years to each date value in the HireDate data field:

Tests if a value is between two specified values.

True if value is between upperBound and lowerBound, or equal to upperBound or lowerBound; returns false otherwise.

The following example tests each value in the SalesTotal data field to see if the value is between 10000 and 20000:

The following example tests each value in the CustomerName data field to see if the value is between A and 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:

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.

Rounds a number up to the nearest specified multiple.

The number that results from the rounding. If num is an exact multiple of significance, no rounding occurs.

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:

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:

The following example rounds prices up to the nearest dollar. If the Price value is 20.30, CEILING( ) returns 21.0.

Returns a number from 1 to 31 that represents the day of the month.

The number of the day of the month for the specified date value.

The following example gets the number of the day for each date value in the ShipDate data field:

Calculates the number of days between two date values.

The number of days between date1 and date2.

The following example calculates the number of days between each value in the invoiceDate data field and each value in the paymentDate data field:

The results show how long it takes to pay invoices.

The following example calculates the number of days from an order date to Christmas:

The following example calculates the number of days from the current date to Christmas. TODAY( ) is a function that returns the current date:

Calculates the number of hours between two date values.

The number of hours between date1 and date2.

The following example calculates the number of hours between each value in the startTime data field and each value in the finishTime data field:

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:

Calculates the number of minutes between two date values.

The number of minutes between date1 and date2.

The following example calculates the number of minutes between each value in the startTime data field and each value in the finishTime data field:

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:

Calculates the number of months between two date values.

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.

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:

Calculates the number of quarters between two date values.

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.

The following example calculates the number of quarters between each value in the PlanClosing data field and each value in the ActualClosing data field:

The following example calculates the number of quarters from each value in the orderDate data field to the end of the year:

Calculates the number of seconds between two date values.

The number of seconds between date1 and date2.

The following example calculates the number of seconds between each value in the startTime data field and each value in the finishTime data field:

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:

Calculates the number of weeks between two date values.

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.

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:

Calculates the number of years between two date values.

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.

The following example calculates the number of years between each value in the HireDate data field and each value in the TerminationDate data field:

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:

Finds the location of a substring in a string.

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.

The following example searches for the substring, Ford, in each ProductName value:

If the product name is 1969 Ford Falcon, FIND( ) returns 6.

The following example searches for the first hyphen (‑) in each product code:

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.

If the product code is ModelA‑1234, LEFT( ) returns the following string:

Returns one value if a specified condition evaluates to TRUE, or another value if the condition evaluates to FALSE.

Returns the doIfTrue value if condition is TRUE or the doIfFalse value if condition is FALSE.

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

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.

Tests if a value is equal to a value in a list.

True if value is equal to one of the check values; returns false otherwise.

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.

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:

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.

Tests if a value in a specified data field is a null value. A null value means that no value exists.

True if a value in the specified data field is a null value; returns false otherwise.

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.

Extracts a substring from a string, starting from the leftmost, or first, character.

A substring of a specific length:

The following example displays the first letter of each name in the CustomerName data field:

The following example uses the LEFT( ) and FIND( ) functions to display the characters that precede the hyphen in a product code:

If the product code is ModelA‑1234, LEFT( ) returns the following string:

Counts the number of characters in a string.

The number of characters in the specified string.

The following example returns the length of each value in the ProductCode data field:

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.

If the product code is ModelA-Ford, RIGHT( ) returns the following string:

Tests if a string matches a pattern.

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:

True if the string matches the pattern; returns false otherwise.

The following example returns true for values in the customerName field that start with D:

The following example returns true for productCode values that contain the substring 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.

Converts all letters in a string to lowercase.

The specified string in all lowercase letters.

The following example displays all the string values in the productLine data field in lowercase:

Returns a Boolean indicating whether a pattern exists within a string.

True if the pattern matches; false otherwise.

The following example uses ECMAScript syntax in the pattern to detect any set of characters followed by the letter C, and returns true:

The following example checks whether the string starts with the letter X, followed by any single character, and ending with C. It returns false.

Returns the modulo value for a number and a divisor.

Returns the remainder value of number divided by divisor.

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.

Returns the month for a specified date value.

If you omit option, MONTH( ) returns the month as a number.

The month for a specified date value.

The following example returns the month (1 – 12) for each value in the ShipDate data field:

The following example returns the full month name for each ShipDate value:

Negates a Boolean expression.

True if the expression evaluates to FALSE, and false if the expression evaluates to TRUE.

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

The previous IF( ) statement is semantically equivalent to the following statement:

Tests if a value in a specified data field is a non‑null value.

True if a value in the specified data field is not a null value; returns false otherwise.

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.

Returns the current date and time.

The current date and time. For 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:

Returns the quarter number for a specified date value.

A number from 1 to 4 that represents the quarter for a specified date value. Quarter 1 starts in January.

The following example displays the quarter number for each value in the CloseDate data field:

The following example displays a string—Q1, Q2, Q3, or Q4—for each value in the CloseDate data field:

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.

If you omit the ascending argument, RANK( ) assumes 0 (descending order).

A number that represents the rank of a value in the specified data field in the table or in the specified group.

The following example ranks each value in the Score data field. The ranking is performed over all the values in the table.

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.

Math

Select the first measure name from the list.

Select the second measure name from the list.

Interactive Crosstabs divides the first measure value by the second measure value and displays the ratio value.

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.

Extracts a substring from a string, starting from the rightmost, or last, character.

A substring of a specific length.

The following example displays the last four characters of each value in the ProductCode data field:

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.

If the product code is ModelA-Ford, RIGHT( ) returns Ford. If the product code is ModelB-Toyota, RIGHT( ) returns Toyota.

Rounds a number to a specified number of digits.

A number rounded to a specified number of digits.

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.

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.

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.

Rounds a number down to a specified number of digits.

A number rounded down to a specified number of digits.

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.

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.

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.

Rounds a number up to a specified number of digits.

A number rounded up to a specified number of digits.

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.

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.

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.

Calculates a running sum for each data row. A running sum is a total accumulated from row to row.

A cumulative total for each row.

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.

Finds the location of a substring in a string. The substring can contain wildcard characters.

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.

The following example searches for the string pattern, S*A, in each product code. If the product name is KBS5412A, SEARCH( ) returns 3.

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.

If the product name is 1969 Ford Falcon, the expression returns 1969.

Calculates the square root of a number.

A number that is the square root of num.

The following example calculates the square root of each value in the LotSize data field:

which translates to the following expression:

Returns the current date that includes a time value of midnight, 12:00 AM.

The current date in the following format:

The following example calculates the number of days from the current date to Christmas:

The following example calculates the number of years from each value in the HireDate data field to the current date:

Removes the leading and trailing blanks from a specified string. TRIM( ) does not remove blank characters between words.

A string with all leading and trailing blank characters removed.

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.

Removes the leading blanks from a specified string.

A string with all leading blank characters removed.

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.

Removes the trailing blanks from a specified string.

A string with all trailing blank characters removed.

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.

Converts all letters in a string to uppercase.

The specified string in all uppercase letters.

The following example displays all the string values in the customerName data field in all uppercase:

Returns a number from 1 to 52 that represents the week of the year.

A number that represents the week of the year for the specified date value.

The following example gets the week number of the year for each date value in the ShipDate data field:

Returns the day of the week for a specified date value.

If you omit option, WEEKDAY( ) assumes option 1.

The day of the week for a specified date value.

The following example gets the full weekday name for each date value in the DateSold data field:

Returns the four‑digit year value for a specified date value.

The number that represents the four‑digit year for the specified date value.

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.