Build App Queries
You can control what criteria to filter work items by with the Query field in the app settings menu. Use SQL-style syntax to add multiple conditions to a query so that you can generate specific results. For example, you can create a query to find all user stories in project X, completed in iteration Y, owned by user Z, with a tag of A.
Building app queries includes the following:
- General rules
- Parentheses rules
- Date variables
- Null queries
- Sample queries
The Query field is available in the following apps:
- Custom Grid
- Portfolio Kanban Board (Requires CA Agile Central Portfolio Manager)
- Portfolio Hierarchy (Requires CA Agile Central Portfolio Manager)
- My Tasks
- My Defects
- My Test Cases
- Story Hierarchy
When creating a custom query, remember the following:
- Queries require correct field names. These are found in your Web Services API documentation.
- Use case-sensitive field names and values such as Owner.UserName and State.
- Boolean field values must be in the form of True or False.
- When querying a custom field, include "c_", before the field name, as noted in the WSAPI documentation. For example, (c_MyCustomField = "MyCustomValue").
- Use a space between the field name, the operator, and the value. For example, use (Project.Name = "Acme") rather than (Project.Name="Acme").
- Do not use spaces in field names; for example, plan estimate should be PlanEstimate.
- You may only write queries that contain a field name on the left of the operator, and a value or date variable on the right side of the operator. For example, the query (CreationDate < AcceptedDate) is invalid, because both CreationDate and AcceptedDate are queryable fields listed in the Web Services API documentation.
- For custom field names, refer to the the Object Model listings in the Web Services API documentation.
Chain expressions cannot be linked together with operators, like this:
- ((Name contains "tito") AND (Notes contains "randy") AND (Description contains "germaine")
The expression must be formed as follows (note the extra set of parentheses around the first two expressions):
- (((Name contains "tito") AND (Notes contains "randy")) AND (Description contains "germaine"))
Multiple conditionsThe following is an example of how to correctly use AND with multiple ORs.
You want to find work items in a specific release that have one of the following values in the Plan Estimate field: No entry, 1, 2, 4, 8, or 16.
This grouping will not return the correct results:
- Release.Name = "Release One" AND (PlanEstimate = "null" OR PlanEstimate = "1" OR PlanEstimate = "2" OR PlanEstimate = "4")
This version has been corrected to use the proper parentheses rules, but will not return all results:
- (((((Release.Name = "Release One") OR (PlanEstimate = "1")) OR (PlanEstimate = "2")) OR (PlanEstimate = "4")) AND (PlanEstimate = "null"))
Writing the query like this forces all of the OR conditions to be met first, and returns all data:
- ((((((PlanEstimate = "null") OR (PlanEstimate = "1")) OR (PlanEstimate = "4")) OR (PlanEstimate = "8")) OR (PlanEstimate = "16" )) AND (Release.Name = "Release One"))
When writing a query, parentheses rules can get complex. A standard, two-condition query looks like this:
- ((Project.Name = "Blue Team") AND (Owner.UserName = "email@example.com"))
You can add as many conditions as you need. Parentheses rules may be different than what you expect. For each condition that you add, you need to put a new ( at the beginning, and each condition after the first must end with )). For example:
((((Project.Name = "Blue Team") AND (Owner.UserName = "firstname.lastname@example.org")) AND (Iteration.Name = "October Phase One")) AND (Tags.Name = "Critical"))
When writing a query against a date field—such as the end of an iteration—you can use variables to automatically update your app as time progresses. There are many variables available:
|Variable||Meaning||Timezone associated with your...||Length|
|Today||current date||user account||1 day|
|Yesterday||the day before today||workspace||1 day|
|Tomorrow||the day after today||workspace||1 day|
|LastWeek||7 days before today||workspace||through the end of today|
|NextWeek||7 days after today||workspace||from the start of today|
|LastMonth||30 days before today||workspace||through the end of today|
|NextMonth||30 days after today||workspace||from the start of today to...|
|LastQuarter||90 days before today||workspace||through the end of today|
|NextQuarter||90 days today||workspace||from the start of today to...|
|LastYear||365 days before today||workspace||through the end of today|
|NextYear||365 days after today||workspace||from the start of the day to...|
To filter for iterations that were modified at any time today in the timezone associated with your user account:
- (LastUpdateDate = "today")
To filter stories that were accepted yesterday but exclude stories accepted today:
- (AcceptedDate = "yesterday")
To include only iterations that have changed in the past week:
- (LastUpdateDate >= "lastweek")
To view Portfolio Items that are expected to start development in the next 30 days:
- ((PlannedStartDate >= "today") AND (PlannedStartDate <= "nextmonth"))
A username variable is also available to assist you with querying for items you own:
- ((Project.Name = "Orange Team") AND (Owner = "currentuser"))
You can also write simple date expressions to query against a date range that is a certain number of days ahead or behind today's date. All date expressions are relative to today, and are written as "today + " or "today - ". Be aware that because date expressions are date ranges, they behave differently than single dates. See the chart below for exact behavior.
Assuming today is Nov 25, 2014:
(AcceptedDate <= "today-7")
Gets dates before and including Nov 25, 2014
(AcceptedDate < "today-7")
Gets dates before Nov 18, 2014
(AcceptedDate >= “today-7”)
Gets dates after and on Nov 18, 2014
(AcceptedDate > “today-7”)
Gets dates after Nov 25, 2014
(AcceptedDate = “today-7”)
Gets dates between and including Nov 18, 2014 and Nov 25, 2014
(AcceptedDate != “today-7”)
Gets dates not between and not including Nov 18, 2014 and Nov 25, 2014
To show iterations that were modified at any time in the past fortnight:
- (LastUpdateDate = "today-14")
To show iterations that have not been updated in the past fortnight:
- (LastUpdateDate < "today-14")
To exclude iterations that were last changed more than two months ago:
- (LastUpdateDate = "today-60")
To view portfolio items that are expected to start development in the next six months:
To query using a specific date and time, use the format YYYY-MM-DDTHH:MM:SS.SSSZ.
- (AcceptedDate > "2014-01-01T23:59:59.000Z")
Order is a sort string which determines the order of the data returned. For example, entering desc will present the data in descending order.
Date variables are case-insensitive. You can write "lastyear" or "NEXTMONTH" or "NextWeek" or "Today+60".White space is optional in date expressions. For example, "today-30" and "today - 30" are equivalent, and mean the same thing as "last month". "today-1" and "yesterday" (and "today+1" and "tomorrow") are not precisely equivalent. "today-1", when interpreted as a range, goes from the start of yesterday to the end of today. "yesterday", when interpreted as a range, goes only from the start of yesterday to the end of yesterday. "today+0" and "today" are not precisely equivalent. "today" is interpreted relative to the timezone associated with your user account, while "today+0" is interpreted relative to the timezone associated with your workspace.
You may search for work items with empty attribute fields by using "null" in most queries. Quotation marks may be used, but are not required:
- (PlanEstimate = null)
- (Parent = "null")
When building a query against a rating or drop-down field, use a set of quotations instead:
- (Priority = "")
Note that custom drop-down fields may use either method, especially if work items were created before the custom fields. To ensure you capture all results, include both sets of syntax:
- ((c_CustomState = null) OR (c_CustomState = ""))
Unsupported nounsUnsupported nouns can be used with curly-brace substitution in custom lists only. These are not supported and may not be present on any given revision:
See the following for query examples to give you an idea of how this feature may be used.