Adding and Modifying Work Item Fields to Support Reporting

You use work item fields to track data for a work item type, to define the filter criteria for queries, and to use in reports. Any field, except system fields, that you want to appear in a report must be defined in the definition file for the types of work items that the field will track. System fields are automatically defined for every work item type. However, they must be included in the work item form to support data entry.

To support reporting, you can add fields or change the attributes of existing fields. When you add or modify fields, you should apply systematic naming conventions to make sure that data is logically grouped into folders in the SQL Server Analysis Services cube.

In this topic

• Best Practices

• Use an Existing Field

• List Fields That Are Defined for a Team Project Collection

• Reportable Field Attributes

• Change a Reportable Attribute for a Field

• Add Fields to Support Reporting

• Verify Changes Made to Reportable Field Attributes

• Best Practices when Assigning Reporting Reference Names

• Reportable Fields That Are Defined in MSF Process Templates

Best Practices

Before you add or modify a field, review the following best practices:

• Determine whether you can use a field that is already defined in the team project collection that contains your team project. Use of an existing field supports cross-project reporting.

• Determine whether you can use a field that is already defined in another project collection in the deployment of Visual Studio Team Foundation Server. Use of an existing field supports cross-project reporting.

• You can have no more than 1,024 fields in each project collection and no more than 1,024 unique reportable fields in all project collections throughout a deployment of Team Foundation Server. Merged fields count as one reportable field.

• Institute a standard procedure and review process to add and modify fields in process templates, team projects, or project collections.

• Use systematic naming conventions when you label fields for reporting. When you assign reference names in a systematic manner across all team project collections in a deployment of Team Foundation Server, you guarantee a more consistent and usable warehouse and cube schema, and you avoid schema conflicts in the warehouse. For more information, see Resolving Schema Conflicts That Are Occurring in the Data Warehouse.

  You can assign up to four label attributes to a work item field:

  Note

  Fields that are defined in the process templates for Microsoft Solutions Framework are not assigned a reporting name or a reporting reference name. By default, the reference name and name attributes are used.

  • name. The friendly name of the field that appears in the drop-down menus of work item queries. The friendly name must be unique across all fields that are defined in a team project. Also, the friendly name may be different from the displayed label that is assigned to the field on the work item form. For more information, see Control XML Element Reference.

  • refname. The unique label that is assigned to the field that distinguishes it from all other fields that are defined in the team project collection. The value that is assigned to the refname cannot be changed.

    For requirements of and restrictions on friendly names and reference names for fields, see Naming Conventions for Work Item Tracking Objects.

  • reportingname. Optional attribute. The name that is used to identify a field in reports. When not explicitly set, the value that is assigned to the name attribute is used.

  • reportingrefname. Optional attribute. The unique label that is assigned to a reportable field that distinguishes it from all other reportable fields that are defined in all team project collections. When not explicitly set, the value that is assigned to the refname attribute is used. For recommended naming conventions, see Best Practices when Assigning Reporting Reference Names later in this topic.

    Note

    The reporting reference names are visible only from a PivotTable report or the Analysis Services cube.

Use an Existing Field

You should use a field that is already defined if that field matches the information that you want to track and report on. To use an existing field, perform the following steps:

• Identify the field that you want to use. Use the witadmin listfields command to identify the fields and their attributes that are defined for all project collections. For more information, see List Fields That Are Defined for a Team Project Collection later in this topic.

• Determine whether the field is reportable and whether the reportable attributes meet your reporting needs.

• If it is not reportable, use the witadmin changefield to change the reportable attribute for the project collections in which it is used. For more information, see Change a Reportable Attribute for a Field later in this topic.

• For the project collection where the field is not defined, add it to the XML definition files for the work item types that you want to use to track data. For more information, see Add Fields to Support Reporting later in this topic.

List Fields That Are Defined for a Team Project Collection

You can use the witadmin listfields command to list fields and their attributes. You can list a specified field or all fields that are defined in a project collection. The witadmin listfields command has the following syntax:

witadmin listfields /collection:CollectionURL /n:RefName 

For more information, see List Work Item Fields and View Attributes Assigned to Fields.

Reportable Field Attributes

Reportable fields have a reportable attribute value of Detail, Dimension, or Measure. The following attributes determine how work item fields are exported and processed to the data warehouse databases:

• reportingtype. To include a field in reports, you must assign one of the following values to the reportable attribute:

  • Assign Detail to export the field to the relational warehouse database but not to the cube. As the following example shows, use the Detail type only for Integer, Double, String, or DateTime fields:

    <FIELD refname="MyCorp.Summary" name="Summary" type="String" reportable="detail">
    

  • Assign Dimension to export the field to both the relational warehouse database and the cube. As the following example shows, use Dimension only for Integer, Double, String, or DateTime fields. This value is useful to include fields that are used to filter reports (for example, fields that have lists of valid values).

    <FIELD refname="MyCorp.Category" name="Category" type="String" reportable="dimension">
    

  • Assign Measure to support the processing of precalculated values in the cube. Use the Measure type only for Integer and Double fields.

    When you assign Measure as the reportingtype, you must assign sum as the formula, as the following example shows:

    <FIELD refname="MyCorp.Cost" name="Cost" type="Integer" reportable="measure" formula="sum">
    

• reportingrefname. You can assign a different reference name to a field that is marked as reportable. If no value is specified, the value that is assigned to the refname attribute is used.

  You can use this attribute to either merge or diverge fields that are included in reports. To merge two fields that have distinct reference names and that are defined in different project collections, you assign the same reportingrefname to both. To diverge two fields that have the same reference name but that are defined in different project collections, you assign a different reportingrefname to each field.

  You should merge fields whenever possible to minimize the number of fields in the warehouse and to keep under the maximum limit of 1024 reportable fields. You can generate cross-group reports with merged fields.

• reportingname. You can assign a different label to a field that is used to display data in reports. If no value is specified, the friendly name that is assigned for the name attribute is used. The value that is assigned to the reportingname appears in the cube. The value that is assigned to the reportingrefname does not appear.

  Important

  You should use best practices to label reporting fields so that they are grouped together in the PivotTable reports. For more information, see Best Practices when Assigning Reporting Reference Names.

Change a Reportable Attribute for a Work Item Field

You can make an existing field reportable by changing the attribute assignments of the field that are defined for a project collection. An existing field is defined in one or more work item type definitions. Also, you can change all attributes that determine how a field is processed in the data warehouses.

You can use the following sequence of steps to change the attribute assignment of a field:

1. You can use the witadmin changefield command to change an attribute assignment to a field. You exercise this command for a team project collection. Use the following syntax:

   witadmin changefield /collection:CollectionURL /n:RefName [/name:NewName] [/syncnamechanges:true | false] [/reportingname:ReportingName] [/reportingrefname:ReportingRefName] [/reportingtype:Type] [/reportingformula:Formula] [/noprompt]
   

   To make an existing field reportable, change the reportingtype. For example, to make the AW.Common.TeamPriority field available for filtering of reports, assign the Dimension value to it:

   witadmin changefield /collection:http://AdventureWorksServer:8080/AWTeam/Collection1 /n:AW.Common.TeamPriority /reportingtype:dimension 
   

   For more information, see Managing Work Item Fields [witadmin].

2. (Optional) If you have more than one project collection, you may want to make similar changes to the work item field that is defined in that collection. To avoid schema conflicts when you export and process data to the data warehouse databases, you must assign the same values to these attributes across all collections:

   • Field type (the value for this field cannot be changed for an existing field).

   • Reporting type.

   • Reporting name.

   For more information, see Resolving Schema Conflicts That Are Occurring in the Data Warehouse.

3. After you have made all changes to the work item fields that you want to use for reporting, you must process the data warehouse databases. You can use the ProcessWarehouse and ProcessAnalysis Web services, which are available through the WarehouseControlWebService.

   This step makes sure that people who use reports do not see an error when you change the field attributes. For more information, see Manually Process the Data Warehouse and Analysis Services Cube for Team Foundation Server.

   For more information, see Managing Work Item Fields [witadmin].

Add Fields to Support Reporting

You can add fields to the definition of a work item type or types. When you add the field, you should add the same field element definition to all types of work items for which the field will support reporting. If you want the field to support cross-project reporting, the field should be added to all work item types in all team projects that will be reported on.

For more information, see the following topics:

• Working with Work Item Fields

• Add a Field to or Remove a Field from a Work Item Type

• FIELD (Definition) Element

Verify Changes Made to Reportable Field Attributes

You can verify the changes that you made to reportable field attributes by processing the data warehouses on demand and then checking the reports to verify that they are updated. Or you can wait until the warehouse adapter jobs run. By default, the relational database is processed every few minutes. However, the cube is processed every two hours by default.

Note

For more information about the WarehouseControlWebService, see Manually Process the Data Warehouse and Analysis Services Cube for Team Foundation Server.

1. Process the relational data warehouse on demand by using the ProcessWarehouse WarehouseControlWebService.

2. Process the cube on demand by using the ProcessAnalysisDatabase WarehouseControlWebService.

3. Verify that the reports are being updated. View a report through the dashboard or Report Manager. For more information, see Dashboards (Agile) or Reports (Agile).

Best Practices when Assigning Reporting Reference Names

For reporting reference names, you want to assign labels so that you can easily find the fields in the PivotTable report and the cube. You can achieve this by applying systematic naming conventions so that fields are grouped in a logical sequence. In addition, if the fields are not grouped in a useful manner, you can change the reporting reference name of a field.

Applying a systematic naming convention becomes increasingly important because all reportable data from all team projects that are defined in all project collections is written to a single relational data warehouse. Data from that warehouse is then processed and written to the cube. Because work item fields are managed distinctly for each project collection, different labels may be applied and may lead to a set of fields that is not well organized to support authoring reports.

Work item fields that have a reportable type of dimension correspond to dimension attributes in the cube. Dimension attributes are organized into folders that are based on the reporting reference name that is assigned in the process template or work item type definition. The following types of mapping occur:

• Fields that have the "System" prefix are intrinsic and listed directly under the Work Item dimension, with "Work Item" prepended.

• Other fields are put under folders whose names correspond to the prefixes in their reference names. For example, fields that have the "Microsoft.VSTS.Common" prefix are listed under the folder that is labeled "Microsoft VSTS Common."

As the following illustration shows, a folder is added for each prefix group of fields that share a common prefix:

The following table lists the fields whose reference names begin with "System" and that are listed in the PivotTable report with the prefix of "Work Item." These fields are put directly under the Work Item dimension. All other fields are put under folders whose names correspond to the prefixes in their reference names.

Note

Deployments that do not use the Enterprise version of SQL Server Analysis Services do not have access to the translation features that are provided by that version. In these deployments, fields are identified by their full reference name in the cube, with ‘.’ replaced by ‘_’ (for example, "System_Id" and "System_Title").

Name in PivotTable report and cube	Reference name	Data type
Work Item.Area Path	System.AreaPath	TreeType
Work Item.Assigned To	System.AssignedTo	String
Work Item.Changed By	System.ChangedBy	String
Work Item.Changed Date	System.ChangedDate	DateTime
Work Item.Created By	System.Created By	String
Work Item.Created Date	System.CreatedDate	DateTime
Work Item.ID	System.Id	Integer
Work Item.Iteration Path	System.IterationPath	TreeType
Work Item.Previous State	System.PreviousState	String
Work Item.Reason	System.Reason	String
Work Item.Rev	System.Rev	Integer
Work Item.State	System.State	String
Work Item.Title	System.Title	String
Work Item.Work Item Type	System.WorkItemType	String

The following table lists the fields that appear in the PivotTable report in the folder that is labeled "Microsoft.VSTS.Common" under the Work Item dimension. These fields have reference names that start with "Microsoft.VSTS.Common."

Name in PivotTable report and cube	Reference name	Data type
Work Item.Activated By	Microsoft.VSTS.Common.ActivatedBy	String
Work Item.Activated Date	Microsoft.VSTS.Common.ActivatedDate	DateTime
Work Item.Closed By	Microsoft.VSTS.Common.ClosedBy	String
Work Item.Closed Date	Microsoft.VSTS.Common.ClosedDate	DateTime
Work Item.Created By	Microsoft.VSTS.Common.CreatedBy	String
Work Item.Created Date	Microsoft.VSTS.Common.CreatedDate	DateTime
Work Item.Resolved By	Microsoft.VSTS.Common.ResolvedBy	String
Work Item.Resolved Date	Microsoft.VSTS.Common.ResolvedDate	DateTime
Work Item.Resolved Reason	Microsoft.VSTS.Common.ResolvedReason	String
Work Item.Priority	Microsoft.VSTS.Common.Priority	Integer
Work Item.Severity	Microsoft.VSTS.Common.Severity	String
Work Item.Stack Rank	Microsoft.VSTS.Common.StackRank	Double

Reportable Fields That Are Defined in MSF Process Templates

The following tables list the fields that are defined in the Microsoft Solutions Framework (MSF) process templates and the default assignments of the fields. These fields appear only for team projects that were created with version 5.0 of an MSF process template. Only those fields that are set to be reportable are listed.

• Detail Fields

• Dimension Fields

• Measure Fields

For a complete list of fields that are defined in the MSF process templates, see Using System Fields and Fields Defined by the MSF Process Templates. If you upgraded a team project, you may need to perform additional tasks before you can work with some of these fields. For more information, see Updating an Upgraded Team Project to Access New Features.

Detail Fields

Field name	Description	Reference name	Data type
Automation Status	The status of a test case. You can specify the following values: Not Automated Planned	Microsoft.VSTS.TCM.AutomationStatus	String

Dimension Fields

Field name	Description	Reference name	Data type
ID	The unique identifier that is assigned to a work item. Work item IDs are unique across all team projects and work items that are defined in a team project collection.	System.Id	Integer
Title	A short description that summarizes what the work item is and helps users to distinguish it from other work items in a list.	System.Title	String
Team Project	The team project to which this work item belongs.	System.TeamProject	String
Work Item Type	The name of the work item type.	System.WorkItemType	String
Area	Groups the work items into product feature or team areas. The area must be a valid node in the project hierarchy.	System.AreaPath	TreePath
Iteration	Groups the work items by named sprints or time periods. The iteration must be a valid node in the project hierarchy.	System.IterationPath	TreePath
Changed By	The name of the team member who modified the work item most recently.	System.ChangedBy	String
Activity	The type of activity that is required to perform a task.	Microsoft.VSTS.Common.Activity	String
Rev	A number that is assigned to the historical revision of a work item.	System.Rev	Integer
Due Date	The forecast due date by which a task will be completed.	Microsoft.VSTS.Scheduling.DueDate	DateTime
Finish Date	The date and time when the schedule indicates that the task will be completed.	Microsoft.VSTS.Scheduling.FinishDate	DateTime
Start Date	The date and time when the schedule indicates that the task will start.	Microsoft.VSTS.Scheduling.StartDate	DateTime
Found In	The product build number, also known as a revision, in which a bug was found.	Microsoft.VSTS.Build.FoundIn	String
Integration Build	Product build number that incorporates the code or fixes a bug.	Microsoft.VSTS.Build.IntegrationBuild	String
Assigned To	The name of the team member who currently owns the work item.	System.AssignedTo	String
Reason	The reason that the work item is in the current state. Values are specific to both the state and the type of work item. The field is not tracked for test cases or shared steps.	System.Reason	String
State	The current state of the work item. The valid values for state are specific to each type of work item.	System.State	String
Activated By	Name of the team member who activated or reactivated the work item.	Microsoft.VSTS.Common.ActivatedBy	String
Activated Date	Date and time when the work item was activated or reactivated.	Microsoft.VSTS.Common.ActivatedDate	DateTime
Closed By	Name of the team member who closed the work item.	Microsoft.VSTS.Common.ClosedBy	String
Closed Date	Date and time when a work item was closed.	Microsoft.VSTS.Common.ClosedDate	DateTime
Created By	Name of the team member who created the work item.	Microsoft.VSTS.Common.CreatedBy	String
Created Date	Date and time when a work item was created.	Microsoft.VSTS.Common.CreatedDate	DateTime
Resolved By	Name of the team member who resolved the bug or user story.	Microsoft.VSTS.Common.ResolvedBy	String
Resolved Date	Date and time when the bug or user story was resolved.	Microsoft.VSTS.Common.ResolvedDate	DateTime
Resolved Reason	The reason that the bug was resolved (for example, it was fixed).	Microsoft.VSTS.Common.ResolvedReason	String
Priority	A subjective rating of the bug, issue, task, or test case as it relates to the business. You can specify the following values: 1:  Product cannot ship without the successful resolution of the work item, and it should be addressed as soon as possible. 2:  Product cannot ship without the successful resolution of the work item, but it does not have to be addressed immediately. 3:  Resolution of the work item is optional, based on resources, time, and risk.	Microsoft.VSTS.Common.Priority	Integer
Rank	A subjective rating of the user story, task, issue, or bug compared to other work items of the same type. An item that is assigned a lower number should be fixed before an item that is assigned a higher number.	Microsoft.VSTS.Common.Rank	Double
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.	Microsoft.VSTS.StoryPoints	Double
Risk	A subjective rating of the relative uncertainty about the successful completion of the user story. You can specify the following values: 1 - High 2 - Medium 3 - Low	Microsoft.VSTS.Common.Risk	String
Severity	A subjective rating of the effect of a bug on the project. You can specify the following values: 1 - Critical 2 - High 3 - Medium 4 - Low	Microsoft.VSTS.Common.Severity	String
Due Date	The forecast date by which an issue will be completed. This field is applicable only to issue work items.	Microsoft.VSTS.Scheduling.DueDate	DateTime

Measure Fields

Field name	Description	Reference name	Data type
Original Estimate	The number of hours that are required to complete a task.	Microsoft.VSTS.Scheduling.OriginalEstimate	Double
Remaining	The number of hours that remain to finish a task.	Microsoft.VSTS.Scheduling.RemainingWork	Double
Completed	The number of hours that were spent working on a task.	Microsoft.VSTS.Scheduling.CompletedWork	Double

See Also

Tasks

Manually Process the Data Warehouse and Analysis Services Cube for Team Foundation Server

Reference

Managing Work Item Fields [witadmin]

Concepts

Resolving Schema Conflicts That Are Occurring in the Data Warehouse

Working with Work Item Fields

Creating, Customizing, and Managing Reports for Visual Studio ALM

Other Resources

Defining Work Item Fields