Edit

Share via

Query by numeric fields

Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022 | Azure DevOps Server 2020

Learn how to query by numeric fields such as effort, schedule estimates, story points, or time-tracking fields in Azure Boards and Azure DevOps.

Common numeric fields track effort for requirements or estimated, remaining, and completed work for tasks. Use queries to list the work items you care about, then create charts that show either a count of work items or a sum of a numeric field.

Prerequisites

Category Requirements
Access levels - To view and run shared queries: Project member.
- To add and save a shared query: At least Basic access.
Permissions Contribute permission set to Allow for the folder that you want to add a query to. By default, the Contributors group doesn't have this permission.

Note

Users with Stakeholder access for a public project have full access to query features just like users with Basic access. For more information, see Stakeholder access quick reference.

Category Requirements
Access levels - To view and run shared queries: Project member.
- To add and save a shared query: At least Basic access.
Permissions Contribute permission set to Allow for the folder that you want to add a query to. By default, the Contributors group doesn't have this permission.

Use operators and macros

Clauses that reference numeric fields support these operators:

  • =, <>, >, <, >=, <=
  • =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field]
  • In, Not In
  • Was Ever

Develop chart-based queries

Tips for building queries that feed charts:

  • Add charts to flat-list queries only.
  • Reference either query filters or fields displayed through column options.
  • Save your query before you add or modify a chart.
  • To group clauses, select them and use the group-clauses icon; to ungroup, select the grouped clause.

For more information, see Use the query editor to list and manage queries, Charts, and Add or modify a field.

Use filters

The following table shows useful filters for queries.

Filter for

Include these query clauses

User stories or bugs

Work Item Type In User Story,Bug

Tasks or bugs

Work Item Type In Task,Bug

Items that are Active or Closed

State In Active,Closed

Items in the Requirements category

Work Item Type In Group Microsoft.RequirementCategory

Unestimated user stories

Story Points <> (leave Value field blank)

Query for count of work items

All queries return a count of matching items when you run them. The following example shows a flat-list query that filters for bugs in any state.

Screenshot that shows a query for bugs in any state with the work item count summary.

Charts also include a Values selection to show a count of work items.

Query for count of bugs per developer

Create an active bugs query, include Assigned To and State in the columns, then add a pivot chart that shows assignments and state.

Screenshot that shows the Configure chart dialog configured to pivot by Assigned To and State.

Query for count of bugs by state and area

Using the same flat-list query, include Area Path as a column and add a pivot chart grouped by state and area.

Screenshot that shows the Configure chart dialog configured to pivot by State and Area Path.

Query for undefined field values

Find work items with an undefined (blank) field value by using the "equals" operator = and leaving the Value blank. For example, this clause finds user stories whose Story Points field is blank.

Screenshot that shows a filter where the Story Points value is blank.

To find items where a field isn't blank, use the "not" operator <> and leave the value blank.

Query for effort or story points

Assign Story Points to user stories or bugs in the Agile process. Use Effort for product backlog items and bugs in the Scrum process. For more, see Basic, Agile, Scrum, or CMMI.

Query for sum of story points and their status

Create a query that filters for User Story and add Story Points and State to the columns.

Screenshot that shows the Query editor flat-list for open stories with Story Points and State columns.

Then add a stacked bar chart that sums Story Points.

Screenshot that shows the Configure chart dialog for a stacked bar chart that sums Story Points.

For information on cumulative flow diagrams, see Cumulative flow.

Query for burn up chart of user stories for an iteration

Create a query that filters for User Story in Active or Closed state and include Story Points in the columns.

Screenshot that shows the Query editor flat-list for user stories in Active or Closed state.

Then add a stacked area trend chart that sums Story Points.

Screenshot that shows the Configure chart dialog for a trend chart that sums Story Points.

Query for remaining and completed work

Depending on your process, projects can include these fields on tasks or bugs:

Process Available fields
Agile Original Estimate, Remaining Work, Completed Work
Scrum Remaining Work
CMMI Original Estimate, Remaining Work, Completed Work

Sum of remaining work per developer

If you estimate Remaining Work for tasks and bugs, get a rollup with a query that uses the In operator and includes both Task and Bug to include bugs tracked as tasks.

Screenshot that shows a query of tasks and bugs for a sprint.

Add Remaining Work as a column, save, and then add a pivot chart to show a sum of Remaining Work per developer.

Screenshot that shows the Configure chart dialog to pivot by Assigned To and sum Remaining Work.

For system-defined sprint burndown charts, see Sprint burndown.

Fields used to estimate and track work

This table describes activity-based and numeric fields you can use to track work. For date-related fields such as Start Date, Finish Date, and Target Date, see Query by date or current iteration.

Field name

Description

Work item type

Activity 1, 2

The activity required to complete a task. For capacity planning, see Capacity planning. Allowed values include:

  • Deployment
  • Design
  • Development
  • Documentation
  • Requirements
  • Testing

The Activity field maps to Activity in the ProcessConfiguration file.3

Reference name=Microsoft.VSTS.Common.Activity, Data type=String

Task, Bug4 (Agile and Scrum)

Business Value

A subjective measure of relative business value for a product backlog item or feature. Higher numbers indicate higher relative value.

Reference name=Microsoft.VSTS.Common.BusinessValue, Data type=Integer

Epic, Feature

Completed Work

The amount of work spent implementing a task. You can record work in hours or days; no inherent time units are enforced.

Reference name=Microsoft.VSTS.Scheduling.CompletedWork, Data type=Double

Task, Bug4

Discipline 1, 2

The discipline assigned to a task. See Capacity planning. Allowed values include:

  • Analysis
  • Development
  • Test
  • User Education
  • User Experience

The Discipline field maps to Activity in the ProcessConfiguration file.3

Reference name=Microsoft.VSTS.Common.Discipline, Data type=String

Task, Bug 4 (CMMI)

Effort

A subjective measure of the size of a bug or product backlog item. Use Effort to calculate team velocity and forecasting; it maps to Effort in the ProcessConfiguration file.

Reference name=Microsoft.VSTS.Scheduling.Effort, Data type=Double

Product Backlog Item, Bug 4 (Scrum)

Feature, Epic

Story Points

A subjective measure of the size of a user story. Story Points contribute to team velocity and forecasting and map to Effort in the ProcessConfiguration file.

Reference name=Microsoft.VSTS.Scheduling.StoryPoints, Data type=Double

User Story, Bug 4 (Agile)

Size

A subjective measure of requirement size. Size contributes to velocity and maps to Effort in the ProcessConfiguration file.

Reference name=Microsoft.VSTS.Scheduling.Size, Data type=Double

Requirement, Bug 4 (CMMI)

Original Estimate

The initial amount of work estimated to complete a task. Expressed in hours or days; no inherent units are enforced.

Reference name=Microsoft.VSTS.Scheduling.OriginalEstimate, Data type=Double

Task, Bug 4 (Agile and CMMI)

Remaining Work

The amount of work that remains to finish a task. Recorded in hours or days; no inherent units are enforced. Remaining Work also contributes to burn down and maps to RemainingWork in the ProcessConfiguration file.

Note

For Azure Boards, the taskboard displays "h" (hours) for Remaining Work. For on-premises XML process configuration, you can modify the ProcessConfiguration file to change the label (for example, "d" for days).

Reference name=Microsoft.VSTS.Scheduling.RemainingWork, Data type=Double

Task, Bug4

Requires Review

Indicates the task requires review. Allowed values: Yes or No (default).

Reference name=Microsoft.VSTS.CMMI.RequiresReview, Data type=String

Task (CMMI)

Requires Test

Indicates the task requires a test. Allowed values: Yes or No (default).

Reference name=Microsoft.VSTS.CMMI.RequiresTest, Data type=String

Task (CMMI)

Task Type1

Specifies the kind of task. Allowed values include:

  • Corrective Action
  • Mitigation Action
  • Planned

Reference name=Microsoft.VSTS.CMMI.TaskType, Data type=String

Task, Bug4 (CMMI process)

Note

  1. To change picklist values: for cloud services or an Inherited process, see Add and manage fields; for on-premises XML process use Add or modify a field, customize a picklist.
  2. The Capacity page shows a union of all values defined for the field across projects in the project collection. To restrict values seen on the Capacity page, make the values match across all teams that share the field assigned to type="Activity".
  3. To change the ProcessConfiguration field assignment (on-premises only), see Process configuration XML element reference.
  4. Each team configures whether bugs behave like requirements or tasks. Because bugs can appear with either, fields used to estimate effort at both levels are included on the work item form.

Next steps

Customize your work tracking experience

Related content

REST API

To programmatically interact with queries, see one of these REST API resources: