Requirements tracking rollup sample report

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

This report builds on the Requirements tracking report report and does a one level roll-up to provide aggregated metrics. For example, if you are tracking requirements with User Stories, you can have a one-level roll up (up to Features) with the queries provided in this section.

Important

Power BI integration and access to the OData feed of the Analytics Service are in Preview. The sample queries provided in this article are valid only against Azure DevOps Services and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.

An example is shown in the following image.

Sample - Stories Overview Rollup Report

Note

Requirement tracking is supported only for test cases linked through a Requirement-based test suite. The association between a requirement work item—User Story (Agile), Product Backlog Item (Scrum), Requirement (CMMI), or Issue (Basic)—and manual test execution is only formed when the test case is linked via a Requirement-based test suite.

This report displays the following information for each requirement that it lists:

  • Percent work completed: Progress bar that shows the percentage of completed work based on the rollup of completed hours for all tasks that are linked to the requirement.
  • Passed tests: The number of test cases run that have passed based on the most recent test run.
  • Failed tests: The number of test cases run that have failed based on the most recent test run.
  • Run tests: The number of test runs that have executed.
  • Active bugs: The number of linked bugs in an Active state.
  • Closed bugs: The number of linked bugs in a Closed, Done, or Completed state.

Questions the report answers

Requirements tracking reports are useful for answering the following types of questions.

Work progress

  • Does the amount of work that remains for each requirement correspond to your expectations?
  • Are top-ranked requirements being implemented first?
  • How many tests are defined for each requirement? How many tests are passing?
  • What requirements are being implemented that have no test cases defined for them?

Quality progress

  • How many test cases have run for each requirement, and how many have passed?
  • How many active bugs does each requirement have?
  • Are bugs being found for requirements that are being tested?
  • Are bugs being resolved or are they remaining active?

Risk assessment

  • Which requirements are at risk?
  • Which requirements aren't sufficiently stable for release?
  • Which requirements can we ship today?

Prerequisites

For the report to generate useful data, you need to have performed the following tasks:

  • You have define requirement work items and assigned them to the area and iteration paths of interest. For information about how to define area and iteration paths, see Define area paths and Define iteration paths.
  • To get the percentage of hours completion, you need to fill in the Complete Work and Remaining Work fields of tasks or bugs linked to requirements with the Child link type.
  • To get the execution status of test cases, you will have created requirement-based test suites in Test Plans corresponding to those requirements. Inline tests you add through the Kanban board satisfy this prerequisite, however requirements that you link to tests don't. To learn more, see Create test plans and test suites.
  • To get the status of bugs, you will have created and linked bugs to requirements with the Child link type.

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Sample queries

Query area and iteration paths

In order to scope your report to a particular Area and Iteration path, you can query them as described in Query your work tracking data using OData Analytics and use AreaSK and IterationSK values of interest to supply to the other queries provided in the sections provided later in this article.

Query for percentage of hours completion for requirements

Note

Change the WorkItemType based on the process you are using. The Scrum template supports Feature and the Basic template supports Epic as the roll up work item type, respectively.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
    Source = OData.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems? 
$filter=( 
    IterationSK eq {iterationSK}
    and AreaSK eq {areaSK}
    and WorkItemType eq 'Feature'
)
&$expand=Descendants( 
    $apply=filter( CompletedWork ne null or RemainingWork ne null ) 
    /aggregate( 
        iif(CompletedWork ne null, CompletedWork, 0) with sum as SumCompletedWork, 
        iif(RemainingWork ne null, RemainingWork, 0) with sum as SumRemainingWork 
    ) 
    /compute( 
        (SumCompletedWork add SumRemainingWork) as TotalWork, 
        SumCompletedWork as SumCompleted 
    ) 
    /compute( 
        iif(TotalWork gt 0,(SumCompleted div cast(TotalWork, Edm.Double) mul 100), 0) as PercCompletedWork 
    ) 
)
&$select=WorkItemId, Title", null, [Implementation="2.0"]),
    #"Expanded Descendants" = Table.ExpandTableColumn(Source, "Descendants", {"SumCompletedWork", "SumRemainingWork", "TotalWork", "SumCompleted", "PercCompletedWork"}, {"Descendants.SumCompletedWork", "Descendants.SumRemainingWork", "Descendants.TotalWork", "Descendants.SumCompleted", "Descendants.PercCompletedWork"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Descendants",{{"Descendants.SumCompletedWork", type number}, {"Descendants.SumRemainingWork", type number}, {"Descendants.TotalWork", type number}, {"Descendants.SumCompleted", type number}, {"Descendants.PercCompletedWork", type number}})
in
    #"Changed Type"

Query for test execution status of requirements

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
    Source = OData.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/TestPoints? 
	$apply=filter(
	    (TestSuite/RequirementWorkItem/IterationSK eq {iterationSK}
    and TestSuite/RequirementWorkItem/AreaSK eq {areaSK}
    and TestSuite/RequirementWorkItem/Processes/any(p:p/BacklogType eq 'RequirementBacklog')
    and TestSuite/RequirementWorkItem/Processes/all(p:p/IsBugType eq false)
	))
	/compute(iif(TestSuite/RequirementWorkItem/Parent ne null, TestSuite/RequirementWorkItem/Parent/WorkItemId, 0) as ParentWorkItemId, 
	iif(TestSuite/RequirementWorkItem/Parent ne null, TestSuite/RequirementWorkItem/Parent/Title, 'Unparented') as ParentWorkItemTitle
	)/groupby(
	    (ParentWorkItemId, ParentWorkItemTitle), 
	    aggregate(
	        $count as TotalCount, 
	        cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as PassedCount, 
	        cast(LastResultOutcome eq 'Failed', Edm.Int32) with sum as FailedCount, 
            cast(LastResultOutcome eq 'Blocked', Edm.Int32) with sum as BlockedCount,
            cast(LastResultOutcome eq 'NotApplicable', Edm.Int32) with sum as NotApplicableCount,
	        cast(LastResultOutcome eq 'None', Edm.Int32) with sum as NotRunCount, 
	        cast(LastResultOutcome ne 'None', Edm.Int32) with sum as RunCount)
)", null, [Implementation="2.0"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Source",{{"TotalCount", type number}, {"PassedCount", type number}, {"FailedCount", type number}, {"BlockedCount", type number}, {"NotApplicableCount", type number}, {"NotRunCount", type number}, {"RunCount", type number}})
in
    #"Changed Type"

Note

The TestSuite/RequirementWorkItem/... entry indicates that the work item must be linked to the test suite via a requirement-based test suites as discussed in the prerequisites.

Query for status of bugs linked to the requirements

Note

Change the WorkItemType based on the process you are using. The Scrum template supports Feature and the Basic template supports Epic as the roll up work item type, respectively.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
    Source = OData.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
    $filter=(
        IterationSK eq {iterationSK}
        and AreaSK eq {areaSK}
        and WorkItemType eq 'Feature'
    )
&$expand=Descendants(
    $apply=filter(
        WorkItemType eq 'Bug'
    )
    /groupby(
        (State),
        aggregate($count as Count)
    )
)
&$select=WorkItemId,Title", null, [Implementation="2.0"]),
    #"Expanded Descendants" = Table.ExpandTableColumn(Source, "Descendants", {"State", "Count"}, {"Descendants.State", "Descendants.Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Descendants", each [Descendants.Count] <> null and [Descendants.Count] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Descendants.State]), "Descendants.State", "Descendants.Count", List.Sum),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Active", type number}, {"Closed", type number}})
in
    #"Changed Type"

Substitution strings

Each query contains the following strings that you must substitute with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {iterationSK} - Iteration SK of the iteration path you are interested in
  • {areaSK} - Area SK of the area path you are interested in.

Query breakdown

The following table describes each part of the query.

Query part

Description

Processes/any(p:p/BacklogType eq 'RequirementBacklog')

Filter the work items in such a way that they should fall in 'requirements' category for at least one process associated with them.

Processes/all(p:p/IsBugType eq false)

Omit the bug type work items while getting requirements. In Basic process template, Issue work items are also of bug type, so for Basic process remove this clause from your query.

Power BI transforms

The transforms applied to Power BI queries are already added in query snippets.

Create the report

Power BI shows you the fields you can report on.

Note

The following example assumes that no one renamed any columns.

To create the report, perform the following steps:

  1. From the Modeling tab, choose Manage Relationships and link the three query results by WorkItemId column.
  2. Create a Power BI visualization Table.
  3. Add the columns you are interested in from the three Power BI queries.
  4. Select Sum as aggregation for additive columns like Passed tests etc.

    Power BI select Sum as aggregation

Here, Authentication scenarios is a parent feature of two User Stories.

Sample - Stories Overview Rollup Report