Pipeline pass rate trend sample report

Azure DevOps Services | Azure DevOps Server 2020

This article shows you how to create a report that shows a pipeline's daily pass rate trend. Pass rate of a pipeline is defined as 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.

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.

The following image shows an example of such a trend.

Sample - Pipelines Pass rate trend - Report

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

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.
  • 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.
  • Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
  • 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

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/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

Each query contains the following strings that you must substitute 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.
  • {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 pipeline runs for the specified pipeline

and CompletedDate ge {startdate}

Return pipeline runs on or after the specified date

and CanceledCount ne 1

Omit the canceled pipeline runs

)

Close filter()

/groupby(

Start groupby()

(CompletedOn/Date),

Group by date of completion of pipeline run

aggregate

Start aggregate. For all the pipeline runs matching the above 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()

/compute(

Start of compute()

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

Query filters

To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to the singular value for an EntityType.

For example, the EntitySet Name="PipelineRunActivityResults" corresponds to the EntityType Name="PipelineRunActivityResult". The OData metadata for EntitySet Name="PipelineRunActivityResults" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.

<EntitySet Name="PipelineRunActivityResults" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.PipelineRunActivityResult">
  <NavigationPropertyBinding Path="Project" Target="Projects"/>
  <NavigationPropertyBinding Path="Pipeline" Target="Pipelines"/>
  <NavigationPropertyBinding Path="PipelineTask" Target="PipelineTasks"/>
  <NavigationPropertyBinding Path="PipelineJob" Target="PipelineJobs"/>
  <NavigationPropertyBinding Path="Branch" Target="Branches"/>
  <NavigationPropertyBinding Path="PipelineRunQueuedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="PipelineRunStartedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="PipelineRunCompletedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ActivityStartedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ActivityCompletedOn" Target="Dates"/>
</EntitySet>

Power BI transforms

Expand CompletedOn column

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

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

  1. Choose the expand button

    Power BI + OData - Choose expand button

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

    Power BI + OData - Select all columns

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

    Power BI + OData - Expanded entity

Change column type

The query doesn't return all the columns in the format in which you can directly consume them in Power BI reports. This section lists the columns whose type you have to change for creating reports.

  1. Change the type of columns PassRate, FailRate and PartiallySuccessfulRate to Decimal Number.

    Power BI + OData - change column type

  2. Change the type of column TotalCount to Whole Number.

    Change the type of column 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...

    Power BI Rename Columns

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

    Power BI Rename Query

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

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Sample - Pipelines pass rate trend - Fields

For a simple report, do the following steps:

  1. Select Power BI Visualization Line Chart.
  2. Add the field CompletedOn.Date to Axis.
    • Right-click CompletedOn.Date and select CompletedOn.Date, rather than Date Hierarchy.
  3. Add the field PassRate to Values.
    • Right-click PassRate field and ensure Sum is selected.

Your report should look like this.

Sample - Pipelines Pass rate trend - Report

More queries

You can use the following other queries to create different but similar reports using the same steps defined previously in this article.

Use Pipeline ID, rather than Pipeline Name

You can change your Pipeline name. To ensure that the Power BI reports don't break when the pipeline name is changed, use pipeline ID rather than pipeline name. You can obtain the pipeline ID from the URL of the pipelines runs page.

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

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/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

Filter by branch

You may want to view the pass rate trend of a pipeline for a particular branch only. To create the report, follow the extra steps below along with what is defined previously 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 pipeline from the slicer for which you need to see the outcome summary

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/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

Filter by Build Reason

You may want to view the pass rate trend of a pipeline for a particular Build Reason (Manual / BatchedCI, Pull Request, and so on) only. To create the report, follow these extra steps along with what is defined previously in this article.

  • Select Power BI Visualization Slicer and add the field RunReason to the slicer's Field
  • Select the pipeline from the slicer for which you need to see the outcome summary.

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/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

You may want to view the pass rate trend for all the pipelines of the project in a single report. To create the report, follow the extra steps below along with what is defined previously in this article.

  • Expand Pipeline into Pipeline.PipelineName
  • Select Power BI Visualization Slicer 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 outcome summary

Refer Outcome summary for all pipelines sample report that has detailed similar steps as required here.

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/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

Full list of sample reports for Pipelines

Pipeline

Pipeline and test