Supported OData features and clauses

Azure DevOps Services | Azure DevOps Server 2019

this article provides a summary of the OData features and functions supported or not supported by Analytics for Azure DevOps.

Note

The Analytics service is generally available for all organizations using Azure DevOps Services. It provides several advanced widgets.

Power BI integration and access to the OData feed of the Analytics Service are in Preview. It is supported for use in production. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Note

The Analytics service is in preview for Azure DevOps Server 2019. While in preview, it is available to everyone free of charge. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

You access Analytics by enabling or installing it for a project collection. The Analytics service provides several advanced widgets, Power BI integration, and access to the OData feed.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Supported clauses

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

When multiple clauses are used in query they will be applied in the order specified above. Order of clauses in query string ignored. For example, in the following query, first work items are grouped and aggregated. Next, the groups are filtered. After that, the filtered groups are sorted. Finally, the first 5 records are returned. This means the query returns the top 5 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

$apply triggers aggregation behavior. It takes a sequence of set transformations, separated by forward slashes to express that they are consecutively applied, i.e. the result of each transformation is the input to the next transformation. For example in the following query, first work item are filtered. Next, grouped by work item type and state. Then 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 details, see Aggregate data

Supported functions

Canonical function Description
cast Returns expression casted to specified type
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 does not 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:

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

Not supported features

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