Test summary sample report

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

When you execute a pipeline run and include test tasks within the pipeline definition, you can create a report that indicates the number of test runs for different test outcomes: Passed, Failed, Not executed, Not impacted. For information on adding tests to a pipeline, see the Test task resources section later in this article.

The following image shows an example of a test summary report.

Use the queries provided in this article to generate the following reports:

• Test summary for build workflow
• Test summary for release workflow
• Test summary for a particular branch
• Test summary for a particular test file
• Test summary for a particular test owner.

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 Server 2020 and later versions, and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.

Prerequisites

• To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
• To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Note

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

Sample queries

You can use the following queries of the TestResultsDaily entity set to create different but similar pipeline test summary reports. The TestResultsDaily entity set provides a daily snapshot aggregate of TestResult executions, grouped by Test.

Note

To determine available properties for filter or report purposes, see Metadata reference for Test Plans Analytics and Metadata reference for Azure Pipelines. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.

Test summary for Build workflow

Use the following queries to view the test summary of a pipeline for a Build workflow.

Power BI query

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/mseng/AzureDevOps/_odata/v4.0-preview/TestResultsDaily?
       $apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And DateSK ge {startdate} "
                &"And Workflow eq 'Build' "
        &") "
            &"/aggregate( "
                &"ResultCount with sum as ResultCount, "
                &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
                &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
                &"ResultNotImpactedCount with sum as ResultNotImpactedCount "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
	Pipeline/PipelineName eq '{pipelineName}'
	And DateSK ge {startdate}
	And Workflow eq 'Build'
	)
	/aggregate(
		ResultCount with sum as ResultCount,
		ResultPassCount with sum as ResultPassCount,
		ResultFailCount with sum as ResultFailCount,
		ResultNotExecutedCount with sum as ResultNotExecutedCount,
		ResultNotImpactedCount with sum as ResultNotImpactedCount
	)

Test summary for Release workflow

Use the following queries to view the test summary of a pipeline for a Release workflow.

Note

To determine available properties for filter or report purposes, see Metadata reference for Test Plans Analytics. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.

Power BI query

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/mseng/AzureDevOps/_odata/v4.0-preview/TestResultsDaily?
       $apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And DateSK ge {startdate} "
                &"And Workflow eq 'Release' "
        &") "
            &"/aggregate( "
                &"ResultCount with sum as ResultCount, "
                &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
                &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
                &"ResultNotImpactedCount with sum as ResultNotImpactedCount "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
	Pipeline/PipelineName eq '{pipelineName}'
	And DateSK ge {startdate}
	And Workflow eq 'Release'
	)
	/aggregate(
		ResultCount with sum as ResultCount,
		ResultPassCount with sum as ResultPassCount,
		ResultFailCount with sum as ResultFailCount,
		ResultNotExecutedCount with sum as ResultNotExecutedCount,
		ResultNotImpactedCount with sum as ResultNotImpactedCount
	)

Test summary filtered by branch

To view the test summary of a pipeline for a particular branch, use the following queries. To create the report, carry out the following extra steps along with what is specified later in this article.

• Expand Branch into Branch.BranchName
• Select Power BI Visualization Slicer and add the field Branch.BranchName to the slicer's Field
• Select the branch name from the slicer for which you need to see the outcome summary.

To learn more about using slicers, see Slicers in Power BI.

Power BI query

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/mseng/AzureDevOps/_odata/v4.0-preview/TestResultsDaily?
       $apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And DateSK ge {startdate} "
                &"And Workflow eq 'Build' "
        &") "
            &"/groupby( "
                &"(Branch/BranchName), "
            &"/aggregate( "
                &"ResultCount with sum as ResultCount, "
                &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
                &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
                &"ResultNotImpactedCount with sum as ResultNotImpactedCount "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
	Pipeline/PipelineName eq '{pipelineName}'
	And DateSK ge {startdate}
	And Workflow eq 'Build'
	)
	/groupby(
		(Branch/BranchName),
		/aggregate(
		ResultCount with sum as ResultCount,
		ResultPassCount with sum as ResultPassCount,
		ResultFailCount with sum as ResultFailCount,
		ResultNotExecutedCount with sum as ResultNotExecutedCount,
		ResultNotImpactedCount with sum as ResultNotImpactedCount
	))

Test summary filtered by test file

To view the test summary of a pipeline for a particular test file, use the following queries. To create the report, carry out the following extra steps along with what is defined later in this article.

• Expand Test into Test.ContainerName
• Select Power BI Visualization Slicer and add the field Test.ContainerName to the slicer's Field
• Select the container name from the slicer for which you need to see the outcome summary.

Power BI query

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/mseng/AzureDevOps/_odata/v4.0-preview/TestResultsDaily?
       $apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And DateSK ge {startdate} "
                &"And Workflow eq 'Build' "
        &") "
        &"/groupby( "
            &"(Test/ContainerName), "
            &"/aggregate( "
                &"ResultCount with sum as ResultCount, "
                &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
                &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
                &"ResultNotImpactedCount with sum as ResultNotImpactedCount "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
	Pipeline/PipelineName eq '{pipelineName}'
	And DateSK ge {startdate}
	And Workflow eq 'Build')
	/groupby(
		(Test/ContainerName),
		/aggregate(
		ResultCount with sum as ResultCount,
		ResultPassCount with sum as ResultPassCount,
		ResultFailCount with sum as ResultFailCount,
		ResultNotExecutedCount with sum as ResultNotExecutedCount,
		ResultNotImpactedCount with sum as ResultNotImpactedCount
	))

Test summary filtered by test owner

To view the test summary of a pipeline for tests owned by a particular test owner, use the following queries. To create the report, carry out the following extra steps along with what is defined later in this article.

• Expand Test into Test.TestOwner
• Select Power BI Visualization Slicer and add the field Test.TestOwner to the slicer's Field
• Select the test owner from the slicer for which you need to see the outcome summary.

Power BI query

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/mseng/AzureDevOps/_odata/v4.0-preview/TestResultsDaily?
       $apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And DateSK ge {startdate} "
                &"And Workflow eq 'Build' "
        &") "
        &"/groupby( "
            &"(Test/TestOwner), "
            &"/aggregate( "
                &"ResultCount with sum as ResultCount, "
                &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
                &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
                &"ResultNotImpactedCount with sum as ResultNotImpactedCount "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
	Pipeline/PipelineName eq '{pipelineName}'
	And DateSK ge {startdate}
	And Workflow eq 'Build')
	/groupby(
		(Test/TestOwner), 
		/aggregate(
		ResultCount with sum as ResultCount,
		ResultPassCount with sum as ResultPassCount,
		ResultFailCount with sum as ResultFailCount,
		ResultNotExecutedCount with sum as ResultNotExecutedCount,
		ResultNotImpactedCount with sum as ResultNotImpactedCount
))

Substitution strings and query breakdown

Substitute the following strings 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
• {pipelinename} - Your pipeline name. Example: Fabrikam hourly build pipeline
• {startdate} - The date to start your report. You can enter the dates in YYYYMMDD format. For example, 20220815 for 15 August 2022.

Query breakdown

The following table describes each part of the query.

Query part

Description

$apply=filter(

Start filter() clause.

Pipeline/PipelineName eq '{pipelineName}'

Return test runs for the specified pipeline.

and CompletedOn/Date ge {startdate}

Return test runs on or after the specified date.

and Workflow eq 'Build' or and Workflow eq 'Release'

Return test runs only for pipelines designated with the Build or Release workflow.

)

Close filter() clause.

aggregate(

Start the aggregate clause for all the test runs matching the filter criteria.

ResultCount with sum as ResultCount,

Count the total number of test runs as ResultCount.

ResultPassCount with sum as ResultPassCount,

Count the total number of passed test runs as ResultPassCount.

ResultFailCount with sum as ResultFailCount

Count the total number of failed test runs as ResultFailCount.

ResultNotExecutedCount with sum as ResultNotExecutedCount,

Count the total number of not executed test runs as ResultNotExecutedCount.

ResultNotImpactedCount with sum as ResultNotImpactedCount

Count the total number of not affected test runs as ResultNotImpactedCount.

)

Close the aggregate() clause.

(Optional) Rename query

You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.

(Optional) Rename column fields

You can rename column fields. For example, you can rename the column Pipeline.PipelineName to Pipeline Name, or TotalCount to Total Count. To learn how, see Rename column fields.

Close the query and apply your changes

Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.

Create the Donut chart report

1. In Power BI, under Visualizations, choose the Donut report.

   

2. Add the following fields to Values, in the order indicated. Right-click each field and ensure Sum is selected.

   • ResultPassCount
   • ResultFailCount
   • ResultNotExecutedCount
   • ResultNotImpactedCount

Your report should look similar to the following image.

Test task resources

• Run tests in parallel using the Visual Studio Test task
• Run tests in parallel for any test runner
• Speed up testing by using Test Impact Analysis (TIA)
• Manage flaky tests
• UI testing considerations
• Azure Load Testing
• Azure Pipelines task reference, Test tasks

Related articles

• Construct OData queries for Analytics
• Connect using Power BI and OData queries
• Metadata reference for Azure Pipelines
• Sample reports and quick reference index
• Slicers in Power BI