Pipeline outcome summary for all pipelines sample report
Azure DevOps Services | Azure DevOps Server 2020
You may want to see pipeline metrics such as pass rate, number of failures, duration, and so on. for all the pipelines together, in a single report. This article shows you how to get pipeline outcome summary, for all the pipelines in a project. You can take a similar approach to get other metrics like pipeline duration and number of failures for all pipelines of the project in a single 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.
An example is shown in the following image.
As shown in the above image, you can select any pipeline from the "Pipeline Name" drop-down at top right and the report will show the outcome summary for the selected pipeline only
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( "
&"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
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
- {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()
CompletedDate ge {startdate}
Return pipeline runs for date greater than specified date
)
Close filter()
/groupby(
Start groupby()
(Pipeline/PipelineName),
Group the below result by Pipeline Name
aggregate(
Start aggregate. 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()
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 Pipeline 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 Pipeline.
After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on Pipeline.
Choose the expand button
Select the checkbox "(Select All Columns)" to expand
The table now contains the expanded entity Pipeline.PipelineName
Change column type
Change the type of column TotalCount to Whole Number.
Rename fields and query, then Close & Apply
When finished, you may choose to rename columns.
Right-click a column header and select Rename...
You also may want to rename the query from the default Query1, to something more meaningful.
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:
- Select Power BI Visualization Stacked Column Chart.
- Add the field "SucceededCount" to Values.
- Right-click "SucceededCount" field and ensure Sum is selected.
- Add the field "FailedCount" to Values.
- Right-click "FailedCount" field and ensure Sum is selected.
- Add the field "CanceledCount" to Values.
- Right-click "CanceledCount" field and ensure Sum is selected.
- Add the field "PartiallySucceededCount " to Values.
- Right-click "PartiallySucceededCount " field and ensure Sum is selected.
- Add the field "Pipeline.PipelineName" to Axis.
- Click somewhere outside the stacked column chart and select Power BI Visualization Slider to add a slicer.
- Add the field "Pipeline.PipelineName" to Field.
- Select the down-arrow of slicer to select the option Dropdown instead of List.
Your report should look like this.
Full list of Pipelines sample reports
Pipeline
Pipeline and test
Related articles
الملاحظات
إرسال الملاحظات وعرضها المتعلقة بـ