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.

In this article you'll learn:

  • About the OData Aggregation Extension
  • How to generate a simple count of work items
  • 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

Note

The Analytics service is automatically enabled for all Azure DevOps Services. It is supported for use in production. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and provide us feedback.

Note

The Analytics service is automatically installed on all new project collections for Azure DevOps Server 2020 and later versions. It is supported for use in production. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and provide us feedback. If you upgraded from Azure DevOps Server 2019, then you're provided with the option to install the Analytics service during upgrade.

Note

The Analytics service is in preview for Azure DevOps Server 2019. You access Analytics by enabling or installing it for a project collection. Power BI integration and access to the OData feed of the Analytics Service are in Preview. We encourage you to use it and provide us feedback.

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.

Basic root URL

Use this basic root URL as a prefix for all the examples provided in this article.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/
https://{servername}:{port}/tfs/{OrganizationName}/{ProjectName}/_odata/{version}/

Note

The examples shown in this article are based on a Azure DevOps Services URL, you will need to substitute in your Azure DevOps Server URL.

Simple count aggregations

First, let's look at how to do counts without the aggregation extensions.

Basic counting is done by adding the $count query option to the end of the URL. For example, to find out how many work items are defined in your organization, you add this string to your query:

/WorkItems/$count

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems/$count

Note

The {version} value is formatted as v1.0. The latest supported version is v2.0, and the latest preview version is v4.0-preview. Data available is version-dependent. For more information, see Data model for Analytics.

For comparison, using the OData aggregation extension, you add this string to your query:

/WorkItems?$apply=aggregate($count as Count)

Where the full OData query is:

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

For simple counts, the non-aggregation approach has a simpler syntax.

Note

Using $count returns a single number; using the OData aggregation extension returns a formatted JSON.

You can also filter what you want to count. For example, if you want to know how many work items are in the "In Progress" state, specify the following string in your query:

/WorkItems/$count?$filter=State eq 'In Progress'

Where the full OData query is:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems/$count?
  $filter=State eq 'In Progress'

For comparison, using data aggregations you add this snippet to your query:

/WorkItems?$apply=filter(State eq 'In Progress')/aggregate($count as Count)

Where the full OData query is:

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

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, this clause will return 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.

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