Aggregate work tracking data by using Analytics

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

You can aggregate work tracking data by using Analytics with OData in two ways: use $count for simple totals, or use the OData $apply aggregation extension to return grouped, filtered, and computed results as JSON.

This article builds on Construct OData queries for Analytics and Define basic queries using OData Analytics. The examples focus on work item data, but the same principles apply to other entity sets. For simple count queries, see Get a count of items.

Prerequisites

About the $apply aggregation extension

OData provides an aggregation extension that introduces the $apply keyword for grouping, filtering, and computing aggregate values over your work tracking data. The following sections show how to use $apply with aggregate, groupby, filter, and compute. For the full specification, see OData Extension for Data Aggregation.

Aggregate data by using $apply

Append the $apply token to your query URL to trigger aggregations. The basic syntax is:

/{entitySetName}?$apply=aggregate({columnToAggregate} with {aggregationType} as {newColumnName})

The following examples show common aggregate operations.

Return the sum of all remaining work

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate(RemainingWork with sum as SumOfRemainingWork)

Return the last work item identifier

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=aggregate(WorkItemId with max as MaxWorkItemId)

Group results using groupby

The groupby clause works like SQL GROUP BY — it breaks down aggregated results by one or more properties.

Count work items by type

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=groupby((WorkItemType), aggregate($count as Count))

Returns a result like:

{
  "value": [
    { "WorkItemType": "Bug", "Count": 3 },
    { "WorkItemType": "Product Backlog Item", "Count": 13 }
  ]
}

Group by multiple properties

Add more properties inside the groupby parentheses to create finer-grained breakdowns:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=groupby((WorkItemType, State), aggregate($count as Count))

Returns one row for each unique combination of type and state (for example, Bug/Active, Bug/Committed, Product Backlog Item/Active).

Group across entities

You can group across related entities by using navigation properties. For example, to count areas per project:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/Areas?
  $apply=groupby((Project/ProjectName), aggregate($count as Count))

Filter aggregated results

Use filter() inside $apply to narrow data before or after aggregation. Chain multiple filters by using / (pipe) and place the most selective filter first for best performance.

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $apply=
    filter(Iteration/IterationName eq 'Sprint 89')/
    filter(WorkItemType eq 'User Story')/
    groupby((State), aggregate($count as Count))

Aggregate multiple fields in a single call

List multiple fields inside a single aggregate clause, separated by commas, to avoid extra round trips.

/WorkItems?$apply=aggregate(CompletedWork with sum as SumOfCompletedWork, RemainingWork with sum as SumOfRemainingWork)

Returns:

{
  "value": [
    { "SumOfCompletedWork": 1525841.29, "SumOfRemainingWork": 73842.39 }
  ]
}

Compute calculated properties

Pipe aggregated results into compute() to derive new values by using arithmetic expressions (div, add, sub, mul). The following example calculates the percentage of work completed:

/WorkItems?$apply=aggregate(CompletedWork with sum as SumOfCompletedWork, RemainingWork with sum as SumOfRemainingWork)/compute(SumOfCompletedWork div (SumOfCompletedWork add SumOfRemainingWork) as DonePercentage)

Returns:

{
  "value": [
    { "DonePercentage": 0.9676, "SumOfCompletedWork": 1514698.34, "SumOfRemainingWork": 50715.95 }
  ]
}

Build a cumulative flow diagram query

The following query combines filter, groupby, and aggregate against the WorkItemBoardSnapshot entity set to produce data for a cumulative flow diagram in Power BI or Excel.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItemBoardSnapshot?
  $apply=
    filter(DateValue gt 2015-07-16Z and DateValue le 2015-08-16Z)/
    filter(BoardName eq 'Stories' and Team/TeamName eq '{teamName}')/
    groupby((DateValue, ColumnName), aggregate(Count with sum as Count))
  &$orderby=DateValue

This query filters to a date range and a specific board and team, groups by date and board column, and returns a count per group. Returns:

{
  "value": [
    { "DateValue": "2015-07-16T00:00:00-07:00", "ColumnName": "Completed", "Count": 324 },
    { "DateValue": "2015-07-16T00:00:00-07:00", "ColumnName": "In Progress", "Count": 5 }
  ]
}

Next step

Related content

• Define basic queries using OData Analytics
• Construct OData queries for Analytics
• Query trend data
• OData Analytics query guidelines
• OData Extension for Data Aggregation Version 4.0