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.
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.3
Reference name=Microsoft.VSTS.Common.Activity, Data type=String
Task, Bug4 (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
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, Bug4
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.3
Reference name=Microsoft.VSTS.Common.Discipline, Data type=String
Task, Bug 4 (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 3 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 4 (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 3 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 4 (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 field3 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 4 (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 4 (Agile and CMMI)
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 3 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, Bug4
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 Type1
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, Bug4 (CMMI process)
Note
- 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.
- 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"
. - To change the ProcessConfiguration field assignment (on-premises only), see Process configuration XML element reference.
- 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
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: