Pass rate trend of a test sample report

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

Pass rate trend reports provide insight into average time it takes for a particular test to execute during a pipeline run.

An example is shown in the following image.

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.

Sample 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

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 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
• {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.

(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 Date column in Power BI

Expand the Date column to show the expanded entity CompletedOn.Date. Expanding a 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

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

2. Select the PassRatecolumn; select Data Type from the Transform menu; and then choose Decimal Number.

To learn more 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 line and stack column chart report

1. In Power BI, under Visualizations, choose the Line and stack column chart and drag and drop the fields onto the chart areas.

   

2. Add Date.Date to the X-axis, right-click the field and select Date.Date rather than Date Hierarchy.

3. Add ResultPassCount and ResultFailCount to the Column y-axis.

4. Add PassRate to the Line y-axis.

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