Rediger

Del via

Test summary sample report

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

For pipelines that include test tasks, you can create a report that shows the number of test runs by outcome: Passed, Failed, Not executed, and Not impacted.

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

Screenshot of Test Summary Donut report.

Use the queries 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

Note

The sample queries in this article require v3.0-preview or later of the Analytics OData feed. Share your feedback.

Category Requirements
Access levels - Project member.
- At least Basic access.
Permissions 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 read Overview of sample reports using OData queries and have a basic understanding of Power BI.

Sample queries

To create different but similar pipeline test summary reports, use the following queries of the TestResultsDaily entity set. This entity set provides a daily snapshot aggregate of TestResult executions, grouped by test.

Note

To find available properties for filtering or reporting, see the Metadata reference for Test Plans Analytics and Metadata reference for Azure Pipelines. You can filter queries or return properties by using any Property value defined under an EntityType, or any NavigationPropertyBinding Path value listed for an EntitySet. Each EntitySet maps to an EntityType, which documents the data type for each property.

Test summary for Build workflow

To view the test summary of a pipeline for a Build workflow, use the following queries.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 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

Test summary for Release workflow

To view the test summary of a pipeline for a Release workflow, use the following queries.

Note

To find available properties for filtering or reporting, see the Metadata reference for Test Plans Analytics. You can filter queries or return properties by using any Property value defined under an EntityType, or any NavigationPropertyBinding Path value listed for an EntitySet. Each EntitySet maps to an EntityType, which documents the data type for each property.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 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

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.

For more information about using slicers, see Slicers in Power BI.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 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

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.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 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

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.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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 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

Substitution strings and query breakdown

Replace the following strings with your values. Don't include the braces {} in 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: YYYYMMDD. Example: 20220815 for August 15, 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 DateSK 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. Enter a new name from the Query Settings pane.

Screenshot of Power BI query menu options, rename query.

(Optional) Rename column fields

You can rename column fields to be more user friendly. To learn how, see Rename column fields.

Close the query and apply your changes

When you finish all your data transformations, select Close & Apply from the Home menu. This action saves the query and returns you to the Report tab in Power BI.

Screenshot of Power Query Editor Close and Apply option.

Create the Donut chart report

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

    Screenshot of visualization fields selections for Test Summary report.

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

    • ResultPassCount
    • ResultFailCount
    • ResultNotExecutedCount
    • ResultNotImpactedCount Your report should look similar to the following image.

Screenshot of Sample Test Summary Donut report.

  • Last updated on