Creating Computed Columns
Posted on 12/11/2023
What are Computed Columns?
Computed Columns in BIRT Studio are columns that are created using the custom functions provided in BIRT Studio. The functions in BIRT Studio are somewhat similar to function calls in a modern programming language such as Python or Javascript where it is written as functionName(arg1, arg2, etc.). The function is written independently in a text box that is called on
Creating a Computed Column
To create a computed column in BIRT Studio, right click on a column header and select Column > New Computed Column. This will open the New Computed Column window.
In the Column Label field, enter a label for the column. This will be the name that appears in the column header. There is a Category dropdown that will allow you to browse the existing function categories mentioned in BIRT Studio Functions below. Changing this dropdown changes the functions available in the Select Function area.
To enter a custom expression, select “Advanced…” in any category. This shows a previously hidden Enter Expression text area. Enter your custom expression here. Click the Validate button to check if the expression is valid. If it is valid, a dialog stating “The expression is valid” will appear. If it is invalid, BIRT Studio will alert you. Click OK to close the dialog. Click Cancel to close the New Computed Column window without saving. Click OK in the New Computed Column window to save the computed column.
If you are creating a computed column that is only being used in another computed column, a good practice is to set the label in camelCase so that it is easy to identify it from other computed columns and is a reminder that it is not being used in the final report.
Column labels cannot be changed once the computed column has been saved. Make sure you are happy with the label before saving!
BIRT Studio Functions
The functions in BIRT Studio are separated into several categories in the UI:
- Comparison
- Financial
- Text
- Math
- Date & Time
The functions that appear in the UI when selecting any one of the below categories is not the comprehensive list. There are more functions that are not listed but can still be used to write computed columns. See the full list below. For easier navigation, each function is linked in the Table of Contents.
ABS()
Returns the absolute value of a number without regard to its sign.
ABS(number) Arguments A numeric expression.
A number that represents the absolute value of num
ADD_DAY()
Returns a date that is a specified number of days from a given date.
ADD_DAY(date, daysToAdd) Arguments - date
A date expression.
- daysToAdd
A numeric expression.
The date value that results from adding the specified number of days to the start date
ADD_HOUR()
Returns a date that is a specified number of hours from a given date.
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
The date and time value that results from adding the specified number of hours to the start date
ADD_MINUTE()
Returns a date that is a specified number of minutes from a given date.
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
The date and time value that results from adding the specified number of minutes to the start date
ADD_MONTH()
Returns a date that is a specified number of months from a given date.
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
The date value that results from adding the specified number of months to the start date
ADD_QUARTER()
Returns a date that is a specified number of quarters from a given date.
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
The date value that results from adding the specified number of quarters to the start date
ADD_SECOND()
Returns a date that is a specified number of seconds from a given date.
ADD_SECOND(date, secondsToAdd) Arguments - date
The date or date expression that represents the start date
- 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
The date and time value that results from adding the specified number of seconds to the start date
ADD_WEEK()
Returns a date that is a specified number of weeks from a given date.
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
The date value that results from adding the specified number of weeks to the start date
ADD_YEAR()
Returns a date that is a specified number of years from a given date.
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
The date value that results from adding the specified number of years to the start date
BETWEEN()
Tests if a value is between two specified values
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 (" ")
True if the value is between the upper and lower bounds, false otherwise
CEILING()
Returns the smallest integer greater than or equal to a number.
CEILING(number, significance) Arguments - number
The numeric value to round up
- significance
The multiple to which to round.
The smallest integer greater than or equal to the number
DAY()
Returns a number from 1 to 31 that represents the day of the month
DAY(date) Arguments A date expression.
The number of the day of the month for the specified date value
DIFF_DAY()
Calculates the number of days between two date values
DIFF_DAY(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of days between the two date values
DIFF_HOUR()
Calculates the number of hours between two date values
DIFF_HOUR(date1, date2) Arguments - date1
The first date value. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
- date2
The second date value. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
The number of hours between the two date values
DIFF_MINUTE()
Calculates the number of minutes between two date values
DIFF_MINUTE(date1, date2) Arguments - date1
The first date value. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
- date2
The second date value. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
The number of minutes between the two date values
DIFF_MONTH()
Calculates the number of months between two date values
DIFF_MONTH(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of months between the two date values. The function calculates the difference by subtracting the month number of `date1` from the month number of `date2`
DIFF_QUARTER()
Calculates the number of quarters between two date values
DIFF_QUARTER(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of quarters between `date1` and `date2`
DIFF_SECOND()
Calculates the number of seconds between two date values
DIFF_SECOND(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of seconds between `date1` and `date2`
DIFF_WEEK()
Calculates the number of weeks between two date values
DIFF_WEEK(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of weeks between `date1` and `date2`
DIFF_YEAR()
Calculates the number of years between two date values
DIFF_YEAR(date1, date2) Arguments - date1
The first date value
- date2
The second date value
The number of years between `date1` and `date2`
FIND()
Finds the location of a substring in a string
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
The numberical position of the substring in the string
IF()
Returns one value if a specified condition evaluates to TRUE, or another value if the condition evaluates to FALSE
IF(condition, doIfTrue, doIfFalse) Arguments - condition
The condition to test
- doIfTrue
The value to return if the condition evaluates to TRUE. The value can be a BIRT Expression that evaluates to any of the following in "Types Allowed"
- doIfFalse
The value to return if the condition evaluates to FALSE. The value can be a BIRT Expression that evaluates to any of the following in "Types Allowed"
Returns the `doIfTrue` value if condition is TRUE or the `doIfFalse` value if the condition is FALSE
IN()
Tests if a value is equal to a value in a list
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. Can add as many checks as needed.
Returns TRUE if value is equal
ISNULL()
Tests if a value in a specified data field is a null value. A null value means that no value exists
ISNULL(value) Arguments The data field in which to check for null values
Returns TRUE if a value in the specified data field is null. Returns FALSE otherwise.
LEFT()
Extracts a substring from a string, starting from the left-most, or first, character
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
A substring of a specified 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
LEN()
Counts the number of characters in a string
LEN(str) Arguments The string expression to evaluate
The number of characters in a specified string
LIKE()
Tests if a string matches a pattern
LIKE(str, pattern) Arguments - 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. The pattern does not support full regex. Characters supported are a percent character (%) and an underscore (_).
TRUE if the string matches the pattern. Otherwise FALSE.
LOWER()
Converts all letters in a string to lowercase
LOWER(str) Arguments The string to convert to lowercase
The string converted to lowercase
MATCH()
Returns a boolean indicating whether a pattern exists within a string
MATCH(str, pattern) Arguments - str
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.
TRUE if the string matches the pattern. Otherwise FALSE.
MOD()
Returns the modulo value for a number and divisor
MOD(number, divisor) Arguments - number
The numeric value to divide
- divisor
The numeric value to divide by
The remainder value of number divided by divisor
MONTH()
Returns the month for a specified date value
MONTH(date, option?) Arguments - date
The date value from which to extract the month
- option
An optional parameter that specifies the format of the month. If you do not specify a format, the function returns the month as a number from 1 to 12
- 1 to get the month as a number from 1 to 12
- 2 to get the full month name. The result is locale-specific
- 3 to get the abbreviated month name. The result is locale-specific
The month of the specified date value
NOT()
Negates a boolean expression
NOT(expression) Arguments The boolean value or expression to negate
TRUE if the expression evaluates to FALSE, and FALSE if the expression evaluates to TRUE
NOTNULL()
Tests if a value in a specified data field is a non-null value
NOTNULL(value) Arguments The data field in which to check for non-null values
Returns TRUE if a value in the specified data field is not null. Returns FALSE otherwise.
NOW()
Returns the current date and time
NOW() Arguments None
ReturnsThe current date and time
QUARTER()
Returns the quarter number for a specified date value
QUARTER(date) Arguments The date value from which to extract the quarter
A number from 1 to 4 that represents the quarter for a specified date value
RIGHT()
Extracts a substring from a string, starting from the right-most, or last, character
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
A substring of a specified length
ROUND()
Rounds a number to a specified number of decimal places
ROUND(number, decimalPlaces?) Arguments - number
The numeric value to round
- decimalPlaces
The number of decimal places to round to
A number rounded to a specified number of digits
ROUNDDOWN()
Rounds a number down to a specified number of decimal places
ROUNDDOWN(number, decimalPlaces?) Arguments - number
The numeric value to round
- decimalPlaces
The number of decimal places to round to. If omitted, assumes 0.
A number rounded down to a specified number of digits
ROUNDUP()
Rounds a number up to a specified number of decimal places
ROUNDUP(number, decimalPlaces?) Arguments - number
The numeric value to round
- decimalPlaces
The number of decimal places to round to. If omitted, assumes 0.
A number rounded up to a specified number of digits
SEARCH()
Finds the location of a substring in a string. The substring can contain wildcard characters
SEARCH(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
The numberical position of the substring in the string. The first character of a string starts at 1. If the substring is not found, the function returns 0.
SQRT()
Returns the square root of a number
SQRT(number) Arguments The numeric value to find the square root of
The square root of the specified number
TODAY()
Returns the current date that includes a time value of midnight, 12:00 AM
TODAY() Arguments None
ReturnsThe current date in the format MMM dd, yyyy 12:00 AM
TRIM()
Removes leading and trailing spaces from a string. Does not remove black characters between words
TRIM(str) Arguments The string to remove leading and trailing spaces from
The string with leading and trailing spaces removed
TRIMLEFT()
Removes leading spaces from a string
TRIMLEFT(str) Arguments The string to remove leading spaces from
The string with leading spaces removed
TRIMRIGHT()
Removes trailing spaces from a string
TRIMRIGHT(str) Arguments The string to remove trailing spaces from
The string with trailing spaces removed
UPPER()
Converts all letters in a string to uppercase
UPPER(str) Arguments The string to convert to uppercase
The string converted to uppercase
WEEK()
Returns a number from 1 to 52 that represents the week of the year
WEEK(date) Arguments The date or date expression whose week of the year to get
The week of the year for the specified date value
WEEKDAY()
Returns the day of the week for a specified date value
WEEKDAY(date, option?) Arguments - date
The date value from which to extract the day of the week
- option
An optional parameter that specifies the format of the day of the week. If you do not specify a format, the function returns the day of the week as a number from 1 to 7
- 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. The result is locale-specific
- 5 to get the abbreviated weekday name. The result is locale-specific
The day of the week for the specified date value
YEAR()
Returns the four digit year value for a specified date value
YEAR(date) Arguments The date value from which to extract the year
The number that represents the year for the specified date value