General Query Examples

The Query field, available in the Custom Grid and other grid-style apps, provides you with a way to filter the data displayed using multiple criteria. You can enter SQL-style queries into the field to find specific work items based on their state, owner, or other fields.

Query field

Examples

Work items with specific text in the name

This example is useful if you use various naming conventions to identify different groups of work items:

(Name contains "Technical Debt")

Work items in a specific iteration (by ID)

You must know the iteration OID to use the following query. You can locate the OID by the number that displays at the end of the URL when viewing the detail page of an iteration, such as rally1.rallydev.com/#/483229521d/detail/iteration/6082450599.

(Iteration.OID = "6082450599")

Work items in a specific iteration (by name)

(Iteration.Name = "September Sprint 2")

Work items with no value selected on a custom drop-down field

(DropDownField = "")

User stories without parent user stories

(Parent = null)

User stories that have parents, but they are not portfolio items

((PortfolioItem = null) AND (Parent != null))

User stories that have parents which are either user stories or feature-level portfolio items

((PortfolioItem != null) OR (Parent != null))

Open defects

(State < "Closed")

Use stories associated with a parent

(Parent.FormattedID = 18)

Use stories needing estimates

((PlanEstimate = null) AND (ScheduleState = "Defined"))

User stories in an iteration needing estimates

(((PlanEstimate = null) AND (ScheduleState = "Defined")) AND (Iteration != null))

User stories with a schedule state value between Defined and Accepted

((ScheduleState > "Defined") AND (ScheduleState < "Accepted"))

User stories without defects

(Defects.ObjectID = null)

User stories with defects

(Defects.ObjectID != null)

Retrospective notes

For this query, use the Notes field on the Iteration object to store your retrospective feedback. If you display just the Notes column, the app will expand to show all of the rich-text content. Be sure to select Iteration as the object in the app settings.

(Notes != null)

Set the order to Descending and the page size to 1 to only display the most recent iteration's notes.

My tasks in the current iteration

This query uses the "today" syntax to show only work in the currently active iteration.

(((Owner.UserName = "paul@acme.com") AND (Iteration.StartDate <= today)) AND (Iteration.EndDate >= today))

Standalone defects reported by more than one customer

This example uses a custom field called Reporting Customers. The Requirement field is used to identify the user story a defect may be associated to.

(((ReportingCustomers > 1) and (State < "Closed")) and (Requirement = null))

Open defects, owned by Bob, associated to a specific user story

(((Owner.UserName = "bob@dobalina.com") and (State < "Closed")) and (Requirement.Name = "Story 2"))

Open defects George found

((SubmittedBy.UserName = "george@vandelayindustries.com") and (State != "Closed"))

Open customer-specific defects

This example uses a custom field named Affected Customers.

((AffectedCustomers contains "ShotzBrewery") AND (State != "Closed"))

Defects without tags

(Tags.ObjectID = null)

Defects that have tags

(Tags.ObjectID != null)

Popular fields

Below are some fields that can help you build a specific query. Not all fields are listed.

Syntax Description
AcceptedDate The date a work item's schedule state is changed to Accepted.
Blocked Identifies if a work item is marked as blocked.
Description Searches contents of the Description field. You may use contains or !contains conditions.
DirectChildrenCount The number of direct children (does not include grandchildren or deeper levels) associated to a user story.
Iteration The iteration a work item is scheduled in. You must know the iteration OID when using this field.
PlanEstimate The value present in the work item's Plan Estimate field.
Release The release a work item is scheduled in. You must know the release OID when using this field.
ScheduleState The state of a work item scheduled in an iteration or release. Standard values are Defined, In-Progress, Completed, and Accepted.
State The state of a defect. Not to be confused with the Schedule State field.
TaskEstimateTotal The total number of estimate hours of tasks associated to a work item.
TaskRemainingTotal The total number of remaining to do hours of tasks associated to a work item.

Common problems

If your query is not returning the data you expect, see the suggestions below.

Correct parentheses syntax

Parentheses used in queries with multiple conditions must be balanced. For every condition in the query, you must add one ( to the front of the string. You must also end each condition beyond the first with )).

Incorrect
((Name = "A") AND (Name = "B") AND (Name = "C"))

Correct
(((Name = "A") AND (Name = "B")) AND (Name = "C"))

Correct field name syntax

While reviewing the Web Services API documentation, be sure to remember that many fields are objects, which contain child elements. For example, Tags are an object in CA Agile Central, but have an element to locate the Name.

May return inconsistent results
((Tags = "Blue") AND (Tags = "Green"))

Correct syntax
((Tags.Name = "Blue") AND (Tags.Name = "Green"))

Ensure that the correct field name is placed to the left of the operator, and the value or date variable is to the right of the operator. A query such as (AcceptedDate < InProgressDate) is invalid, because it contains field names on both sides of the operator.

Case sensitivity

Note that field values must be queried using the correct case. For example, if a username in CA Agile Central is capitalized as George@Vandelay.com, a query formatted as (Owner.UserName = "george@vandelay.com") will not work. The value must match exactly in the query.

Fields that do not contain specified text

When using the !contains (does not contain) condition against a text or string field, you may see fewer results than expected. For example, the query below will find work items that both have a non-empty Notes field, and do not contain the word "blue":

(Notes !contains "blue")

To find work items that contain text other than "blue" and work items with empty fields, structure your query like so:

((Notes !contains "blue") OR (Notes = null))

Project settings

Ensure that your project scoping in the app settings menu is correct. A query will not return work items that reside in projects outside of your selection.

User permissions

Queries will not return work items that reside in projects you do not have permission to view. Ensure that you have permissions in the projects you want to query.

How to build queries

Learn more about formatting syntax in the Query field:

Feedback

Need more help? The CA Agile Central Community is your one-stop shop for self-service and support. To submit feedback or cases to CA Agile Central Support, find answers, and collaborate with others, please join us in the CA Agile Central Community.