Aggregate work tracking data using Analytics

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

You can get a sum of your work tracking data in one of two ways using Analytics with OData. The first method returns a simple count of work items based on your OData query. The second method returns a JSON formatted result based on your OData query that exercises the OData Aggregation Extension.

This article builds off information provided in Construct OData queries for Analytics and Define basic queries using OData Analytics. Also, the queries is this article are focused on retrieving work item data, however, the principles apply for querying other entity sets.

In this article you'll learn:

• About the OData Aggregation Extension
• How to use the Aggregation Extension for OData
• How to group and filter aggregated results
• How to aggregate data to generate a Cumulative Flow diagram

To learn how to generate simple counts, see Return a count of items (no other data) and Return a count of items and data.

Note

The Analytics service is automatically enabled and supported in production for all Azure DevOps Services. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and give us feedback. Available data is version-dependent. The latest supported version is v2.0, and the latest preview version is v4.0-preview. For more information, see OData API versioning.

What is the Aggregation Extension for OData?

Analytics relies on OData to author queries over your work tracking data. Aggregations in OData are achieved using an extension that introduces the $apply keyword. We have some examples of how to use this keyword below. Learn more about the extension at OData Extension for Data Aggregation.

Aggregate data using the OData aggregation extension

Now that you've seen how to do simple counts, let's review how to trigger aggregations using the $apply token where the basic format at the end of the URL is as follows:

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

Where:

• {entitySetName} is the entity that needs to be queried for
• {columnToAggregate} is the aggregation column
• {aggregationType} will specify the type of aggregation used
• {newColumnName} specifies the name of the column having values after aggregation.

Aggregated data using the apply extension

Using the $apply extension, you can obtain counts, sums, and additional information when you query your work tracking data.

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 the groupby clause

The OData aggregation extension also supports a groupby clause that is identical to the SQL GROUP BY clause. You can use this clause to quickly break down numbers in more detail.

For example, the following clause returns a count of work items:

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

Add the groupby clause to return a count of work items by type:

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

It returns a result similar to this example:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(WorkItemType,Count)","value":[
    {
      "@odata.id":null,"WorkItemType":"Bug","Count":3
    },
    {
      "@odata.id":null,"WorkItemType":"Product Backlog Item","Count":13
    }
  ]
}

You can also group by multiple properties as in this example:

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

It returns a result similar to this example:

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(WorkItemType,State,Count)",
  "value": [
    {
      "@odata.id": null,
      "State": "Active",
      "WorkItemType": "Bug",
      "Count": 2
    },
    {
      "@odata.id": null,
      "State": "Committed",
      "WorkItemType": "Bug",
      "Count": 1
    },
    {
      "@odata.id": null,
      "State": "Active",
      "WorkItemType": "Product Backlog Item",
      "Count": 5
    },
    {
      "@odata.id": null,
      "State": "Committed",
      "WorkItemType": "Product Backlog Item",
      "Count": 8
    }
  ]
}

You can also group across entities, however OData grouping differs from how you might normally think about it.

For example, suppose you wanted to know how many areas are in each project in an organization or collection. In OData, "count all areas and group them by project" is equivalent to "give me all projects and a count of areas for each project". This results in a query similar to:

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

Filter aggregated results

You can also filter aggregated results, however they're applied slightly differently than when you aren't using aggregation. Analytics evaluates filters along a pipe so it's always best to do the most discrete filtering first.

Filters look like this example:

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

Note

You don't have to provide the groupby clause. You can simply use the aggregate clause to return a single value.

Generate multiple aggregations within a single call

You might want to provide multiple pieces of information. An example is the sum of completed work and separately the sum of remaining work. In such a case, you can make separate calls or a single call as follows:

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

It will return a result that looks like this example:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(SumOfCompletedWork,SumOfRemainingWork)","value":[
    {
      "@odata.id":null,"SumOfCompletedWork":1525841.2900000005,"SumOfRemainingWork":73842.39
    }
  ]
}

Generate calculated properties for use within a single call

You might need to use a mathematical expression to calculate properties for use in a result set. An example is the sum of completed work that is divided by the sum of completed work plus the sum of remaining work to calculate the percentage of work completed. In such a case, you can use this example:

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

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/$metadata#WorkItems(SumOfCompletedWork,SumOfRemainingWork)","value":[
    {
      "@odata.id":null,"DonePercentage":0.96760221857946638,"SumOfRemainingWork":50715.95,"SumOfCompletedWork":1514698.3400000033
    }
  ]
}

Generate a Cumulative Flow Diagram from aggregate data

Let's say you want to create a cumulative flow diagram in Power BI. You can use a query similar to the one below:

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

It returns a result similar to this example. You can then use it directly within your data visualization of choice.

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//$metadata#WorkItemBoardSnapshot(DateValue,ColumnName,Count)",
  "value": [
    {
      "@odata.id": null,
      "DateValue": "2015-07-16T00:00:00-07:00",
      "Count": 324,
       "ColumnName": "Completed"
    },
    {
      "@odata.id": null,
      "DateValue": "2015-07-16T00:00:00-07:00",
      "Count": 5,
      "ColumnName": "In Progress"
    }
  ]
}

Let's take a look at what this query actually does:

• Filters the data to a specific team
• Filters the data to a specific backlog
• Returns a count of work items.

When refreshing Power BI or Excel, the fewer rows required, the faster the refresh occurs.

Next steps

Query trend data

Related articles

• Define basic queries using OData Analytics
• OData Extension for Data Aggregation Version 4.0