Using oData to select from Azure Dev Ops Project all open tickets and their children

Question

I am a novice :-)

I have an ado project where the ticket hierarchy is "EPIC - Feature - User Story - Bugs or Tasks - Tasks so up to 5 levels.

I want to be able to pull into Power BI using OData all workitems from ADO where the EPIC is not closed (or has closed this financial year - closed date of 21020701).

https://analytics.dev.azure.com/{organisation}/{project}/_odata/v3.0-preview/WorkItems? $select=WorkItemId,WorkItemType,Title,State,Closeddate,targetdate,closeddateSk &$filter=WorkItemType eq 'Epic' and state eq 'Closed' and closeddateSk ge 20210701 or state ne 'Closed' and WorkItemtype eq 'Epic' &startswith(Area/AreaPath,'{project}') &$orderby=WorkItemId desc

Then using those results, I want to get their children and their childrens children (and as Monty Python Life of Brian states) their childrens childrens children.

Any help would be appreciated.

Answer 1

Depending on your service there may be limitations to the number of levels you can dive into. If you are programming the service you can increase this limit.

I am not clear on the structure of your service but I think you are looking for $expand to get the children.

Here is an example using the odata service. OData uses the ampersand (&) at the first level, but when you expand to lower levels you will need to use a semi-colon (;) to separate select and further expands.

https://services.odata.org/TripPinRESTierService/People?$select=UserName&$expand=Friends($select=UserName;$expand=Friends($select=UserName;$expand=Friends($select=UserName;$expand=Friends($select=UserName))))&$filter=startsWith(UserName,'scott')