Edit

Share via

Pipeline pass rate trend sample report

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

This article shows you how to create a report that shows a pipeline's daily pass rate trend. The pass rate of a pipeline is the percentage of successful pipeline runs to the total pipeline runs. It's similar to the 'Pass rate trend' chart of the Pipeline pass rate report. The following image shows an example of such a trend.

Screenshot of Power BI Pipelines Runs Pass Rate Trend report.

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 pass rate trend reports, use the following queries of the PipelineRuns entity set.

Note

To find available properties for filtering or reporting, see the 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.

Pass rate trend for a named pipeline

The following queries return the pipeline runs for a specific pipeline from a specified start date.

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/v3.0-preview/PipelineRuns?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"and CompletedDate ge {startdate} "
                &"and CanceledCount ne 1 "
        &") "
        &"/groupby( "
            &"(CompletedOn/Date), "
                &"aggregate "
                &"($count as TotalCount, "
            &"SucceededCount with sum as SucceededCount , "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount)) "
        &"/compute( "
    &"SucceededCount mul 100.0 div TotalCount as PassRate, "
    &"FailedCount mul 100.0 div TotalCount as FailRate, "
    &"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
    &"&$orderby=CompletedOn/Date asc "
    ,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-DD followed by Z (UTC indicator). Example: 2026-09-01Z represents September 1, 2026. Don't enclose in quotes or brackets.

Query breakdown

The following table describes each part of the query.

Query part

Description

$apply=filter(

Start filter() clause.

Pipeline/PipelineName eq '{pipelinename}'

Return pipeline runs for the specified pipeline.

and CompletedDate ge {startdate}

Return pipeline runs on or after the specified date.

and CanceledCount ne 1

Omit canceled pipeline runs.

)

Close filter() clause.

/groupby(

Start groupby() clause.

(CompletedOn/Date),

Group by date of completion of pipeline run.

aggregate

Start aggregate clause for all the pipeline runs matching the filter criteria.

($count as TotalCount,

Count the total number of runs as TotalCount.

SucceededCount with sum as SucceededCount ,

Count the number of successful runs as SucceededCount.

FailedCount with sum as FailedCount,

Count the number of failed runs as FailedCount.

PartiallySucceededCount with sum as PartiallySucceededCount))

Count the number of partially successful runs as PartiallySucceededCount. Close aggregate() and groupby() clauses.

/compute(

Start of compute() clause.

SucceededCount mul 100.0 div TotalCount as PassRate,

Calculate PassRate for each day by dividing number of successful runs by number of total runs.

FailedCount mul 100.0 div TotalCount as FailRate,

Calculate FailRate for each day by dividing number of failed runs by number of total runs.

PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate)

Calculate PartiallySuccessfulRate for each day by dividing number of partially successful runs by number of total runs.

&$orderby=CompletedOn/Date asc

Order the result in ascending order based on date of pipeline run.

Pass rate trend for a pipeline ID

You can rename pipelines. To keep Power BI reports from breaking when you change a pipeline name, use the pipeline ID instead of the pipeline name. You can get the pipeline ID from the URL of the pipeline runs page.

https://dev.azure.com/{organization}/{project}/_build?definitionId={pipelineid}

The following queries return the pipeline runs for a specific pipeline ID from a specified start date.

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/v3.0-preview/PipelineRuns?"
        &"$apply=filter( "
                &"PipelineId eq {pipelineId} "
                &"and CompletedDate ge {startdate} "
                &"and CanceledCount ne 1 "
        &") "
        &"/groupby( "
            &"(CompletedOn/Date), "
                &"aggregate "
                &"($count as TotalCount, "
            &"SucceededCount with sum as SucceededCount , "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount)) "
        &"/compute( "
    &"SucceededCount mul 100.0 div TotalCount as PassRate, "
    &"FailedCount mul 100.0 div TotalCount as FailRate, "
    &"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
    &"&$orderby=CompletedOn/Date asc "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Pass rate trend, filter by branch

You might want to view the pass rate trend of a pipeline for a particular branch only. To create the report, follow these extra steps along with the steps outlined in the Change column data type and Create the Line chart report sections.

  • Expand Branch into Branch.BranchName.
  • Select Power BI Visualization Slicer and add Branch.BranchName to the slicer's Field.
  • Select the branch from the slicer for which you need to see the pass rate trend.

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/v3.0-preview/PipelineRuns?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"and CompletedDate ge {startdate} "
                &"and CanceledCount ne 1 "
        &") "
        &"/groupby( "
            &"(Branch/BranchName, CompletedOn/Date), "
                &"aggregate "
                &"($count as TotalCount, "
            &"SucceededCount with sum as SucceededCount , "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount)) "
        &"/compute( "
    &"SucceededCount mul 100.0 div TotalCount as PassRate, "
    &"FailedCount mul 100.0 div TotalCount as FailRate, "
    &"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
    &"&$orderby=CompletedOn/Date asc "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Pass rate trend, filter by build reason

You might want to view the pass rate trend of a pipeline for only specific Build Reasons (Manual, BatchedCI, Pull Request, and so on). To create the report, follow these extra steps along with the steps outlined in the Change column data type and Create the Line chart report sections.

  • Select Slicer from the Visualizations pane and add the RunReason to the slicer's Field.
  • Select the run reason from the slicer for which you need to see the pass rate trend.

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/v3.0-preview/PipelineRuns?"
        &"$apply=filter( "
                &"Pipeline/PipelineName eq '{pipelineName}' "
                &"and CompletedDate ge {startdate} "
                &"and CanceledCount ne 1 "
        &") "
        &"/groupby( "
            &"(RunReason, CompletedOn/Date), "
                &"aggregate "
                &"($count as TotalCount, "
            &"SucceededCount with sum as SucceededCount , "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount)) "
        &"/compute( "
    &"SucceededCount mul 100.0 div TotalCount as PassRate, "
    &"FailedCount mul 100.0 div TotalCount as FailRate, "
    &"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
    &"&$orderby=CompletedOn/Date asc "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Pass rate trend for all project pipelines

Use the following queries to view the pass rate trend for all the pipelines of the project in a single report. To create the report, follow these extra steps along with what is outlined in the Change column data type and Create the Line chart report sections.

  • Expand Pipeline into Pipeline.PipelineName.
  • Select Slicer from the Visualizations pane, and add the field Pipeline.PipelineName to the slicer's Field.
  • Select the Build pipeline from the slicer for which you need to see the pass rate trend.

Refer to the Outcome summary for all pipelines sample report, which has similar detailed steps.

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/v3.0-preview/PipelineRuns?"
        &"$apply=filter( "
                &"CompletedDate ge {startdate} "
                &"and CanceledCount ne 1 "
                &") "
        &"/groupby( "
        &"(Pipeline/PipelineName, CompletedOn/Date), "
            &"aggregate "
                &"($count as TotalCount, "
                &"SucceededCount with sum as SucceededCount , "
            &"FailedCount with sum as FailedCount, "
                &"PartiallySucceededCount with sum as PartiallySucceededCount)) "
            &"/compute( "
        &"SucceededCount mul 100.0 div TotalCount as PassRate, "
    &"FailedCount mul 100.0 div TotalCount as FailRate, "
    &"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
    &"&$orderby=CompletedOn/Date asc "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

(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 columns in Power Query Editor

Before creating the report, expand columns that return records containing several fields. In this case, expand the CompletedOn column to flatten it to CompletedOn.Date.
To learn how to expand work items, see Transform Analytics data to generate Power BI reports.

Change column data type

From the Transform menu, change the data type for the following columns. To learn how, see Transform a column data type.

  • Change the PassRate, FailRate, and PartiallySuccessfulRate columns to Decimal Number.
  • Change TotalCount to Whole Number.

(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 Line chart report

  1. In Power BI, under Visualizations, select the Line chart report.

    Screenshot of visualization fields selections for pass rate trend line chart report.

  2. Add CompletedOn.Date to X-Axis. Right-click the field and choose CompletedOn.Date.

  3. Add PassRate to Y-Axis, and right-click it to ensure Sum is selected.

  4. To change the report title, select the Format your visual paint-brush icon from the Visualizations pane, select General, expand Title, and replace the existing text.

    The following image shows the resulting report.

    Screenshot of Power BI sample Pipelines Runs Pass Rate Trend report.

  • Last updated on