Jaa

Query by numeric fields in Azure Boards and Azure DevOps

  • Artikkeli

TFS 2018

How do I determine how much work each developer has completed on my team? Is there a way to sum up the effort or story points for an iteration?

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.

Supported operators and macros

Query clauses that specify a numeric field can use the operators listed below.

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

Tips for developing chart-based queries

  • You can only add charts for flat-list queries
  • Chart options reference either query filters or fields displayed through column options
  • Save changes you make to your query prior to adding or modifying a chart.
  • To group one or more clauses, select them and then choose the Group Query Clause icon group clauses icon. To ungroup, select on the grouped clause.

For more details on creating queries and chart-based queries, see Use the query editor to list and manage queries and Charts. If you want to add a custom field to track and generate sums of other numeric values, see Add or modify a field.

Useful filters

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)

Work item count queries and charts

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.

Query bugs any state, count of work items summary

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

Count of bugs per developer

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

Configure chart, count of bugs by developer, area, pivot chart

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.

Configure chart, count of bugs by area, state, pivot chart

Undefined field value queries

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.

Filter based on blank entries

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

Effort or story point queries and charts

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.

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.

Query editor, flat list, open stories

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

Configure chart, sum of story points per iteration, stacked bar chart.

For information on system-defined cumulative flow diagrams, see Cumulative flow.

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. Modify the column options to show Story Points.

Query editor, flat list, user stories - active or closed

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

Configure chart, sum of story points per iteration, stacked bar chart

Remaining and completed work queries and charts

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 the amount of work remaining for each developer with the following query and chart. By using the In operator and including both Task and Bug, you include any bugs that are being tracked as tasks.

Query of tasks and bugs for sprint

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.

Configure chart, sum of remaining work by developer, area, pivot chart

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.To learn more 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

Completed Work

The amount of work that has been 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's 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's 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's 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)

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 3 is also used to calculate burn down. It is 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

  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.

For information on adding custom fields, see Customize your work tracking experience.

The main tools you use to plan and track work are described here:

For more information on using work items and queries, see:

Rollup numeric values across work item types

Rollup provides summed values of select fields for all child work items of a parent. Natively, Azure Boards provides rollup of Remaining Work for tasks on the taskboard. For other rollup requirements, see the following articles:

What items appear in the Requirement or Task categories?

The default assignments of work item types to each category are listed below for each process.

Process Requirement category Task category
Agile User Story Task
Scrum Product Backlog Item Task
CMMI Requirement Task

However, each team can determine if the Bug work item type appears in either the Requirement or Task category. See Show bugs on backlogs and boards.

You can add custom work item types to a backlog. For more information, see Add or modify a work item type, Add a custom WIT to a backlog or board.

REST API

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