Supported OData functions and clauses

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

This article covers how Analytics supports several OData functions. Unsupported functions are also listed. OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines best practices for building and consuming REST APIs. To learn more, see OData documentation.

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.

Note

The Analytics service is automatically installed and supported in production for all new project collections for Azure DevOps Server 2020 and later versions. 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. If you upgraded from Azure DevOps Server 2019, then you can install the Analytics service during upgrade.

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.

Note

The Analytics service is in preview for Azure DevOps Server 2019. You can enable or install 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 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.

Supported clauses

  • $apply
  • $compute
  • $count
  • $expand
  • $filter
  • $orderby
  • $select
  • $skip
  • $top

When multiple clauses are used in a query, they're applied in the order specified above. The order of clauses in the query string is ignored. For example, in the following query, work items are first grouped and aggregated. Next, the groups are filtered. After that, the filtered groups are sorted. Finally, the first five records are returned. The query returns the top five work item types used at least 100 times.

WorkItems?$filter=Count ge 100&$apply=groupby((WorkItemType), aggregate($count as Count))&$orderby=Count&top=5

Aggregation extensions support

It takes a sequence of set transformations, separated by forward slashes to express that they're consecutively applied. The result of each transformation is the input to the next transformation. For example, in the following query, work items are filtered and then grouped by work item type and state. Next, the groups are filtered and grouped again.

Note

OData aggregation extensions are relatively new and not yet fully supported by some client tools.

Workitems?$apply=filter(State ne 'Closed')/groupby((WorkItemType, State), aggregate($count as Count))/filter(Count gt 100)/groupby((State),aggregate(Count with max as MaxCount))  

The following transformations are supported:

Transformation Notes
aggregate Allows aggregation using one of following methods $count, average, max, min, sum
compute Allows adding calculated properties
expand Allows expansion by specified properties
filter Allows filtering input set. Supports the same expressions as $filter
groupby Allows grouping by properties

For more information, see Aggregate work tracking data.

Supported functions

Canonical function Description
cast Returns expression of the current instance cast to the type specified.
contains Returns true if the second parameter string value is a substring of the first parameter string value, otherwise it returns false.
endswith Returns true if the first parameter string value ends with the second parameter string value, otherwise it returns false.
startswith Returns true if the first parameter string value starts with the second parameter string value, otherwise it returns false.
length Returns the number of characters in the parameter value.
indexof Returns the zero-based character position of the first occurrence of the second parameter value in the first parameter value or -1 if the second parameter value doesn't occur in the first parameter value.
substring Returns a substring of the first parameter string value, starting at the Nth character and finishing at the last character (where N is the second parameter integer value).
tolower Returns the input parameter string value with all uppercase characters converted to lowercase.
toupper Returns the input parameter string value with all lowercase characters converted to uppercase.
trim Returns the input parameter string value with all leading and trailing whitespace characters.
year Returns the year component of the Date or DateTimeOffset parameter value.
month Returns the month component of the Date or DateTimeOffset parameter value.
day Returns the day component of the Date or DateTimeOffset parameter value.
date Returns the date part of the DateTimeOffset parameter value.
time Returns the time part of the DateTimeOffset parameter value.
totaloffsetminutes Returns the signed number of minutes in the time zone offset part of the DateTimeOffset parameter value.
now Returns the current point in time (date and time with time zone) as a DateTimeOffset value.
maxdatetime Returns the latest possible point in time as a DateTimeOffset value.
mindatetime Returns the earliest possible point in time as a DateTimeOffset value.

OData functions are used in a $filter clause, but not in a $select clause the way they would be uses in a SQL statement.

For example, you can specify:

/WorkItems?$filter=toupper(Title) eq 'HELP' 

However, you can't enter the following string:

/WorkItems?$select=WorkItemId,State,toupper(Title)

Unsupported features

  • bottomcount
  • bottomsum
  • bottompercent
  • $crossjoin
  • concat
  • countdistinct
  • from
  • isdefined
  • $rollup
  • $search
  • topcount
  • topsum
  • toppercent