Analyzing Work Item 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 Work Items and Workflow (Agile) and Test Case (Agile).

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) v5.0 include the Bug Reactivations report in Excel. For more information, see Bug Reactivations Excel Report.

Back to top

Selecting and Filtering 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 a Report in Microsoft Excel for Visual Studio ALM.

2. Right-click the chart, click Change Chart Type, click Area, and then click Stacked Area.

3. For each report filter, right-click 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 click Hide All Field Buttons on Chart.

Back to top

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 Adding and Modifying Work Item Fields to Support Reporting.

Back to top

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.

Back to top

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. Click 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, click 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 click 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.

Back to top

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 click 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. Click 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 Working with Shared Dimensions in the Analysis Services Cube.

To create reports about the status of current work items, you click 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, click Work Item in the combo box.

In this section

• Filter by Work Item Fields Under the Work Item Dimension

• 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

Back to top

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 Using System Fields and Fields Defined by the MSF Process Templates. 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 Adding and Modifying Work Item Fields to Support Reporting. For information about how to work with date hierarchies, see Working with Shared Dimensions in the Analysis Services Cube.

Back to top

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 Create and Modify Areas and Iterations.
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 Working with 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	Contains a 14-level hierarchy, as the following illustration shows, 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 Create and Modify Areas and Iterations.
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 Designing the Workflow.

Back to top

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

When you click 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. Click 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

• Filter Based on an Attribute of a Linked Work Item

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 Working with Link Types and Customizing and Managing Link Types [witadmin].

Back to top

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.

Back to top

Filter By Changesets

You click the Work Item Changeset entry in the combo box to access the Version Control Changeset and Work Item dimensions. Click 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 Find, Link, and View Changesets Associated with a Work Item.

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.

Back to top

Filter By Category

The Work Item to Category entry in the combo box contains the Work Item and Work Item Category dimensions. Click 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 Grouping Work Item Types into Categories.

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

Back to top

Filter By Hierarchical Relationships

The Work Item to Tree entry in the combo box contains the Work Item and Work Item Tree dimensions. Click 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 Working with Link Types.

Back to top

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 Analyzing Test Results Using the Test Perspective in the Analysis Services Database for Visual Studio ALM.

Back to top

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 Work Items and Workflow (Agile), Work Items and Workflow (CMMI), or Visual Studio Scrum 1.0.

• 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 Choosing Link Types to Effectively Track Your Project.

• 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 Active, Proposed, Resolved, or Closed.

• 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.

Back to top

See Also

Concepts

Current Work Item Tables

Work Item Link History Tables

Work Item Category Tables

Work Item Changeset Tables

Work Item History Tables

Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System

Change History