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.
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))
(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)
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 = "firstname.lastname@example.org") 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 = "email@example.com") and (State < "Closed")) and (Requirement.Name = "Story 2"))
Open defects George found
((SubmittedBy.UserName = "firstname.lastname@example.org") 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)
Below are some fields that can help you build a specific query. Not all fields are listed.
|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.|
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 )).
((Name = "A") AND (Name = "B") AND (Name = "C"))
(((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"))
((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.
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 = "email@example.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))
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.
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: