You are not logged in. Click here to log in.

codeBeamer ALM

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

AB
15
26
37

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]

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.

and eq gt true instanceof
or ne le false empty
not lt ge null div mod

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

Refer to tracker item fields in expressions using either:

  • The field’s property E.g. id, namedPriority, assignedTo, customField[0], choiceList[2] or
  • The field’s label E.g. ID, Priority, “Assigned to”, “Estimated Hours”

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

If the field label contains spaces, or any characters other than letters and numbers, the label must be delimited by enclosing it in “” or ‘’. For example, delimit “Assigned to” or “Estimated Hours”.

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

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:

FunctionSignatureMeaning
concatString concat(Object...)Converts all parameters to strings and concatenates them to a single result string.
containsboolean contains(String text, String part)Tests, if the specified text contains the specified part
containsIgnoreCaseboolean containsIgnoreCase(String text, String part)Tests, if the specified text contains the specified part, irrespective of case
endsWithboolean endsWith(String text, String suffix)Tests if the specified text ends with the specified suffix
escapeXmlString escapeXml(String text)Escapes characters with a special meaning in XML with appropriate escape sequences
formatString format(String pattern, Object... args)Create a string representation of the specified arguments using a MessageFormat pattern.
indexOfint indexOf(String text, String part)Finds the first index of the specified part within text, or -1 if not found
joinString join(String[] array, String separator)Joins the elements of the provided array into a single String, separated by the separator
lengthint length(Object)Returns the length of the passed string, or the number of elements of the passed array or collection
printfString printf(String format, Object... args)Create a string representation of the specified arguments using a printf like format
replaceString replace(String text, String replace, String replacement)Replaces a String with another String inside a larger String
splitString[] split(String text, String separator)Splits the provided text into an array (at each occurence of the separator)
startsWithboolean startsWith(String text, String prefix)Tests if the specified text starts with the specified prefix
substringString substring(String text, int start, int end)Gets a substring from the specified String, from the start position (inclusive) to the end position (exlusive)
substringAfterString substringAfter(String text, String separator)Gets the substring after the first occurrence of the separator
substringBeforeString substringBefore(String text, String separator)Gets the substring before the first occurrence of the separator
substringBetweenString 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
toLowerCaseString toLowerCase(String text)Converts a String to lower case
toUpperCaseString toUpperCase(String text)Converts a String to upper case
trimString 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:

FunctionSignatureMeaning
distinctList distinct(List list)Returns a list with all not null/distinct/unique values in the specified list.
minObject min(List list)Returns the minimum/smallest value in the specified list
maxObject max(List list)Returns the maximum/largest value in the specified list
sumNumber sum(List list)Returns the sum/total of all numeric values in the specified list
avgDouble avg(List list)Returns the average of all numeric values in the specified list


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



CodeBeamer also provides the following specific functions:

FunctionSignatureMeaning
ListList List(Object...)Create a List from the passed arguments or the passed array
DateDate Date(String date)Create a date according to the passed argument
roundDateDate roundDate(Date date, String precision)Round a date according to the specified precision (see below)
truncateDateDate truncateDate(Date date, String precision)Truncate a date according to the specified precision (see below)
shiftDateDate shiftDate(Date date, int distance, String unit)Shift a date by the specified distance in the specified unit (see below)
isSameDayboolean isSameDay(Date date1, Date date2)Returns true if both dates are within the same calendar day.
roundInteger round(Number number)Rounds the specified (floating point) number to the nearest Integer.
countryCodeString countryCode(String country)Get the ISO 3166 2-letter country code for the specified country code or English country name
valueInListboolean valueInList(Object value, Object... values)Returns true if the first argument is equal to any of the following arguments
valuesInListboolean valuesInList(Iterable list, Object... values)Returns true if any element of the specified collection or array is equal to any of the following arguments.
objectInListboolean objectInList(NamedDto object, String csv)Returns true if the specified object is referenced in the specified comma-separated list of object references
objectsInListboolean objectsInList(List<? extends NamedDto> objects, String csv)Returns true if any of the specified objects is referenced in the specified comma-separated list of object references
objectIdInListboolean objectIdInList(Identifiable object, Integer... ids)Returns true if the ID of the specified object is equal to any of the specified ids
objectIdsInListboolean objectIdsInList(List<Identifiable> objects, Integer... ids)Returns true if the ID of an object in the specified list is equal to any of the specified ids.


The parameter for the function Date, can be either a date specified as:
  • "yyyy-MM-dd [HH:mm]"

or one of the following String constants:

  • "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 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)


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.