Calculated Issue Fields

From CB-5.4 you can define read-only tracker fields whose content is calculated from other tracker item fields.

Every custom field can become a computed field, by specifying an expression or formula for the field:

Figure: Tracker Customize Field Properties

Example 1: Past Deadline Test (for Issue Resolution )

In this example, we want to display whether we have already passed an issue's scheduled End Date. Click 'Add custom field' at the bottom of the Tracker → Customize → Field Properties tab. ('Past End Date' is already implemented by codeBeamer, using the red exclamation mark in the issue display). Our new custom field is called 'Past Deadline', and it must be set up as type boolean, and it returns true if the issue is very late. Under the 'Layout and Content' column of the Field Properties, for the new custom field, the '=' field is defined as:

not(endDate >= fn:Date("today"))

Be sure to checkmark the list column to list the field (Selected in the Field Properties) if desired. Also be sure to set up appropriate permissions under the Field Access tab, to allow the field to be viewed. The write permissions, as specified under Field Access, only take effect if the custom field's computed-value field definition is deleted, and it becomes a non-computed field. For more information about functions available for the computed tracker fields, see the Functions section of this document, below.

Example 2: Weight = Priority * Severity

We want to combine issue priority and severity into a new field “Weight”, with

Weight = Priority * Severity

Priority is a (single) choice field with the following choice values:

Figure: Priority Choice Field Values

Severity is a (multiple) choice field with these defined values:

Figure: Severity Choice Field Values

All choice fields, except Status and Priority, are lists or arrays of values. However, the GUI for static choice fields currently only allows selection of a single value. So in order to access the first/single value of a (multiple) choice field, we must use the [0] operator, e.g. Severity[0].

The “Highest” priority and the highest severity (“Blocker”) selections both have the lowest ID, so in order to compute a Weight proportional to the logical order of Priority and Severity, we must use operands that are inversely proportional to the choice value ids, e.g. : integer Weight =

(5 - Priority.id) * (6 - Severity[0].id)

An empty Priority or Severity would yield an id of null, so the above formula would return the highest possible weight (= 30) for issues with empty Priority and Severity, which is wrong, so we must handle empty values appropriately, i.e.: integer Weight =

(empty Priority ? 0 : 5 - Priority.id) * (empty Severity ? 0 : 6 - Severity[0].id) 

Example 3: Compute column(s) in an embedded table from the values of other column(s)

Since CB-7.3, a tracker (item) can also have tables. A table consists of one or more columns.

E.g. A table called Matrix containing four columns:




The first two columns A and B contain numeric operands and the other two columns should contain the product and sum/total of the operands (per row).


If you are addressing a table column, e.g. via it's attribute/property name tableColumn[0,1], then you must be aware, that you always address the whole column (an array/vector of column values (indexed by row)).

E.g. for this table

The expression A or tableColumn[0,0] would yield [1, 2, 3] and B or tableColumn[0,1] would be [5, 6, 7].

To address the whole table, use the table's name or attribute, e.g. Matrix or table[0], which would yield an array of table rows, where each row is an array of table column values (in the order of the columns).

[
  [1, 5],
  [2, 6],
  [3, 7]
]

The first table row can be accessed as Matrix[0] or table[0][0], and would yield [1, 5].

So how can we define a third column Product, whose value is the product of A multiplied by B (for each row) ?

If we would define Product to be computed as

tableColumn[0,1] * tableColumn[0,2]

this would mean a multiplication of two vectors, e.g. [1, 2, 3] * [5, 6, 7], an operation not supported by the expression language. Also the result would be a two dimensional array, and not a one-dimensional column value vector !

The solution is, to use a projection: table.{ row | expression } , that iterates over each row in the table and produces an array of values, one value per row according to expression!




The projection/expression

table[0].{row | row[0] * row[1] }

reads as:

• Iterate over table[0]
  • for each row (an array of column values)
    • evalute the expression row[0] * row[1]
• return an array of the expression values per row

So for our example table, the result would be the array/vector: [5, 12, 21]

Please note that tableColumn[x,y] is a fixed property name text, therefore y is not adjusted when column position is changed. When defining formulas involving table columns, one may not rely on the order of columns, but property names have to be checked (this can be done by setting the "Show property name" check-box in field configuration page).

At other places [y] is used as an index number (not text). For example table[x][y] refers to the y-th row in table[x].

Syntax

Unified Expression Language (EL) syntax is used for the field values' formulae.

The unified expression language defines the following literals:

• Boolean: true and false
• Integer: as in Java
• Floating point: as in Java
• String: with single and double quotes; " is escaped as \", ' is escaped as \', and \ is escaped as \\ .
• Null: null

In addition to the . and [] operators, the expression language provides the following operators:

• Arithmetic: +, - (binary), *, / and div, % and mod, - (unary)
• Logical: and, &&, or,||, not, !
• Relational: ==, eq, !=, ne, <, lt, >, gt, <=, ge, >=, le. Comparisons can be made against other values, or against boolean, string, integer, or floating point literals.
• Empty: The empty operator is a prefix operation that can be used to determine whether a value is null or empty.
• Conditional: A ? B : C. Evaluate B or C, depending on the result of the evaluation of A.
• Projection: .{alias|expression} Is a special operator on collections, that iterates over the collection and creates a new collection by evaluating the specified (sub-) expression for each element alias of the original collection.

The precedence of operators highest to lowest, left to right is as follows:

• [] .
• () - Used to change the precedence of operators.
• - (unary) not ! empty
• * / div % mod
• + - (binary)
• < > <= >= lt gt le ge
• == != eq ne
• && and
• || or
• ? :

The following words are reserved for the JSP expression language and should not be used as identifiers.

Many of these words are not in the language now, but they may be in the future, so you should avoid using them. For more information on the Unified Expression Language, you could start with: http://java.sun.com/javaee/5/docs/tutorial/doc/bnahq.html

Context variables

The execution/evaluation context of a unified expression contains the following objects:

You can use the dot (.) operator, to access attributes of context objects, e.g. user.realName or project.name.

To access attributes (fields) of the current tracker item (this), you can ommit this. in front of the attribute/field identifier, e.g. submitter is equivalent to this.submitter.

To refer to (the value) of tracker item fields, the following field identifiers are allowed:

• the field property/attribute name, e.g. id, name, description, submitter, submittedAt, namedPriority, status, assignedTo, subjects, categories, versions, customField[0], choiceList[2], etc.
• or the field label, but only,
  • if the label only consists of alphanumeric characters ([A-Z],[a-z],[0-9]), ‘$‘(dollar sign) and ‘_‘ (underscore),
  • does not start with digits ([0-9])
  • and does not contain HTML markup.
  • e.g. Color is allowed, but Background color, Best.-Nr., Estimated<br/>Effort or <b>v</b>_max (v_max) are not allowed
• or the RESTful field name (lower camel case of field label, with all HMTL markup and not allowed characters removed)
  • e.g. color, backgroundColor, bestNr, estimatedEffort and vmax

The field's property and label are displayed in their respective columns in the Tracker → Customize → Field Properties area.

For static choice fields, in CB-9.3 and newer, you can also refer to

• the list of defined choice field options: choiceField_$options
  • e.g. status_$options or choiceList[1]_$options
• a specific choice field option, per option id or lowercase option name: choiceField_$option[idOrName]
  • e.g. status_$option["new"], namedPriority_$option["high"] or choiceList[1]_$option[2]
  • Choice option names can be changed, to using option ids is safer, although using option names may be more convenient or easier to understand.

To access individual values of multi-value choice fields, use the [] operator, e.g. categories[0], choiceList[2][0]

Please note: choiceList[1]_$option[2] refers to the option with id==2 of the choice field choiceList[1], not the third option.

To access attributes of complex field values, e.g. choice values, use the . operator followed by the attribute, e.g. Priority.id, Status.name, Resolution[0].name

All references, choice and members field values have at least the following attributes:

• id Integer
• name Text

To extract attributes from a multi-value field, use a projection. E.g. to create a list of the names of all users/roles assigned to the item, you can use:

assignedTo.{member|member.name} 

You can use such a projection in combination with the function valuesInList to check if the item is assigned to some particular users, e.g.

fn:valuesInList(assignedTo.{member|member.name}, “bond”, “Project Admin”) ?

Functions

Within field value expressions/formulas, you can use the following standard functions:

Function	Signature	Meaning
concat	String concat(Object...)	Converts all parameters to strings and concatenates them to a single result string.
contains	boolean contains(String text, String part)	Tests, if the specified text contains the specified part
containsIgnoreCase	boolean containsIgnoreCase(String text, String part)	Tests, if the specified text contains the specified part, irrespective of case
endsWith	boolean endsWith(String text, String suffix)	Tests if the specified text ends with the specified suffix
escapeXml	String escapeXml(String text)	Escapes characters with a special meaning in XML with appropriate escape sequences
format	String format(String pattern, Object... args)	Create a string representation of the specified arguments using a MessageFormat pattern.
indexOf	int indexOf(String text, String part)	Finds the first index of the specified part within text, or -1 if not found
join	String join(String[] array, String separator)	Joins the elements of the provided array into a single String, separated by the separator
length	int length(Object)	Returns the length of the passed string, or the number of elements of the passed array or collection
printf	String printf(String format, Object... args)	Create a string representation of the specified arguments using a printf like format
replace	String replace(String text, String replace, String replacement)	Replaces a String with another String inside a larger String
split	String[] split(String text, String separator)	Splits the provided text into an array (at each occurence of the separator)
startsWith	boolean startsWith(String text, String prefix)	Tests if the specified text starts with the specified prefix
substring	String substring(String text, int start, int end)	Gets a substring from the specified String, from the start position (inclusive) to the end position (exlusive)
substringAfter	String substringAfter(String text, String separator)	Gets the substring after the first occurrence of the separator
substringBefore	String substringBefore(String text, String separator)	Gets the substring before the first occurrence of the separator
substringBetween	String substringBetween(String text, String open, String close)	Gets the substring after the first occurrence of the open string and before the occurance of the close string
toLowerCase	String toLowerCase(String text)	Converts a String to lower case
toUpperCase	String toUpperCase(String text)	Converts a String to upper case
trim	String trim(String text)	Removes leading and trailing whitespace

The case of function names is significant. The usage of the prefix fn: when referring to functions is optional, e.g.

concat(customField[3], " ", customField[4], " has downloaded and installed codeBeamer.")

or

fn:format("{0,date,yyyy-MM-dd HH:mm}", submittedAt)

In CodeBeamer 8.2.1 and newer, there is also:

New functions in codeBeamer 9.3.0

Plus additional mathematical functions:

Function	Signature	Meaning
Integer	Integer Integer(Object value)	Returns an Integer, if the specified value is a numeric value, e.g. a Number or a numeric string, otherwise null.
Number	Number Number(Object value)	Returns a Number, if the specified value is a numeric value, e.g. a Number or a numeric string, otherwise null.
random	Double random()	Generates a pseudo random number in the range [0 .. 1)
sin	Double sin(Number angle)	Returns the trigonometric sine of an angle (in radians)
cos	Double cos(Number angle)	Returns the trigonometric cosine of an angle (in radians)
tan	Double tan(Number angle)	Returns the trigonometric tangent of an angle (in radians)
asin	Double asin(Number value)	Returns the arc sine of a value; the returned angle is in the range [-π/2 .. π/2].
acos	Double acos(Number value)	Returns the arc cosine of a value; the returned angle is in the range [0 .. π].
atan	Double atan(Number value)	Returns the arc tangent of a value; the returned angle is in the range [-π/2 .. π/2].
toRadians	Double toRadians(Number angle)	Converts an angle measured in degrees to an approximately equivalent angle measured in radians
toDegrees	Double toDegrees(Number radians)	Converts an angle measured in radians to an approximately equivalent angle measured in degrees
exp	Double exp(Number x)	Returns ex, where e is Euler's number.
sinh	Double sinh(Number x)	Returns the hyperbolic sine of a value, that is defined to be: (ex - e-x) / 2
cosh	Double cosh(Number x)	Returns the hyperbolic cosine of a value, that is defined to be: (ex + e-x) / 2
tanh	Double tanh(Number x)	Returns the hyperbolic tangent of a value, that is defined to be: (ex - e-x) / (ex + e-x), in other words: sinh(x) / cosh(x).
log	Double log(Number x)	Returns the natural logarithm (base e) of a value: ln(x) or loge(x)
log10	Double log10(Number x)	Returns the base 10 logarithm of a value: log10(x)
sqrt	Double sqrt(Number x)	Returns the (positive) square root of a value: √x
cbrt	Double cbrt(Number x)	Returns the cube root of the value: ∛x
pow	Number pow(Number x, Number y)	Returns the value of x raised to the power of y: xy
abs	Number abs(Number x)	Returns the absolute value: |x|
floor	Long floor(Number x)	Returns the largest integer value ≤ x
ceil	Long ceil(Number x)	Returns the smallest integer value ≥ x
round	Long round(Number x)	Returns the integer value, that is closest to x
signum	Integer signum(Number x)	Returns -1, 0 or 1, depending on whether the number is less, equal or greater than 0.

Plus functions for random string generation:

Plus functions for net working time arithmetic, based on the System default and Project specific Working Time settings:

These new functions (plus the previously existing length and join allow to define map/reduce expressions, typically but not exclusively, in conjunction with (multi-level) projections.

E.g.:
A task has multiple subjects (via the subjects reference field). Each subject is another tracker item, that each has a priority.
The priority of the task should reflect the highest priority of all subjects:

max(subjects.{subject|subject.priority})

E.g.:
A Bug refers to one or more Builds, that are affected by this bug (via a custom reference field, e.g. choiceList[1]).
Each Build is a config item, that itself refers to a Release (via the versions field). Different Builds can belong to the same Release.
The field Detected in (Release) of the Bug should now be automatically computed to reflect the Releases of all Builds affected by this bug:

distinct(choiceList[1].{build|build.versions.{version|version}})

Please note the nested projections, which executes as

List result;
for (Object build in choiceList[1]) {
  for (Object version in build.versions) {
     result.add(version);
  }
}
return distinct(result);

E.g.:
The Severity of an item should be the highest Severity of all Subjects:

List(min(subjects.{subject|subject.severities.{severity|severity}}))

or

first(1, ascending(subjects.{subject|subject.severities.{severity|severity}}))

Please note:

• Severity is a choice list/field, so the field value is a list of choice options. By default, only one list element is allowed.
• Severity choice options are ordered descending from Highest to Lowest, so we must use min or first in order to get the highest severity.

E.g.:

Show the last two comments on the tracker item:

join(first(2, reverse(distinct(attachments.{attachment|attachment.description}))).toArray(), "\\\\")

That reads as:

• Collect the comments of all attachments:

  attachments.{attachment|attachment.description} 

• Remove all duplicates and empty comments:

  distinct(...)

• Reverse the order of comments (last comment first):

  reverse(...)

• Extract the first 2 comments (due to reverse order, these are the last/newest 2 comments in descending order):

  first(2, ...)

• Concatenate the comments into a single string, separated by a Wiki linebreak:

  join(... .toArray(), "\\\\")

CodeBeamer also provides the following specific functions:

In CB-9.2 and newer, there is also:

The parameter date for the function Date, can be either an absolute date specified as:

• "yyyy-MM-dd[ HH:mm[:ss]]"
• plus an optional time zone, e.g.
  • "UTC", if the literal represents coordinated universal time
  • "default", "system" or "local", if the literal represents default/system/local time (of the codeBeamer installation/server).
  • the time zone offset from GMT, as "GMT[±][hh[:mm]]"
  • a time zone abbreviation
  • if no timezone for the date is specified, the default will be the time zone of the current user, or, if there is no current user, or if it does not have a special time zone setting, the default time zone of the codeBeamer system.

or one of the following String constants:

• "Now"
• "Today"
• "Tomorrow"
• "Yesterday"
• "Start of this week"
• "End of this week"
• "Start of next week"
• "End of next week"
• "Start of last week"
• "End of last week"
• "Start of this month"
• "End of this month"
• "Start of next month"
• "End of next month"
• "Start of last month"
• "End of last month"
• "Start of this year"
• "End of this year"
• "Start of next year"
• "End of next year"
• "Start of last year"
• "End of last year"

E.g:

endDate >= Date("today")

The precision for roundDate and truncateDate and the unit of shiftDate and timeBetween must be (an abbreviation of) :

• "Year"
• "Month"
• "Week"
• "day"
• "hour"
• "minute"
• "second"

E.g:

roundDate(endDate, "h")
shiftDate(startDate, 30, "min")

For single value attributes like status, priority or submitter, you can use valueInList().
E.g. Check if the name of the user that has submitted the item is “bond” or “klaus”:

valueInList(submitter.name, "bond", "klaus")

But for attributes with multiple values (e.g. assignedTo, supervisors, subjects, versions, etc. plus all custom choice lists), checking for constant values requires valuesInList() in combination with a projection.
E.g. To check if the issue owner/supervisors list contains any of the specified users (by name):

valuesInList(supervisors.{principal|principal.name}, "bond", "klaus")

The function objectIdsInList() is functionally equivalent to

valuesInList(objects.{object|object.id}, ids)

E.g: Check if the field “Resolution” contains a value with ID 2 or 4:

objectIdsInList(resolutions, 2, 4)

or

valuesInList(resolutions.{resolution|resolution.id}, 2, 4)

The groups parameter(s) for the function userInGroup, are the names of User Groups:

There can be any number of additional user groups.

In most cases, it will be more appropriate to check, if a user has a specific System Permission (that can be granted via different User Groups), e.g. instead of

userInGroup(user, "sysadmin")

you would use

userHasPermission(user, "system_admin")

The roles parameter(s) for the function userInRole, are the names of Project Roles :

There can be any number of additional project roles.

E.g. Check if the current user has the role Developer or Tester in the current project:

userInRole(user, project, "Developer", "Tester")

P.S. In most cases, it will be more appropriate to check, if a user has a specific Project Permission (that can be granted via different Project Roles), e.g. instead of

userInRole(user, project, "Project Admin")

you would use

userHasProjectPermission(user, project, "project_admin")

The permissions parameter(s) for the function userHasPermission, are the names of System Permissions:

System permissions are granted to user groups and indirectly to all users in that groups.

The permissions parameter(s) for the function userHasProjectPermission, are the names of Project Permissions:

Project permissions are granted to project roles and indirectly to all project members with that roles.

The permissions parameter(s) for the function userHasTrackerPermission, are the names of Tracker Permissions:

Tracker permissions are granted to project roles via Tracker → Customize → Permissions, and so indirectly to all project members in that roles.

New functions in codeBeamer 9.4.0

E.g. Check if the tracker item does not have suspected links:

!hasSuspectedLink(this)

E.g. Check if the tracker item has upstream suspected links and does not have downstream suspected links:

hasUpstreamSuspectedLink(this) and !hasDownstreamSuspectedLink(this)

Caveats

If you define computed fields, please beware of the following pitfall:

When displaying computed fields, CodeBeamer always computes the value to display on the fly. But because filtering and sorting of the displayed issues is done at database level (via SQL), codebeamer also stores the (shadow) values of computed fields in the database. These shadow values of computed fields in the database are only updated when issues are created or modified, but not when a tracker field computation formula is defined or modified. If you modifiy a field formula for a tracker with existing items, the displayed field values immediately reflect the new formula, but the (shadow) field values in the database do not, and subsequent filtering/sorting by this computed field produces erratic results.

This can also affect exporting data from codeBeamer with custom Word or Excel templates.

• Extracting data from calculated fields with method customField[<index>] returns the shadow value from the database.
• On the other hand, method getByLabel($entity, "Description") returns the value calculated on the fly.