Progress status sample report

Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020

This article shows you how to get the execution state of one or more Test Plans in Power BI.

Important

Power BI integration and access to the OData feed of the Analytics Service are generally available for Azure DevOps Services and Azure DevOps Server 2020 and later versions. 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.

The report generated is similar to following image and the Summary chart of the Track test status - Progress report.

Sample - Overall Execution State - Report

This report displays two donut charts that summarize Test Plans executed and not executed, and the status of executed Test Plans.

  • Executed vs Not executed
    • Executed: The percentage of test plans defined which have run.
    • NotExecuted: The percentage of test plans defined which haven't run.
  • Split by outcome
    • Blocked: Percentage of test cases that are currently blocked from running.
    • Failed: Percentage of test cases that failed.
    • NotApplicable: Percentage of test cases that didn't run.
    • Passed: Percentage of test cases that passed.

Questions the report answers

The overall execution state report helps you track the team's progress with respect to planned testing of your product or service by answering the following questions:

  • How much testing is complete?
  • What is the current status of tests passing, failing, or being blocked?

Note

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

Prerequisites

  • You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
  • For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
  • To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
  • Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
  • To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.
  • You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
  • Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
  • For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
  • To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
  • Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
  • To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your*View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

For the report to generate useful data, the team must carry out the following activities to manage test plans:

  • Define test plans, test suites, and test cases. Specify their state. For a Test Suite to run, it must be in the In Progress state. For a Test Case to run, it must be in the Ready state. For details, see Create manual test cases.

  • Run manual tests and verify the results. Mark the results of each validation step in the test case as passed or failed. For details, see Run manual tests.

    Note

    Testers must mark a test step with a status if it is a validation test step. The overall result for a test reflects the status of all the test steps that were marked. Therefore, the test will have a status of failed if any test step is marked as failed or not marked.

Sample queries

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/TestPlanTitle eq '{testPlanTitle}' ))" 
        &"/aggregate(" 
            &"$count as TotalCount," 
            &"cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as Passed," 
            &"cast(LastResultOutcome eq 'Failed', Edm.Int32) with sum as Failed," 
            &"cast(LastResultOutcome eq 'Blocked', Edm.Int32) with sum as Blocked," 
            &"cast(LastResultOutcome eq 'NotApplicable', Edm.Int32) with sum as NotApplicable," 
            &"cast(LastResultOutcome eq 'None', Edm.Int32) with sum as NotExecuted," 
            &"cast(LastResultOutcome ne 'None', Edm.Int32) with sum as Executed" 
        &")/compute(" 
            &"Executed mul 100 div TotalCount as ExecPct," 
            &"iif(TotalCount gt NotExecuted, Passed mul 100 div Executed,0) as PassedPct" 
        &")", null, [Implementation="2.0"]) 
in 
    Source

Substitution strings

Each query contains the following strings that you must replace 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
  • {testPlanTitle} - Title of your test plan. Example: Fabrikam test plan.

Query breakdown

The following table describes each part of the query.

Query part

Description

filter((TestSuite/TestPlanTitle eq '{testPlanTitle}'))

Return data for only selected test plan. You can add multiple plans with a clause like filter((TestSuite/TestPlanTitle eq '{testPlanTitle1}' or TestSuite/TestPlanTitle eq '{testPlanTitle2}')). You can also apply any other filters related to test suites, test configurations here.

/aggregate($count as TotalCount,

Aggregate data across the filtered test points with having count as TotalCount.

cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as Passed

While aggregating, type-cast test points having latest execution outcome 'Passed' to 1 and sum them up as 'Passed' metric.

/compute(Executed mul 100 div TotalCount as ExecPct.

Provide a computed metric ExecPct that is equal to (Executed test points / Total count * 100).

Query filters

To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to the singular value for an EntityType.

For example, the EntitySet Name="TestPoints" corresponds to the EntityType Name="TestPoint". The OData metadata for the EntitySet Name="TestPoints" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.

<EntitySet Name="TestPoints" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.TestPoint">
  <NavigationPropertyBinding Path="ChangedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="Project" Target="Projects"/>
  <NavigationPropertyBinding Path="TestSuite" Target="TestSuites"/>
  <NavigationPropertyBinding Path="TestConfiguration" Target="TestConfigurations"/>
  <NavigationPropertyBinding Path="TestCase" Target="WorkItems"/>
  <NavigationPropertyBinding Path="Tester" Target="Users"/>
  <NavigationPropertyBinding Path="AssignedTo" Target="Users"/>
  <Annotation Term="Org.OData.Display.V1.DisplayName" String="Test Points"/>
</EntitySet>

Power BI transforms

In Power BI, do the following steps.

When finished, you may choose to rename columns.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. Change the type of count columns to Whole Number and percentage fields to Decimal Number.

    Power BI Change Column Type

  3. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  4. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Sample - Overall Execution - Fields

To create the report, do the following steps:

  1. To create donut chart of Executed v/s Not executed:
    • Create a Power BI visualization Donut chart.
    • Drag and drop Executed and NotExecuted in Values.
  2. To create donut chart of split by outcome
    • Create a Power BI visualization Donut chart.
    • Drag and drop outcome values like Passed, Failed, Blocked, NotApplicable, and so on, into Values.

Your report should appear similar to the following image.

Sample - Overall Execution State - Report

Full list of Test Plans sample reports