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. For more information, 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.

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

Related articles

• Query your work tracking data
• Aggregate data
• OData v4.0 specification
• OData v4.0 Part 2: URL Conventions Plus Errata 02
• OData v4.0 Aggregation Extension