Failed tests sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
You can create a report that lists failed tests, similar to the following image, for pipeline runs that include test tasks. For information on adding tests to a pipeline, see the Test task resources section later in this article.
Use the queries provided in this article to generate the following reports:
- Failed tests for build workflow
- Failed tests for release workflow
- Failed tests for a particular branch
- Failed tests for a particular test file
- Failed tests 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
- Access level: You must be a member of a project with Basic access or higher.
- Permission: By default, project members have permission to query Analytics and create views.
- For more information 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 failed test 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
. For more information about the data type of each value, review the metadata provided for the corresponding EntityType
.
Failed tests for a Build workflow
Use the following queries to view the failed tests for a Build workflow pipeline.
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/v4.0-preview/TestResultsDaily?
$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build' "
&") "
&"/groupby( "
&"(TestSK, Test/TestName), "
&"aggregate( "
&"ResultCount with sum as TotalCount, "
&"ResultPassCount with sum as PassedCount, "
&"ResultFailCount with sum as FailedCount, "
&"ResultNotExecutedCount with sum as NotExecutedCount, "
&"ResultNotImpactedCount with sum as NotImpactedCount, "
&"ResultFlakyCount with sum as FlakyCount)) "
&"/filter(FailedCount gt 0) "
&"/compute( "
&"iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Failed tests for Release workflow
Use the following queries to view the failed tests for a Release workflow pipeline.
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/v4.0-preview/TestResultsDaily?
$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate}) "
&"/groupby((TestSK, Test/TestName, Workflow), "
&"aggregate( "
&"ResultCount with sum as TotalCount, "
&"ResultPassCount with sum as PassedCount, "
&"ResultFailCount with sum as FailedCount, "
&"ResultNotExecutedCount with sum as NotExecutedCount, "
&"ResultNotImpactedCount with sum as NotImpactedCount, "
&"ResultFlakyCount with sum as FlakyCount)) "
&"/filter(FailedCount gt 0) "
&"/compute( "
&"iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Failed tests filtered by Branch
To view the failed tests 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
intoBranch.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.
For more information about using slicers, see Slicers in Power BI.
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/v4.0-preview/TestResultsDaily?
$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate} "
&"And Workflow eq 'Build') "
&"/groupby((TestSK, Test/TestName, Branch/BranchName), "
&"aggregate( "
&"ResultCount with sum as TotalCount, "
&"ResultPassCount with sum as PassedCount, "
&"ResultFailCount with sum as FailedCount, "
&"ResultNotExecutedCount with sum as NotExecutedCount, "
&"ResultNotImpactedCount with sum as NotImpactedCount, "
&"ResultFlakyCount with sum as FlakyCount)) "
&"/filter(FailedCount gt 0) "
&"/compute( "
&"iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Failed tests filtered by test file
To view the failed tests for a pipeline and 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
intoTest.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.
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/v4.0-preview/TestResultsDaily?
$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate}) "
&"/groupby((TestSK, Test/TestName, Test/ContainerName), "
&"aggregate( "
&"ResultCount with sum as TotalCount, "
&"ResultPassCount with sum as PassedCount, "
&"ResultFailCount with sum as FailedCount, "
&"ResultNotExecutedCount with sum as NotExecutedCount, "
&"ResultNotImpactedCount with sum as NotImpactedCount, "
&"ResultFlakyCount with sum as FlakyCount)) "
&"/filter(FailedCount gt 0) "
&"/compute( "
&"iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Failed tests filtered by test owner
To view the Failed test for 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
intoTest.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.
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/v4.0-preview/TestResultsDaily?
$apply=filter("
&"Pipeline/PipelineName eq '{pipelineName}' "
&"And Date/Date ge {startdate}) "
&"/groupby((TestSK, Test/TestName, Test/TestOwner), "
&"aggregate( "
&"ResultCount with sum as TotalCount, "
&"ResultPassCount with sum as PassedCount, "
&"ResultFailCount with sum as FailedCount, "
&"ResultNotExecutedCount with sum as NotExecutedCount, "
&"ResultNotImpactedCount with sum as NotImpactedCount, "
&"ResultFlakyCount with sum as FlakyCount)) "
&"/filter(FailedCount gt 0) "
&"/compute( "
&"iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
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. Format: YYYY-MM-DDZ. Example:2021-09-01Z
represents September 1, 2021. Don't enclose in quotes or brackets and use two digits for both, month and date.
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'
Return test runs for Build
workflow pipeline.
)
Close filter()
clause.
/groupby(
Start groupby()
clause.
(TestSK, Test/TestName),
Group by the test Name
aggregate(
Start aggregate
clause to sum the test runs matching the filter criteria.
ResultCount with sum as TotalCount,
Count the total number of test runs as TotalCount
.
ResultPassCount with sum as PassedCount,
Count the total number of passed test runs as PassedCount
.
ResultFailCount with sum as FailedCount,
Count the total number of failed test runs as FailedCount
.
ResultNotExecutedCount with sum as NotExecutedCount
Count the total number of not executed test runs as NotExecutedCount
.
ResultNotImpactedCount with sum as NotImpactedCount,
Count the total number of not affected test runs as NotImpactedCount
.
ResultFlakyCount with sum as FlakyCount
Count the total number of flaky test runs as FlakyCount
.
))
Close aggregate()
and groupby()
clauses.
/compute(
Start compute()
clause.
iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate
For all the tests, calculate PassRate
.
)
Close compute()
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.
Expand the Test column in Power BI
Expand the Test
column to show the expanded entity Test.TestName
. Expanding the column flattens the record into specific fields. To learn how, see Transform Analytics data to generate Power BI reports, Expand columns.
Change column data type
From the Power Query Editor, select the
TotalCount
,PassedCount
,FailedCount
,NotExecutedCount
,NotImpactedCount
, andFlakyCount
columns; select Data Type from the Transform menu; and then choose Whole Number.Select the
PassRate
column; select Data Type from the Transform menu; and then choose Decimal Number.
For more information about changing the data type, see Transform Analytics data to generate Power BI reports, Transform a column data type.
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 Table report
In Power BI, under Visualizations, choose Table and drag and drop the fields onto the Columns area.
Add the following fields to the Columns section in the order listed.
Test.TestName
TotalCount
PassedCount
FailedCount
NotImpactedCount
NotExecutedCount
FlakyCount
PassRate
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