Skip to content

Latest commit

 

History

History
136 lines (102 loc) · 9.43 KB

odata-supported-features.md

File metadata and controls

136 lines (102 loc) · 9.43 KB
Raw
title titleSuffix description ms.subservice ms.assetid ms.reviewer ms.author author ms.topic monikerRange ms.date
Supported OData functions and clauses
Azure DevOps
Learn about the current level of support for OData specification in Analytics for Azure DevOps.
azure-devops-analytics
8D81FEFD-F432-4E10-A415-9167B5FE9A57
desalg
chcomley
chcomley
conceptual
>= azure-devops-2019
09/30/2020

Supported OData functions and clauses

[!INCLUDE version-gt-eq-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.

[!INCLUDE temp]

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