Analyze and report on work items and test case data using the Work Item perspective

You can analyze current or historical data for work items and test cases by using the measures, dimensions, and attributes that are defined for the Work Item perspective in the SQL Server Analysis Services cube for Visual Studio Team Foundation Server. A test case is a type of work item that is associated within its own dimension and used specifically to support Microsoft Test Manager. For more information, see Create Manual Tests using Team Web Access.

The Work Item perspective is based on the relational tables that enable reporting on work items as either a property of the work item or a linked work item. For more information, see Work Item History tables.

By using the Work Item perspective, you can create reports that answer the following questions: What was the total count of active bugs each day in the last iteration? How many scenarios were active each month during the last year? How many bugs of each priority have been active each day in the last month? How much outstanding and remaining work has a set of work items had over the last month? How much work did a particular group of developers finish? How much additional work was created after a particular date? Note If your data warehouse for Visual Studio Application Lifecycle Management (ALM) is using SQL Server Enterprise Edition, the list of cubes will include Team System and a set of perspectives. The perspectives provide a focused view of the data so that you do not have to scroll through all of the dimensions and measure groups in the whole Team System cube.

In this topic

• Example: Bug Reactivations Report

• Work Item Measures

• Date Filters and Numeric Filters

• Dimensions and Attributes in the Work Item Perspective That Support Filtering and Categorization

  • Filter by Work Item Fields

  • Filter by Link Type or Fields That Are Defined for a Linked Work Item

  • Filter By Changesets

  • Filter By Category

  • Filter By Hierarchical Relationships

  • Filter By Test Result

• Required Activities to Monitor and Track Work

Example: Bug Reactivations Report

By using PivotChart reports in Excel, you can display the number of bugs that were closed and then reactivated over time, similar to the data in the following illustration.

The process templates for Microsoft Solutions Framework (MSF) Agile and CMMI include the Bug Reactivations report in Excel. For more information, see Bug Reactivations Excel Report.

Select and filter pivot fields

You can create a bug reactivations report by performing the following steps:

1. In Excel, connect to the Analysis Services cube for Team Foundation Server, and insert a PivotChart report.

   For more information, see Create Excel reports from a work item query.

2. Open the shortcut menu for the chart, choose Change Chart Type, choose Area, and then choose Stacked Area.

3. For each report filter, open the shortcut menu for each of the following fields, specify the hierarchies, weeks, or other elements of interest, and then drag the field to the Report Filter area.

   • Team Project Hierarchy from the Team Project dimension.

   • Work Item.Iteration Hierarchy from the Work Item dimension.

   • Work Item.Area Hierarchy from the Work Item dimension.

   • Work Item.Work Item Type from the Work Item dimension. Select the Bug check box as the only type to display.

   • Year Week Date from the Date dimension.

4. In the Date dimension, expand More fields, and drag the Date, Week, or Month fields to the Axis Fields (Categories) area to specify how granular a report you want to generate.

5. Drag Work Item Count from the Work Item measure group to the Values area.

6. Drag State from the Work Item dimension to the Column Labels area, and then select the Active and Resolved check boxes.

7. In the Work Item dimension, expand More Fields, drag Previous State to the Column Labels area, and then select the Active and Resolved check boxes.

8. In the PivotTable report, collapse the Active column to show only a single column, and then rename the cell as Reactivated and Still Active.

9. In the PivotTable report, collapse the Resolved column to show only a single column.

10. (Optional) Right-click any filter that appears on the chart, and then choose Hide All Field Buttons on Chart.

Work item measures

The following table describes the measures that you can use to filter or report on the status or progress of work. With the Work Item Count measure, you can report on the total number of work items in a particular state at a particular point in time. To report activity that occurred on a particular day, you can use the State Change Count or Revision Count measures. The Work Item measure group contains additional measures when custom fields in the definitions of work item types specify Measure as the reportable attribute. For more information about how to use the reportable attribute, which is optional, and its values, see Add or modify work item fields to support reporting.

Hidden measures

To build the calculations that provide point-in-time totals, several hidden measures are used. These measures are not exposed to client tools such as Microsoft Excel, Report Builder, or Report Designer, but the measures are present in definitions in the deployed cube. Hidden measures perform a calculation by using the Multidimensional Expressions (MDX) LastChild function, which aggregates the total for the measure as of a particular date.

Date Filters and Numeric Filters

You can use Date Filters and Numeric Filters to filter a report based on one or more values in this set. These filters appear under the measure group that is labeled Values. You can use the selections within each group to filter the set of work items. You should not add them to the PivotTable report directly. Instead, you filter the rows or columns within the PivotTable report by performing the following steps:

1. Choose the filter icon for either Row Labels or Column Labels within the PivotTable report, point to Value Filters, and then specify the criteria that you want (for example, Greater Than).

2. In the first field, choose the date or numeric filter that you want (for example, Created Date).

3. In the third field, type the value that meets your filter criteria, and then choose OK.

   For example, to display all work items that were created after June 1, 2010, type 6/1/2010.

The following table describes the date filters that you can use to refine the set of work items that a report returns. You can even combine filters.

The following table describes the numeric filters that you can use to distill the set of work items that appear in a report.

Dimensions, Attributes, Filters, and Categories in the Work Item Perspective

You can combine attributes across several dimensions to filter the set of work items that appear in a report or to highlight specific details about a group of work items. These attributes correspond to all the fields for any type of work item that have the reportable attribute set to Dimension. You can filter based on field attributes by work item, test case, linked work item, or linked test case. You can also filter the set of work items based on the type or types of links between them.

When you choose Work Item in the Show fields related to combo box in Excel, you have access not only to the Test Case, Work Item, and Work Item Category dimensions but also the Date and Team Project shared dimensions. Choose Work Item when you want to analyze work items or test cases and filter by date, project collection, project, or work item category. For information about the Date and Team Project dimensions, see Shared dimensions in the Analysis Services Cube.

To create reports about the status of current work items, you choose Current Work Item in the combo box. This combination of dimensions does not contain the Date dimension. To analyze the historical data of work items in a trend report, choose Work Item in the combo box.

Filter by Work Item Fields Under the Work Item Dimension

The Work Item and Test Case dimensions contain all attributes that are specific to work items, such as State, Work Item Type, and Work Item ID. Additionally, work item fields in process templates that have the reportable attribute set to "Dimension" are reflected as attributes in the Work Item dimension.

Attributes appear under display folders in the Test Case and Work Item dimensions. Dimension attributes are organized into folders that are based on the reporting reference name that is assigned in the definition of each type of work item. The following types of mapping occur:

• Intrinsic fields, which have the "System" prefix, appear directly under the Test Case or Work Item dimension.

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

For example, work item fields appear under the Work Item dimension, as the following illustration shows. The Linked Work Item and Test Case dimensions have similar structures.

For information about each field, see Work item field reference for Visual Studio ALM. Only fields that have reportable=”Dimension” appear under the associated dimensions. Additional attributes appear when custom fields in the definitions of types of work items specify Dimension as the reportable attribute. For more information about how to use the reportable attribute, which is optional, and its values, see Add or modify work item fields to support reporting. For information about how to work with date hierarchies, see Shared dimensions in the Analysis Services Cube.

The following table describes the attributes that are not associated with a specific work item field and the hierarchical attributes that the Work Item dimension provides.

Attribute	Description
Area	Contains a 14-level hierarchy, as the following illustration shows, which correlates the measures in the cube according to the area classifications with which they are associated. You can use this parent-child hierarchy to summarize or filter measures according to their level within the area hierarchy. For more information, see Add and modify area and iteration paths.
Area Path	Flat list of the area paths for all team projects.
Changed Date	Flat list of dates that you can use to filter the set of work items based on the dates on which the team modified the items.
Changed Date Hierarchy by Month	Hierarchical tree that supports filtering and reporting on the set of work items based on the months in which the team modified the items. For example, you can use the Changed Date Hierarchy by Month or by Week, as the following illustration shows, to filter or create a trend report based on the changed date. For more information about how to work with date hierarchies, see Shared dimensions in the Analysis Services Cube.
Changed Date Hierarchy by Week	Hierarchical tree that supports filtering and reporting on the set of work items based on the week in which the team modified the items.
Created Date	Flat list of dates that you can use to filter the set of work items based on the date on which the team created the items.
Created Date Hierarchy by Month	Hierarchical tree that supports filtering and reporting on the set of work items based on the month in which the team created the items.
Created Date Hierarchy by Week	Hierarchical tree that supports filtering and reporting on the set of work items based on the week in which the team created the items.
Iteration	As the following illustration shows, contains a 14-level hierarchy that correlates the measures in the cube according to the iteration classifications with which they are associated. You can use this parent-child hierarchy to summarize or filter measures according to their levels within the iteration hierarchy. For more information, see Add and modify area and iteration paths.
Iteration Path	Flat list of the set of iteration paths that are defined for all team projects.
Previous State	The only field under More fields is Previous State, which you can use to filter a report based on the State to which a work item was assigned before it was assigned to its current state. State is an attribute of the workflow for a type of work item. For more information, see Change the workflow for a work item type.

Filter by Link Type or Fields That Are Defined for a Linked Work Item

When you choose the Linked Current Work Item entry in the Show fields related to combo box, you gain access to the Work Item, Work Item Link Type, and Work Item Linked dimensions. Choose this entry when you want to filter the set of work items in a report based on the link type or values of fields in work items that are linked to other work items.

Filter Based on an Attribute of a Link Type

By specifying one or more of the attributes in the following table, you can filter work items that have at least one link relationship whose link type meets the criteria that you specify. For more information, see Link type element reference and Manage link types [witadmin].

Filter Based on an Attribute of the Linked Work Item

The Work Item Linked dimension contains the same set of attributes as the Test Case and Work Item dimensions. You can use these attributes to find work items or filter measures based on attributes of work items that are linked to the work items that you are analyzing. By running this type of query, you can answer questions such as "How many active bugs are linked to priority 1 user stories?"

The Work Item Link Type and Work Item Linked dimensions are many-to-many dimensions that are linked to the Current Work Item fact table. Team members can link any work item to more than one other work item.

If a dimension corresponds to the attributes of links between work items, the name of the dimension is prefixed with the phrase "Work Item Linked." For example, the "Assigned To" attribute corresponds to the "Work Item Linked.Assigned To" attribute and so on for all other work item fields in the OLAP cube. For more information, see Filter by Work Item Fields Under the Work Item Dimension earlier in this topic.

Filter By Changesets

You choose the Work Item Changeset entry in the combo box to access the Version Control Changeset and Work Item dimensions. Choose this entry when you want to filter the set of work items by information in the changesets that are linked to the work items. When you make and check in changes to the code by using Team Foundation version control, you can associate the changeset with the work item that the changes address. A changeset lists the source files that you checked in for the changeset and a list of all work items that you linked to the changeset. For more information, see Check in your work to the team's codebase.

The Version Control Changeset dimension is a parent-child dimension type and linked to the Current Work Item fact table through an intermediate, many-to-many fact table. Team members can link any work item to more than one changeset. The following table describes the attributes in the Version Control Changeset dimension.

Filter By category

The Work Item to Category entry in the combo box contains the Work Item and Work Item Category dimensions. Choose this entry when you want to filter the set of work items by their associated categories. You use categories when your team projects contain types of work item that are similar but named differently. For more information, see Use categories to group work item types.

The following table describes the attributes in the Work Item Category dimension.

Filter By Hierarchical Relationships

The Work Item to Tree entry in the combo box contains the Work Item and Work Item Tree dimensions. Choose this entry when you want to filter by hierarchical nesting of work items that are linked by using the Parent and Child types of links or other custom-defined types of tree-topology links.

The Work Item Tree dimension is a parent-child dimension type and linked to the Current Work Item fact table through an intermediate, many-to-many fact table. Any work item can be linked to more than one work item and, therefore, can appear in multiple places in the tree.

As the following illustration shows, The Work Item Tree Hierarchy contains eight hierarchical filters. The first hierarchy supports filtering work items at the top of the tree, Work Item Tree 1 supports filtering work items that are nested one level deep, and so on.

The following table describes the attributes in the Work Item Tree dimension. You use link types that are based on the tree topology to create multi-level, hierarchical relationships among work items. In addition to supporting multi-level, hierarchical views, hierarchical link types support directionality and restrict circular relationships. For more information, see Link type element reference.

Filter By Test Result

The Work Item with Result entry in the combo box contains the Test Result, Version Control Changeset, Work Item, Work Item Category, and Work Item Tree dimensions. You can use the attributes in the Test Result dimension to filter reports based on the test results that are associated with test cases and their linked work items.

You can use the Work Item Count measure not only to determine how many work items are linked to a test result but also to report on work items and their associated test results. For a description of each Test Result attribute, see Analyze and report on test results using the test perspective in the Analysis Services database for Visual Studio ALM.

Required Activities to Monitor and Track Work

To create reports that contain useful data about the status, progress, and trends about work items, team members must perform the following activities:

• Create work items. Team members must create work items to manage the backlog of user stories or bugs, to track work by using tasks, and to manage test cases. Update the state of work items based on the process guidance for each type of work item.

  For more information, see Agile process template work item types and workflow, CMMI process template work item types and workflow, or Scrum process template work item types and workflow.

• Link work items to other work items or changesets. To track relationships between work items, team members must create links between, for example, user stories and tasks. Team members can create other useful links such as those between user stories and test cases, bugs and test cases, and changesets and user stories, tasks, and bugs. Team members can show relatedness, dependency, or hierarchy by specifying the appropriate type of link.

  For more information, see Link work items to support traceability.

• Specify area and iteration paths. To monitor status or trends on product areas or milestones by filtering reports, team members must set the Area and Iteration fields for each work item.

• Assign work to owners and update the work item State. As work progresses, team members must change the State of the work items that are assigned to them as the work items move from a new or proposed state, to active or in progress, to closed or done

• Update the work effort. To monitor progress that a team has made in completing work for an iteration or other interval of time, team members must update the amount of completed and remaining work for the work items that are assigned to them.

See Also

Concepts

Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

Other Resources

Table reference for the relational warehouse database for Visual Studio ALM