Query by numeric fields

VSTS | TFS 2018 | TFS 2017 | TFS 2015 | TFS 2013

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.

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, click on the grouped clause.

For additional 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)

Queries and charts based on counts

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

In addition, 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

Queries and charts based on 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 Agile, Scrum, or CMMI work item types and workflow topics.

Sum of story points and their status

Note

Feature availability: The Sum feature is supported on VSTS and TFS 2013.4 and later versions.

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

Note

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

Show a burnup 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

Queries and charts based on remaining and completed work

Based on the process your team 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

Note

Feature availability: The Sum feature is supported on VSTS and TFS 2013.4 and later versions.

If you follow Scrum practices and estimate Remaining Work for your tasks and bugs, you can get a roll up 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

Note

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 perform a task.To learn more about how this field is used, see Capacity planning. Allowed values are:

  • Deployment

  • Design

  • Development

  • Documentation

  • Requirements

  • Testing

Note: This field 3 is also used to calculate capacity by activity. It is assigned to type="Activity" in the ProcessConfiguration file.

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

Note: This field 3 is also used to calculate capacity by activity. It is assigned to type="Activity" in the ProcessConfiguration file.

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 is assigned to type="Effort" in the ProcessConfiguration file.

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

Product Backlog Item, Bug 4 (Scrum)

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 is assigned to type="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 is assigned to type="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 VSTS, the task board always shows "h" for hours in relationship to Remaining Work. For TFS, you can modify the ProcessConfiguration filefor 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)

Notes:

  1. To change the menu selection (on-premises TFS only), see Add or modify a field, customize a pick list.
  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 team 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 team projects for the field assigned to type="Activity".

  3. To change the ProcessConfiguration field assignment (on-premises TFS 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, VSTS and TFS provide rollup of Remaining Work for tasks on the Task board. For other rollup requirements, see the following topics:

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.

Also, you can add custom work item types to a backlog. For details, see Add or modify a work item type, Add a custom WIT to a backlog or board .