Query by numeric fields

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Learn how to query by numeric fields based on effort, schedule, story points, or time tracking fields in Azure Boards and Azure DevOps.

The most common numeric fields track effort for items in the Requirements category or estimated, remaining, and completed work for items in the Task category. With queries you can list the work items of interest, and then define a chart that shows either a count of work items or a sum of a numeric field.

Prerequisites

• Access levels:
  • To view and run shared queries, be a project member.
  • To add and save a shared query, have at least Basic access.
• Permissions: Have your 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.

Use operators and macros

Supported query clauses that specify a numeric field can use the following operators.

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

Develop chart-based queries

The following tips can help you develop your queries:

• Add charts for flat-list queries only.
• Reference either query filters or fields displayed through column options.
• Save changes you make to your query before you add or modify a chart.
• Group one or more clauses by selecting them and then the group clauses icon. To ungroup, select on the grouped clause.

For more information, see Use the query editor to list and manage queries and 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 show a count of items when you run the query. Here we define a flat-list query that filters for bugs in any state.

Also, all charts contain a Values selection designed to display a count of work items within the chart.

Query for count of bugs per developer

To show Assigned To and State, create an active bugs query and modify the column options. Then, add a pivot chart that displays the assignments and state.

Query for count of bugs by state and area

Using the same flat-list query that filters for bugs shown in the previous section, you can show a count based on area. Modify the column options to show the Area Path. Then, add a pivot chart that displays the state and area path.

Query for undefined field values

You can find work items that have an undefined field value by using the equals operator, =, and leaving the Value for the field blank. For example, the following filters list all work items of type User Stories whose Story Points field is blank.

To list work items based on a field that isn't blank, use the not operator, <>, and leave the Value blank.

Query for effort or story points

You can assign Story Points to user stories or bugs when you work in an Agile process. Or, Effort to product backlog items and bugs when you work in a Scrum process. For more information, see Basic, Agile, Scrum, or CMMI work item types and workflow articles.

Query for sum of story points and their status

Create a query that filters for User Story as the work item type and modify the column options to show Story Points and State.

Then, add a stacked bar chart that sums the Story Points.

For more information on system-defined 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 as the work item type and in the Active or Closed state. To show Story Points, modify the column options.

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

Query for remaining and completed work

Based on the process your project references, you can assign the following fields to 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 follow Scrum practices and estimate Remaining Work for your tasks and bugs, you can get a rollup of remaining work with the following query and chart. Use the In operator and include both Task and Bug to include any bugs that are tracked as tasks.

Add Remaining Work as a column option to the query and save. To view a sum of the remaining work, add a pivot chart as shown in the following example.

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

Fields used to estimate and track work

The following table describes the activity-based and numeric fields that you can use to track work. For information on 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 type of activity that is required to complete a task. For more information about how this field is used, see Capacity planning. Allowed values are:

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

The Activity field is assigned to Activity in the ProcessConfiguration file.³

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

Task, Bug⁴ (Agile and Scrum)

Business Value

A subjective unit of measure that captures the relative business value of a product backlog item or feature compared to other items of the same type. An item that is assigned a higher number should be considered as having more business value than an item that is assigned a lower number.

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

Epic, Feature

Completed Work

The amount of work spent implementing a task. You can specify work in hours or in days. There are no inherent time units associated with this field.

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

Task, Bug⁴

Discipline ^{1, 2}

The type of activity or discipline that is assigned to a task. To learn more about how this field is used, see Capacity planning. Allowed values are:

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

The Discipline field is assigned to Activity in the ProcessConfiguration file.³

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

Task, Bug ⁴ (CMMI)

Effort

A subjective unit of measure that captures the size of a bug or product backlog item. If you assign more effort to an item, you indicate that more work is required to implement it.

This field ³ is also used to calculate team velocity and forecasting. It gets assigned to Effort in the ProcessConfiguration file.

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

Product Backlog Item, Bug ⁴ (Scrum)

Feature, Epic

Story Points

A subjective unit of measure that captures the size of a user story. If you assign more points to a user story, you indicate that more work is required to implement it.

This field ³ is also used to calculate team velocity and forecasting. It gets assigned to Effort in the ProcessConfiguration file.

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

User Story, Bug ⁴ (Agile)

Size

A subjective unit of measure that captures the size of a requirement. The larger the size, the more work is required to implement it.

This field³ is also used to calculate team velocity and forecasting. It gets assigned to Effort in the ProcessConfiguration file.

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

Requirement, Bug ⁴ (CMMI)

Original Estimate

The amount of work required to complete a task. You can specify work in hours or in days. There are no inherent time units associated with this field.

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

Task, Bug ⁴ (Agile and CMMI)

Remaining Work

The amount of work that remains to finish a task. You can specify work in hours or in days. There are no inherent time units associated with this field. This field ³ is also used to calculate the burn down. It gets assigned to type="RemainingWork" in the ProcessConfiguration file.

Note

For Azure Boards, the taskboard always shows "h" for hours in relationship to Remaining Work. For TFS, you can modify the ProcessConfiguration file for the Remaining Work type field to specify "d" for days, or other preferred label.

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

Task, Bug⁴

Requires Review

Indicates the task requires review. You can specify Yes or No (default).

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

Task (CMMI)

Requires Test

Indicates the task requires a test. You can specify Yes or No (default).

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

Task (CMMI)

Task Type¹

Specifies the kind of task to implement. Allowed values are:

• Corrective Action
• Mitigation Action
• Planned

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

Task, Bug⁴ (CMMI process)

Note

1. To change the menu selection: for cloud services or an Inherited process, see Add and manage fields; and for On-premises XML process, see Add or modify a field, customize a picklist.
2. The values displayed in the Capacity page for Activity (Agile or Scrum) or Discipline (CMMI) reflect a union of all values defined for the field in all projects within the project collection instance. Therefore, to restrict the values that appear for Capacity on the sprint backlog pages, you must make the values match in all the projects for 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 can configure their Agile tools to determine if bugs are treated similar to requirements or tasks. Since bugs can appear either with requirements or tasks, fields used to estimate effort at the requirement-level and the task-level are included in the work item form.

Next steps

Customize your work tracking experience

Related articles

• Create your backlog
• Plan sprints
• Plan capacity
• Define a work item query
• Query fields, operators, and macros
• Support rollup of work and other fields
• Create rollup charts with Power BI

REST API

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

• Azure DevOps Services REST API Reference
• Queries
• Work item query language
• Fetch work items with queries programmatically