Pass rate trend of a test sample report

Azure DevOps Services

This article shows you how to create a report that shows day wise trend of number of times a test passed and failed, along with its pass rate of any given test of a pipeline.

An example is shown in the following image.

Important

Power BI integration and access to the OData feed of the Analytics Service are in Preview. 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.

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.

Sample queries

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of 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/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter("
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"And Date/Date ge {startdate} "
        &"And Test/TestName eq '{testName}' "
        &"And Workflow eq 'Build') "
            &"/groupby((Date/Date), "
                &"aggregate( "
                &"ResultCount with sum as TotalCount, "
            &"ResultPassCount with sum as ResultPassCount, "
                &"ResultFailCount with sum as ResultFailCount, "
            &"ResultAbortedCount with sum as ResultAbortedCount, "
        &"ResultErrorCount with sum as ResultErrorCount, "
    &"ResultInconclusiveCount with sum as ResultInconclusiveCount, "
    &"ResultNotExecutedCount with sum as ResultNotExecutedCount, "
    &"ResultNotImpactedCount with sum as ResultNotImpactedCount)) "
    &"/filter(ResultFailCount gt 0) "
    &"/compute( "
    &"iif(TotalCount gt ResultNotExecutedCount, ((ResultPassCount add ResultNotImpactedCount) div cast(TotalCount sub ResultNotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) "
    ,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 Date/Date ge {startdate}
	And Test/TestName eq '{testName}'
	And Workflow eq 'Build')
/groupby((Date/Date), 
	aggregate(
	ResultCount with sum as TotalCount,
	ResultPassCount with sum as ResultPassCount,
	ResultFailCount with sum as ResultFailCount,
	ResultAbortedCount with sum as ResultAbortedCount,
	ResultErrorCount with sum as ResultErrorCount,
	ResultInconclusiveCount with sum as ResultInconclusiveCount,
	ResultNotExecutedCount with sum as ResultNotExecutedCount,
	ResultNotImpactedCount with sum as ResultNotImpactedCount))
/filter(ResultFailCount gt 0)
/compute(
	iif(TotalCount gt ResultNotExecutedCount, ((ResultPassCount add ResultNotImpactedCount) div cast(TotalCount sub ResultNotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate)

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
• {pipelinename} - Your pipeline name. Example: Fabrikam hourly build pipeline.
• {testName} - Your test name
• {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()

Pipeline/PipelineName eq '{pipelineName}'

Return test runs for the specified pipeline

And Date/Date ge {startdate}

Return test runs on or after the specified date

And Test/TestName eq '{testName}'

Return test runs only for the specified test name

and Workflow eq 'Build'

Return test runs for 'Build' workflow

)

Close filter()

/groupby(

Start groupby()

(Date/Date),

Group by the date of completion of test run

aggregate(

Start aggregate. For all the test runs matching the above filter criteria:

ResultCount with sum as TotalCount,

Count the total number of test runs as TotalCount.

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.

ResultAbortedCount with sum as ResultAbortedCount,

Count the total number of aborted test runs as ResultAbortedCount.

ResultErrorCount with sum as ResultErrorCount,

Count the total number of errored test runs as ResultErrorCount.

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 aggregate() and groupby()

/compute(

Start compute()

iif(TotalCount gt ResultNotExecutedCount, ((ResultPassCount add ResultNotImpactedCount) div cast(TotalCount sub ResultNotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate)

For all the days, calculate Pass rate.

)

Close compute()

Power BI transforms

The query returns some columns that you need to expand and flatten into its fields before you can use them in Power BI. In this example, such an entity is Date.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on Date.

Expand the Date column

1. Choose the expand button

   

2. Select the checkbox "(Select All Columns)" to expand

   

3. The table now contains the expanded entity CompletedOn.Date.

   

Change column type

1. Change the type of column PassRate to Decimal Number and TotalCount to Whole Number.

   

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

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

   

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

   

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

   

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

For a simple report, do the following steps:

1. Select Power BI Visualization Line and stacked column chart.
2. Add the field "Date.Date" to Shared Axis.
   • Right-click "Date.Date" and select "Date.Date", rather than Date Hierarchy.
3. Add the field "ResultPassCount" to Column values.
4. Add the field "ResultFailCount" to Column values.
5. Add the field "PassRate" to Line values.

Your report should look like this.

Full list of Pipelines sample reports

Pipeline

Pipeline and test

---

• Outcome summary
• Outcome summary for all pipelines
• Pass rate trend
• Stage wise failures
• Duration
• Duration trend
• Task duration
• Task duration trend

• Test summary
• Test summary trend
• Failed tests
• Flaky tests
• Test duration
• Test duration trend
• Pass rate trend of a test

---

Related articles

• Overview of sample reports using OData queries
• Connect using Power BI and OData queries
• Analytics OData query quick reference