Rediger

Del via

Test duration sample report

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

Test duration reports, similar to the one shown in the following image, provide insight into the number of times a test runs and the average time it takes for a particular test to execute during a pipeline run.

Screenshot of Test Duration Table report.

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

  • Test duration for build workflow
  • Test duration for release workflow
  • Test duration for a particular branch
  • Test duration for a particular test file
  • Test duration 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

Use the following queries of the TestResultsDaily entity set to create different but similar test duration reports. The TestResultsDaily 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 duration for Build workflow

Use the following queries to view the test duration report for a pipeline with a Build workflow.

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 Date/Date ge {startdate} "
      &"And Workflow eq 'Build'"
      &")/groupby("
      &"(TestSK, Test/TestName),"
      &"aggregate("
      &"ResultCount with sum as TotalCount, "
      &"ResultDurationSeconds with sum as TotalDuration"
      &"))/compute("
      &"TotalDuration div TotalCount as AvgDuration"
      &")",
      null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
   )
in
    Source

Test duration for Release workflow

Use the following queries to view the test duration report for a pipeline with a Release workflow.

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 Date/Date ge {startdate}"
      &")/groupby("
      &"(TestSK, Test/TestName, Workflow),"
      &"aggregate("
      &"ResultCount with sum as TotalCount, "
      &"ResultDurationSeconds with sum as TotalDuration"
      &"))/compute("
      &"TotalDuration div TotalCount as AvgDuration"
      &")",
      null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
   )
in
    Source

Test duration filtered by branch

To view the test duration of pipeline tests 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 Date/Date ge {startdate} "
      &"And Workflow eq 'Build'"
      &")/groupby("
      &"(TestSK, Test/TestName, Branch/BranchName),"
      &"aggregate("
      &"ResultCount with sum as TotalCount, "
      &"ResultDurationSeconds with sum as TotalDuration"
      &"))/compute("
      &"TotalDuration div TotalCount as AvgDuration"
      &")",
      null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
   )
in
    Source

Test duration filtered by test file

To view the test duration 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.ContainerName.
  • Select Power BI Visualization Slicer and add the field Test.ContainerName to the slicer's Field.
  • Select the test file 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 Date/Date ge {startdate} "
      &"And Workflow eq 'Build'"
      &")/groupby("
      &"(TestSK, Test/TestName, Test/ContainerName),"
      &"aggregate("
      &"ResultCount with sum as TotalCount, "
      &"ResultDurationSeconds with sum as TotalDuration"
      &"))/compute("
      &"TotalDuration div TotalCount as AvgDuration"
      &")",
      null, [Implementation="2.0", OmitValues = ODataOmitValues.Nulls, ODataVersion = 4]
   )
in
    Source

Test duration filtered by test owner

To view the test duration 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 Date/Date ge {startdate} "
      &"And Workflow eq 'Build'"
      &")/groupby("
      &"(TestSK, Test/TestName, Test/TestOwner),"
      &"aggregate("
      &"ResultCount with sum as TotalCount, "
      &"ResultDurationSeconds with sum as TotalDuration"
      &"))/compute("
      &"TotalDuration div TotalCount as AvgDuration"
      &")",
      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: 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 named pipeline.

And Date/Date ge {startdate}

Return test runs on or after the specified date.

and Workflow eq 'Build'

Return test runs for a Build workflow.

)

Close filter() clause.

/groupby(

Start groupby() clause.

(TestSK, Test/TestName),

Group by the test name.

aggregate(

Start aggregate clause to sum different test run outcomes matching the filter criteria.

ResultCount with sum as TotalCount,

Count the total number of test runs as TotalCount.

ResultDurationSeconds with sum as TotalDuration

Sum the total duration of all the runs as TotalDuration.

))

Close aggregate() and groupby() clauses.

/compute(

Start compute() clause.

TotalDuration div TotalCount as AvgDuration

For all the tests, calculate the average duration by dividing the total duration by total number of runs.

)

Close compute() 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.

Expand the Test column in Power BI

Expand the Test column to show the expanded entity Test.TestName. When you expand the column, you flatten the record into specific fields. For more information, see Transform Analytics data to generate Power BI reports, Expand columns.

Change column data type

  1. In Power Query Editor, select the TotalCount column. Select Data Type from the Transform menu, and then choose Whole Number.

  2. Select the TotalDuration and AvgDuration columns. 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

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 Table report

  1. In Power BI, under Visualizations, select Table. Drag and drop the fields onto the Columns area.

    Screenshot of visualization fields selections for Test Duration table report.

  2. Add the following fields to the Columns section in the order listed.

    • Test.TestName
    • TotalCount
    • AvgDuration

  3. Right-select the AvgDuration field and choose Average instead of Sum.

Your report should look similar to the following image.

Screenshot of Sample Test Duration Table report.

  • Last updated on