cbQL

The cbQL is a new query language in codebeamer, with which it is possible to create reusable queries. The syntax of the cbQL is similar to the SQL.

Structure

There are four different parts of a cbQL.

• SELECT keyword and projection. This part is optional. If we would like to see additional information about a group of tracker items then it is possible to add aggregation functions and filters or their alias names.
• WHERE keyword and a condition list. The WHERE keyword is required if the cbQL contains SELECT keyword otherwise doesn't.
• GROUP BY keyword and a field or an alias list. This part is optional. It is possible to make groups from the result of the conditions based on the definition. After the GROUP BY keyword.
• ORDER BY keyword and field list and direction. This part is optional. If we would like to order the result, then we can add the necessary fields and the direction of the order.
  

  SELECT assignedTo as 'Assigned to' WHERE storyPoints > 2 GROUP BY assignedTo ORDER BY summary DESC

  
  

Projection section

The result of a cbQL will contain all possible columns of the tracker items by design. The projection section can't reduce the set of the columns but we can add extra information like result of aggregation functions or header of a group. The usage of the section is similar to the SQL projection clause. So we can use here only aggregation functions or fields what we use in the GROUP BY clause.

since 8.0.0

Filter section

The filter options can be used in any order. It is necessary to use AND or OR operator among the filter options.

Date values in cbQL

There are the following options to use dates in the cbQL:

String literal

Patterns: 'yyyy-MM-dd HH:mm:ss', 'yyyy-MM-dd HH:mm', 'yyyy-MM-dd HH', 'yyyy-MM-dd', 'yyyy-MM', 'yyyy'

For example: '2010-01-01'

Short date expression

Pattern: [+-]\d[dwmyq] -> sign number part of date

s = second, min = minute, h = hour, d = day, w = week, m = month, y = year, q = quarter (since 8.0.0), wd = week day(Monday-Friday since 8.0.0)

For example:

+1d -> add one day to current date
-2d -> subtract two days from current date
+1w -> add one week to current date

Long date expression

Pattern: (START|END OF)? NEXT|PREVIOUS /d DAY|WEEK|MONTH|YEAR

For example:

current date: 2016-02-05 08:21:11 (Friday)
START OF NEXT 2 DAY -> it will be add 2 days to current date and truncate it to day. Result: 2016-02-07 00:00:00
END OF NEXT 1 WEEK -> it will be add 1 week to current date and truncate it to week. Result: 2016-02-14 00:00:00

Operators

Comparison operators: =, >=, <=, <, >, !=

BETWEEN

startDate BETWEEN '2012.01.01' AND '2012.01.01'

LIKE

summary LIKE 'test%'
summary NOT LIKE '%test'

IN

project.name IN ('Test', 'Test2')
project.name NOT IN ('Test')

EXISTS, NOT EXISTS

It is possible to define subquery in cbQL. You can use each field in the subquery. The exists will check whether there is a relation between the result of the subquery and the result of the query.

For example:

tracker.id = 7 and exists(tracker.id = 9)

The result will contain only elements which are in the tracker where the id = 7 and which have a relation to/from items which are in the tracker where id = 9

Aggregation function

All of them will return with a single value based on the selected filter. (since 8.0.0)

min

Returns the smallest value. for example:

min(storyPoints)

max

Returns the largest value. for example

max(storyPoints)

avg

Return the average value. for example:

avg(storyPoints)

sum

Returns the sum of values. for example

sum(estimatedTime)

count

Returns number of rows. for example

count(1)

Date functions

Truncate functions

There are some functions with which it is possible to truncate the date fields in the Projection section. (since 8.0.0)

TO_DAY, TO_WEEK, TO_MONTH, TO_QUARTER, TO_YEAR

SELECT TO_MONTH(submittedAt) as 'Month' WHERE project.id = 1 GROUP BY 'Month'

Result: 2016-01-01, 2016-02-01 ...

Date part functions

SELECT GET_MONTH(submittedAt) as 'Month' WHERE project.id = 1 GROUP BY 'Month'

Include Expressions

With the include expression, the following queries can be run:

• Filtering items with a subquery.
• Including reports in queries.
• Including tracker item descriptions as parameters in queries.

See Reports for further information on the Include options available on the UI.

Filtering Items with a Subquery

This function is available since Codebeamer release 10.1.

Filters can be defined in the include expression. It is automatically extracted to the original query.

tracker.id = 3 AND include ={summary='test' or summary='test2'}
same as
tracker.id = 3 AND (summary='test' or summary='test2')

The include expression can be used recursively, so an include expression can contain other include expressions.

Include Reports in Queries

Reports can be included with the following syntax:

include [Report:1234]

Include Tracker Item Description as Parameters in Queries

The description of the used item must be 'Plain'.

It is possible to set the following pattern to description:

• id list: 1,2,3
• tracker reference: ${currentTrackerId}}}
• project reference: ${currentProjectId}}}

Syntax:

tracker.id in (include[123])    //description of 123 = 1,2,3
tracker.id = include[123]       //description of 123 = ${currentTrackerId}
project.id = include[123]       //description of 123 = ${currentProjectId}
item.id in (include[123])       //description of 123 = 2,3,4

The include expression can also be used in filters which filter by ID. For example:

releaseId IN (include[123])

Projection

Fields

project.id

Filter the work items by id of project.

Available operators: =, !=, IN, NOT IN

project.id = 1

project.name

Filter the work items by name of project.

Available operators: =, !=, IN, NOT IN

project.name IN ('Test', 'Test2')

tracker.id

Filter the work items by id of tracker.

Available operators: =, !=, IN, NOT IN

tracker.id = 1

tracker.name

Filter the work items by name of tracker.

Available operators: =, !=, IN, NOT IN

tracker.name = 'Bugs'

item.id

Filter the work items by their id.

Available operators: =, !=, IN, NOT IN

item.id != 2

submittedAt

Filter work items by creation date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

submittedAt BETWEEN '2015-12-12' AND '2200-12-12'

modifiedAt

Filter work items by modification date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

modifiedAt BETWEEN '2015-12-12' AND '2200-12-12'

modifiedBy

Filter work items by the modifier.

Available operators: =, !=, IN, NOT IN

modifiedBy = 1
modifiedBy = 'user name'

startDate

Filter work items by start date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

startDate BETWEEN '2015-12-12' AND '2200-12-12'

endDate

Filter work items by end date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

startDate BETWEEN '2015-12-12' AND '2200-12-12'

assignedTo

Filter the work items by the target of the assignment. The assignment is an user in this case.

Available operators: =, !=, IN, NOT IN

assignedTo IN ('bond','test')
assignedTo = 3 // where 3 = id of an user

assignedToIndirect

Filter the work items by the target of an indirect assignment. The assignment is an user in this case.

Available operators: =, !=, IN, NOT IN

assignedToIndirect = 3 // where 3 = id of an user

assignedToRole

Filter the work items by the target of the assignment. The assignment is a role in this case.

Available operators: =, !=, IN, NOT IN

assignedToRole IN (3276) // where 3276 = id of a role

assignedToRoleIndirect

Filter the work items by the target of an indirect assignment. The assignment is a role in this case.

Available operators: =, !=, IN, NOT IN

assignedToRoleIndirect IN (3276) // where 3276 = id of a role

assignedToGroup

Filter the work items by the target of the assignment. The assignment is a group in this case.

Available operators: =, !=, IN, NOT IN

assignedToGroup IN (121) // where 121 = id of a group

assignedToGroupIndirect

Filter the work items by the target of an indirect assignment. The assignment is a group in this case.

Available operators: =, !=, IN, NOT IN

assignedToGroupIndirect IN (121) // where 121 = id of a group

assignedAt

Filter the work items by the time of the assignment.

Available operators: =, !=, BETWEEN, <, >, <=, >=

assignedAt > -1d AND assignedAt < 1d

owner

Filter the work items by owner. The owner is an user in this case.

Available operators: =, !=, IN, NOT IN

Not supported in baseline mode

owner IN ('bond','test')
owner = 3 // where 3 = id of an user

ownerRole

Filter the work items by owner. The owner is a role in this case.

Available operators: =, !=, IN, NOT IN

Not supported in baseline mode

ownerRole IN (3) // where 3 = id of an role

ownerGroup

Filter the work items by owner. The owner is a group in this case.

Available operators: =, !=, IN, NOT IN

Not supported in baseline mode

ownerGroup IN (3) // where 3 = id of a group

since Carmen

hasAssociation

Filters tracker items with at least one association. Returns outgoing and incoming associations.

Syntax:

hasAssociation = 'true' / 'false'

hasAssociation('<association_type>')

hasAssociation('<association_type>',<item_id1>,<item_id2>,...)

hasAssociation(<item_id1>,<item_id2>,...) // Only available in Expert mode.

hasSuspectedAssociation = 'true' / 'false'
hasNotSuspectedAssociation = 'true' / 'false'

hasSuspectedAssociation('<association_type>')

Examples:

hasAssociation = 'true' // Items with any association type.
hasAssociation = 'false' // Items without associations.

hasAssociation('related') // Items with 'related' association type.

hasAssociation('related',7367483) // Items with 'related' association to 7367483.
hasAssociation('related',7367483,7367482) // Items with 'related' association to 7367483 OR 7367482.

hasAssociation(7548726) // Items with any association type to 7548726. Only available in Expert mode.
hasAssociation(7548726,75487267) // Items with any association type to 7548726 OR 75487267. Only available in Expert mode.

hasSuspectedAssociation = 'true' // Items with any association type AND with 'Suspected References' filtering.
hasNotSuspectedAssociation = 'true' // Items with any association type AND with 'Not Suspected References' filtering.

hasSuspectedAssociation('related') // Items with 'related' association type AND with 'Suspected References' filtering.

For detailed information about the filter that uses this cbQL, see the Has Association section on the Reports page.

hasDownstreamReference

Filter work items by their downstream references. Each items, which is referenced by another item, will be part of the result.

Available operators: =, !=

hasDownstreamReference = 'true'
hasDownstreamReference = 'false'

hasSuspectedLink

Filter work items by their actual suspected links (there have been modification on the referenced item). Each item, which has either a suspected downstream or upstream reference (), will be part of the result.

hasSuspectedLink = 'true'
hasSuspectedLink = 'false'

hasUpstreamReference

Filter work items by their upstream references. Each items, which refer to another item, will be part of the result.

Available operators: =, !=

hasUpstreamReference = 'true'
hasUpstreamReference = 'false'

priority

Filter work items by priority. A special filter option because the possible values of the priority field are configurable differently in each tracker. That cause, it is necessary to define the project and the tracker in the filter option.

Available operators: =, !=, IN, NOT IN

'projectName.trackerName.priority' = 'High'
'1.2.priority' = 'High'
(1 = project's id, 2 = tracker's id)

referenceFromTracker

Filter work items by downstream references. The result items are referenced by an item from the defined tracker or tracker type.

For example: Bug 1 -> Release 1, Task 1 -> User Story1

Available operators: =, !=, IN, NOT IN

type = 'User Story' AND referenceFromTracker = 'Bug'

type = 'User Story' AND referenceFromTracker = '5.8970'    // '<projedtId.trackerId>'

type = 'User Story' AND referenceFromTracker = 8970    // trackerId since 9.0.0

referenceToId

Filter the work items by upstream references. Each work item will be part of the result set, which refers to the specified item.

For example: Bug 1 -> User Story 1 (id = 10)

Available operators: =, !=, IN, NOT IN

referenceToId = 10 //the result will be the 'Bug1' work item

referenceToName

Filter the work items by upstream references. Each work item will be part of the result set, which refers to the specified item.

For example: Bug 1 -> User Story 1

Available operators: =, !=, IN, NOT IN

referenceToName = 'User Story 1' //the result will be the 'Bug1' work item

referenceFromId

Filter the work items by downstream references. Each work item will be part of the result set, which has downstream reference from the specified item.

For example: Bug 1 (id = 10) -> User Story 1

Available operators: =, !=, IN, NOT IN

referenceFromId = 10 //the result will be the 'User Story 1' work item

referenceFromName

Filter the work items by downstream references. Each work item will be part of the result set, which has downstream reference from the specified item.

For example: Bug 1 -> User Story 1

Available operators: =, !=, IN, NOT IN

referenceFromName = 'Bug 1' //the result will be the 'User Story 1' work item

referenceToTracker

Filter the work items by upstream references. Each work item will be part of the result set, which refers to an item of the specified tracker or tracker type.

For example: Bug 1 -> User Story 1

Available operators: =, !=, IN, NOT IN

referenceToTracker = 'User Story'

referenceToTracker = '5.8970'    // '<projedtId.trackerId>' 

referenceToTracker = 8970    // trackerId since 9.0.0

release

Filter work items by release field.

Available operators: =, !=, IN, NOT IN

release = 'Release 1'

resolution

Filter work items by resolution. A special filter option because the possible values of the resolution field are configurable differently in each tracker. That cause, it is necessary to define the project and the tracker in the filter option.

Available operators: =, !=, IN, NOT IN

'projectName.trackerName.resolution' = 'Duplicate'
'1.2.resolution' = 'Later'
(1 = project's id, 2 = tracker's id)

severity

Filter work items by severity. A special filter option because the possible values of the severity field are configurable differently in each tracker. That cause, it is necessary to define the project and the tracker in the filter option.

Available operators: =, !=, IN, NOT IN

'projectName.trackerName.severity' = 'Blocker'
'1.2.severity' = 'Minor'
(1 = project's id, 2 = tracker's id)

status

Filter work items by status. A special filter option because the possible values of the status field are configurable differently in each tracker. That cause, it is necessary to define the project and the tracker in the filter option.

Available operators: =, !=, IN, NOT IN

'projectName.trackerName.status' = 'In Progress'
'1.2.status' = 'New'
(1 = project's id, 2 = tracker's id)

submittedByEmail

Filter work items by email address of submitter. The filter value can be regular expression.

Available operators: =, !=, IN, NOT IN

submittedByEmail = '.*@intland.com'

submittedBy

Filter work items by name of submitter.

Available operators: =, !=, IN, NOT IN

submittedBy = 'James Bond'

summary

Filter work items by summary field.

Available operators: =, !=, LIKE, NOT LIKE

summary LIKE '%item%'

workItemStatus

Filter work items by meaning of the status.

Available operators: =, !=

Available values: Open, Closed, InProgress, Resolved, Deleted

workItemStatus = 'Open'
workItemStatus = 'Closed'

workItemResolution

Filter work items by meaning of resolution.

Available operators: =, !=

Available values: Successful, Unsuccessful

workItemResolution = 'Successful'

type

Filter work items by type of tracker.

Available operators: =, !=, IN, NOT IN

type = 'Bug'

In case of Test Run, Test Case, and Test Set type trackers, TestRun, TestCase, and TestSet strings must be used.

escalationDate

Filter work items by date of their escalations. Each work item will be part of the result set, which has not fired escalation with the defined date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

escalationDate > '2010-01-01'

firedEscalationDate

Filter work items by date of their escalations. Each work item will be part of the result set, which has fired escalation with the defined date.

Available operators: =, !=, BETWEEN, <, >, <=, >=

firedEscalationDate > '2010-01-01'

escalationLevel

Filter work items by level of their escalations. Each work item will be part of the result set, which has not fired escalation on the defined level. (since 8.0.0)

Available operators: =, !=, <, >, <=, >=

escalationLevel > 1

estimatedTime

Filter work items by the estimation time. It is possible to use the short date expression. (since 8.0.0)

Available operators: =, !=, <, >, <=, >=

estimatedTime < 15min
estimatedTime > 1h

spentTime

Filter work items by the spent time. It is possible to use the short date expression. (since 8.0.0)

Available operators: =, !=, <, >, <=, >=

spentTime < 20s
spentTime > 1h

subjectName, subjectId

Filter work items by their subject field. It will work properly if the destination of the field is Work/Config items. (since 8.0.0)

Available operators: =, !=, IN, NOT IN

subject = 'Summary of User Stroy'
 subjectName = 'Summary of Requirement'
 subjectId IN (1, 2, 3)

teamName, teamId

Filter work items by their team field. It will work properly if the destination of the field is Work/Config items. (since 8.0.0)

Available operators: =, !=, IN, NOT IN

team = 'Summary of Team'
 teamName = 'Summary of Team'
 teamId IN (1, 2, 3)

releaseMethod

Filter the work item by their release method field which is a choice field.

Available operators: =, !=, IN, NOT IN

releaseMethod = 'Scrum'

parentName, parentId

Filter the work items by their parent. (since 8.0.0)

Available operators: =, !=, IN, NOT IN

parentName = 'Name of a tracker item'
parentId = 1

In special cases when we would like to see for example the Test Run's results we have to use this field to filter the report:

parentId IS NOT NULL

which means the report will show only the result items of the Test Runs.

childName, childId

Filter the work items by their children. (since 8.0.0)

Available operators: =, !=, IN, NOT IN

childName = 'Name of a tracker item'
childId = 1

firedEscalationLevel

Filter work items by level of their escalations. Each work item will be part of the result set, which has fired escalation on the defined level.

Available operators: =, !=, <, >, <=, >=

firedEscalationLevel > 1

escalation

Filter work items by level of their escalations. The value of this option is a formula: (start date of escalation, end date of escalation, level of escalation, FIRED to filter only fired escalation (optional))

Available operators: =

escalation = '(2011-01-01,2010-02-02,2)'
escalation = '(-1w,+1d,3,FIRED)'

storyPoints

Filter work items by Story Points.

Available operators: =, !=, <, >, <=, >=, IN, NOT IN

storyPoints > 1
storyPoints IN ('1', '2')

projectTag

Filter work items by their project tags. It is possible to add tag for the projects. If a project has a tag which is in the filter section, then all tracker item will be part of the result which is in the project. (since 8.0.1)

Available operators: =, !=, IN, NOT IN

projectTag = 'Development'

trackerTag

Filter work items by their tracker tags. It is possible to add tag for the trackers. If a tracker has a tag which is in the filter section, then all tracker item will be part of the result which is in the tracker. (since 8.0.1)

Available operators: =, !=, IN, NOT IN

trackerTag = 'Support'

trackerItemTag

Filter work items by their tags. (since 8.0.1)

Available operators: =, !=, IN, NOT IN

trackerItemTag = 'Development'

platformName, platformId

Filter work items by their platform field. It will work properly if the destination of the field is Work/Config items. (since 8.0.0)

Available operators: =, !=, IN, NOT IN

platform = 'Summary of User Stroy'
platformName = 'Summary of Requirement'
platformId IN (1, 2, 3)

customField

It is possible to filter the tracker items by user defined fields. To use this filter it is necessary to define the id of the tracker in which we would like to use the filter, because the custom fields can be different in each tracker. The property name of a field is available on the tracker configuration page. (since 8.0.0)

'1234.customField[1]' = 1 //where 1234 the id of the tracker and the customField[1] is the property name of field
'1234.choiceList[1]' = 'Option1'

description

Filter the work items by the content of their description. The description is a special field because it can store large texts. (since 8.1.0)

Available operator: LIKE

description LIKE '%content%'

Not implemented in baseline mode.

hasReview

When a Review is created then the items in the Review have a reference to the Review. This filter option will check the existence of this reference. (since 8.2.0)

Available operator =, !=

hasReview = 'true'     //the result will contain items which are in a Review
hasReview = 'false'    //the result will contain items which aren't in any Reviews

underReview

Filter the items by the status of the Review in which they are. (since 8.2.0)

Available operators =, !=

underReview = 'true'  //show items which are in a Review and the status of the Review is not Closed
underReview = 'false' //show items which are in a Review and the status of the Review is Closed

include

Filter items by a subquery since 10.1. It is possible to define into include expression which will be automatically extracted to the original query:

tracker.id = 3 AND include ={summary='test' or summary='test2'}
same as
tracker.id = 3 AND (summary='test' or summary='test2')

It is possible to use include expression recursively so an include expression can contain other include expressions.

upstream/downstreamReference

It is possible to filter items by their references since 10.0.

For example: all task which has release reference which is 'In Progress' state.

1=Task tracker id
31=Release field id
2=Release tracker id

tracker.id = 1 AND 'upstreamReference.31' ={'2.status' = 'In Progress'}

It is possible to use shared fields :

1=Task tracker id
2=Shared field id

tracker.id = 1 AND downstreamReference.cbQLGlobalType.2 = 'Item'

Grouping

It is possible to create group from the tracker items and calculate information via aggregation functions. There are two different part of the cbQL which are related to grouping. The first is the Projection which starts 'SELECT' keyword. After that field and alias pairs with optionally aggregation function.

SELECT storyPoints as 'Alias of Story Points', ...
SELECT max(storyPoints) as 'Maximum value of Story Point', ...

Another part is the grouping part starts with 'GROUP BY' after that aliases.

GROUP BY 'Alias of Story Points',...

The alias will be the connection between the select and the grouping part. It is not possible to add alias to the Projection which is not part of the Grouping section except if it uses aggregation function. Basically the rule is similar like in SQL:

Order

The structure of the order section is similar to the SQL, which means: first a field : project.id after that optionally a direction: ASC,DESC. It is possible to add more than one option with comma separate.

ORDER BY project.id ASC, item.id DESC

Current Item Placeholder

Since Codebeamer release 2.1 (IMPALA), the Export to Word workflow action is available to export the items of a tracker to Microsoft Word documents if a specific state transition defined in the tracker configuration is performed.

Reports can also be selected as the source of tracker items to be exported to Word via the Export to Word workflow action.

With the use of the 'current item' placeholder in Reports, the tracker items to be exported are dependent on the current item when the configured Export to Word workflow action is started.

The 'current item' placeholder is implemented for the following resolvers:

• ItemID
• ParentID and ChildID
• Default reference fields: Subject, Release, Team, Platform, Area
  The 'current item' placeholder only works with the default reference fields if those are used as reference fields, and are not reconfigured into other field types.
• Custom reference fields

Example:

item.id IN ('current item') 

Using a report containing the 'current item' placeholder as the source of tracker items to be exported, in item context, that is, when the Export to Word workflow action is started, the 'current item' placeholder is replaced with the ID of the actual tracker item the status change of which triggered the workflow action.

Example:

Filtering Performance Warnings

Since codebeamer 22.04 (FELICITY) release, the Filtering Performance Warnings feature supports the creation of more effective filtering queries.

This feature has been developed to notify users by displaying warning hints in banners when such query structures are used that could cause performance issues.

How to enable/disable the feature?

By default, the Filtering Performance Warnings are enabled in codebeamer. This feature can be disabled in the Preferences ► REPORT / TRACKER VIEW SETTINGS by deselecting the checkbox.

Availability

The performance warning hints are available in

• Reports
• Tracker Views:
  • Table View
  • Intelligent Table View
  • Document View
  • Document Edit View
  • Traceability Browser
  • Kanban Board
  • Planner View (Release Tracker)

thus, everywhere within codebeamer where cbQL queries can be performed.

Filtering Performance Warnings are also displayed when using Column Filters.

Indicators of performance issues and the hints displayed

1. Missing Project or Tracker filter
   In case only a general filter is applied (e.g. on Status) without filtering on a given Tracker of Project.
   Displayed warning:
   "For better performance limit the scope of your search to a single project or tracker."
2. Several AND/OR conditions
   The application of 2 or more OR condition activates the user hint.
   Displayed warning:
   "The underlying query uses the OR operator multiple times which can lead to poor performance. You may have selected several meaning options or choice options from multiple trackers."
3. ORDER BY, GROUP BY clauses
   Using a GROUP BY or an ORDER BY clause activates the warning. The joint usage of GROUP BY and ORDER BY activates the hints as well.
   Displayed warning:
   "For better performance add more filter criteria to your grouping query."
   "For better performance add more filter criteria to your ordering query."
4. LIKE/NOT LIKE operators
   Performance warnings are always shown when applying the LIKE/NOT LIKE operators.
   Displayed warning:
   "For better performance avoid using the like operator. If you know the exact text you are looking for, use equals instead."
   
5. Complex upstream/downstream reference filters
   Applying further filters (e.g. on Summary, Description, etc.) within the active upstream or downstream reference filters, the displayed warning is:
   "For better performance limit the scope of your search to a single project or tracker and For better performance consider using simpler reference filters."
6. Historical filters
   Using a Historical filter automatically activates the performance warning.
   Displayed warning:
   "Using historical filters may lead to poor query performance."
   
   Displayed warning after switching to Expert mode:
   "Historical filters do not appear in cbQL expressions, but still saved with the Report in Expert mode."

Notes

Please note that deprecated Milestone field type is not supported in cBQL, reports, filters and exports.

In Historical View only the Default fields and Reference fields work for Group by / Order by.