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:

Availability

The Query field is available in the following apps:

Note: The My Defects, My Tasks, and My Test Cases apps have data pre-populated in the Query field, making them unique versions of the Custom Grid app. You may edit or replace these existing queries.

General rules

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.

Operators

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 conditions

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

Parentheses rules

When writing a query, parentheses rules can get complex. A standard, two-condition query looks like this:

  • ((Project.Name = "Blue Team") AND (Owner.UserName = "tom@acme.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 = "tom@acme.com")) AND (Iteration.Name = "October Phase One")) AND (Tags.Name = "Critical"))

Date variables

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...

Examples

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

Date expressions

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:

Expression

Meaning

(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


Examples

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:

  • (PlannedStartDate = "today + 180")

Literal dates

To query using a specific date and time, use the format YYYY-MM-DDTHH:MM:SS.SSSZ.

  • (AcceptedDate > "2014-01-01T23:59:59.000Z")

Details

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.

Null Queries

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 nouns

Unsupported 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:
  • {projectName}
  • {projectOid}
  • {user}

Sample queries

See the following for query examples to give you an idea of how this feature may be used.

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.