query-numeric.md

title	titleSuffix	description	ms.custom	ms.service	ms.assetid	ms.author	author	ms.topic	monikerRange	ms.date
Query by numeric fields based on effort, schedules, and story points	Azure Boards	Track work by creating queries based on effort, story points, schedules, or time tracking fields in Azure Boards and Azure DevOps.	boards-queries, engagement-fy23	azure-devops-boards	78fe418f-fbd8-4ae2-97d7-c754c14dd3cd	chcomley	chcomley	example-scenario	<= azure-devops	05/30/2024

Query by numeric fields

[!INCLUDE version-lt-eq-azure-devops]

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

[!INCLUDE prerequisites-queries]

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.

:::row::: :::column span="1"::: Filter for :::column-end::: :::column span="1"::: Include these query clauses :::column-end::: :::row-end::: :::row::: :::column span="1"::: User stories or bugs
:::column-end::: :::column span="1":::

Work Item Type In User Story,Bug
:::column-end::: :::row-end::: :::row::: :::column span="1"::: Tasks or bugs :::column-end::: :::column span="1":::

Work Item Type In Task,Bug
:::column-end::: :::row-end::: :::row::: :::column span="1":::

Items that are Active or Closed :::column-end::: :::column span="1":::

State In Active,Closed
:::column-end::: :::row-end::: :::row::: :::column span="1":::

Items in the Requirements category :::column-end::: :::column span="1":::

Work Item Type In Group Microsoft.RequirementCategory
:::column-end::: :::row-end::: :::row::: :::column span="1":::

Unestimated user stories :::column-end::: :::column span="1":::

Story Points <> (leave Value field blank)
:::column-end::: :::row-end:::

---

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.

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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.

[!div class="mx-imgBorder"]

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.

:::row::: :::column span="1"::: Field name :::column-end::: :::column span="2"::: Description :::column-end::: :::column span="1"::: Work item type :::column-end::: :::row-end:::

:::row::: :::column span="1"::: Activity ^{1, 2}

:::column-end::: :::column span="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

:::column-end::: :::column span="1"::: Task, Bug⁴ (Agile and Scrum) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Business Value

:::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: Epic, Feature :::column-end::: :::row-end::: :::row::: :::column span="1"::: Completed Work

:::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: Task, Bug⁴ :::column-end::: :::row-end::: :::row::: :::column span="1"::: Discipline ^{1, 2}

:::column-end::: :::column span="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

:::column-end::: :::column span="1"::: Task, Bug ⁴ (CMMI) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Effort :::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: Product Backlog Item, Bug ⁴ (Scrum)

Feature, Epic :::column-end::: :::row-end::: :::row::: :::column span="1"::: Story Points

:::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: User Story, Bug ⁴ (Agile) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Size :::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: Requirement, Bug ⁴ (CMMI) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Original Estimate :::column-end::: :::column span="2"::: 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

:::column-end::: :::column span="1"::: Task, Bug ⁴ (Agile and CMMI) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Remaining Work :::column-end::: :::column span="2"::: 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 :::column-end::: :::column span="1"::: Task, Bug⁴ :::column-end::: :::row-end::: :::row::: :::column span="1"::: Requires Review :::column-end::: :::column span="2"::: Indicates the task requires review. You can specify Yes or No (default).

Reference name=Microsoft.VSTS.CMMI.RequiresReview, Data type=String :::column-end::: :::column span="1"::: Task (CMMI) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Requires Test :::column-end::: :::column span="2"::: Indicates the task requires a test. You can specify Yes or No (default).

Reference name=Microsoft.VSTS.CMMI.RequiresTest, Data type=String :::column-end::: :::column span="1"::: Task (CMMI) :::column-end::: :::row-end::: :::row::: :::column span="1"::: Task Type¹ :::column-end::: :::column span="2"::: Specifies the kind of task to implement. Allowed values are:

• Corrective Action
• Mitigation Action
• Planned

Reference name=Microsoft.VSTS.CMMI.TaskType, Data type=String :::column-end::: :::column span="1"::: Task, Bug⁴ (CMMI process) :::column-end::: :::row-end:::

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

[!div class="nextstepaction"] 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

[!INCLUDE temp]