Edit

Pipeline outcome summary for all pipelines sample reports

  • Article

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

You can use the information provided in this article to query pipeline metrics--such as pass rate, number of failures, duration, and so on--for all pipelines and create a single report. Additional queries are provided to get other metrics, such as pipeline duration and number of failures for all project pipelines.

The following image illustrates the outcome summary for all pipelines defined for a project since September 2022.

Screenshot of All Pipelines Outcome Summary Report.

As shown in the following image, you can select any pipeline from the Pipeline Name drop-down menu, and the report changes to focus on the outcome summary for the selected pipeline.

Screenshot of report that shows the outcome summary for the selected pipeline only.

Important

Power BI integration and access to the OData feed of the Analytics Service are generally available for Azure DevOps Services and Azure DevOps Server 2020 and later versions. The sample queries provided in this article are valid only against Azure DevOps Server 2020 and later versions, and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.

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.

Note

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

Sample queries

You can use the following queries of the PipelineRuns entity set to create different but similar pipeline outcome summary reports.

Note

To determine available properties for filter or report purposes, see Metadata reference for Azure Pipelines. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.

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} "
	       &")"
                &"/groupby( "
        &"(Pipeline/PipelineName), "
        &"aggregate( "
            &"$count as TotalCount, "
                &"SucceededCount with sum as SucceededCount, "
                &"FailedCount with sum as FailedCount, "
            &"PartiallySucceededCount with sum as PartiallySucceededCount, "
                &"CanceledCount with sum as CanceledCount "
            &")) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

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
  • {startdate} - The date to start your report. Format: YYYY-MM-DDZ. Example: 2022-09-01Z represents September 1, 2022. 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.

CompletedDate ge {startdate}

Return pipeline runs for date greater than the specified date.

)

Close filter() clause.

/groupby(

Start groupby() clause/

(Pipeline/PipelineName),

Group data results by pipeline name.

aggregate(

Start aggregate clause for each pipeline.

$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.

CanceledCount with sum as CanceledCount

Count the number of canceled runs as CanceledCount.

))

Close aggregate() and groupby() clauses.

(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.

Screenshot of Power BI query menu options, rename query.

Expand Pipeline column in Power Query Editor

Prior to creating the report, expand the Pipeline column that returns records that may contain one or more fields.

  1. Close the Advanced Editor.

  2. From the Power Query Editor, choose the Pipeline column expand button, ensure that PipelineName is selected, and then choose OK.

    Screenshot of Pipelines column expand menu.

    The table now contains the expanded entity Pipeline.PipelineName.

    Screenshot of Pipeline.PipelineName column.

Change column data type

From the Power Query Editor, select the TotalCount column, and then select Data Type from the Transform menu, and choose Whole Number. To learn more about changing the data type, see Transform Analytics data to generate Power BI reports, Transform a column data type.

(Optional) Rename column fields

You can rename column fields. For example, you can rename the column Pipeline.PipelineName to Pipeline Name, or TotalCount to Total Count. To learn how, see Rename column fields.

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.

Screenshot of Power Query Editor Close and Apply option.

Create the Stacked Column Chart report

  1. In Power BI, under Visualizations, choose the Stacked Column Chart report.

    Screenshot of visualization fields selections for all pipeline runs report.

  2. Add Pipeline.PipelineName or the renamed column Pipeline Name to Axis.

  3. Add the following fields to Values in the order indicated, and right-click each field and ensure Sum is selected.

    • SucceededCount
    • FailedCount
    • CanceledCount
    • PartiallySucceededCount.

  4. To add a slicer to the report, deselect the report and select Slicer from the Visualizations pane.

    • Add Pipeline.PipelineName or the renamed column Pipeline Name to Field.
      Screenshot of Visualizations pane, Slicer, Pipeline Name added.

    • To change the slicer from a list to a dropdown menu option, select the Format your visual paint-brush icon from the Visualizations pane, and select the Dropdown option instead of List.

      Screenshot of Visualizations pane, Slicer, settings options, Dropdown selected.

The report appears as follows.

Screenshot of sample All Pipelines Outcome Summary Report.