OData Query to Only Return State Changes from Azure Devops

0
votes

I have an OData Query that I am using to pull data into PowerBI that I am trying to make more efficient. I am doing a report from Azure DevOps and pulling data in from the WorkItemRevisions resource. Currently, I am pulling all the data for a Work Item and then filtering in PowerBI to only get when the State has changed. I would like to move this filtering to the Odata query so that I can minimize the data that I am pulling into the report.

Currently, I have a query like the following (simplified example used for this question)

https://analytics.dev.azure.com/{Organization}/{Project}/_odata/v3.0-preview/WorkItemRevisions?
$select=Revision,WorkItemId,WorkItemType,Title,State,ChangedDate,LeadTimeDays,ParentWorkItemId

How can this be updated so that only Revisions where the State has changed (from New to Active, Active to Done, etc) are returned?

1
azure-devopspowerbiodata
How about the issue? Does the answer below resolved your question, If yes, you could accept it as an answer, so it could help other community members who get the same issues and we could archive this thread, thanks. If not, please let us know if you would like further assistance. – Leo Liu-MSFT

1 Answers

0
votes

How can this be updated so that only Revisions where the State has changed (from New to Active, Active to Done, etc) are returned?

I am afraid that OData Query could not perfectly achieve what we need.

There is a feature Revisions/any(r:r/state eq '{state}') to filter the work item has a set state in the past.

For example:

https://analytics.dev.azure.com/<Organization>/<Project>/_odata/v2.0//WorkItems?
  $filter=State eq 'Closed' and Revisions/any(r:r/State eq 'Active')

This query is similar to a Work Item query that uses the Was Ever operator.

As I said, this may not be a perfect solution. That because it can only filter whether the work item has ever had a specified states, but cannot accurately determine the states of the work item must be from New to Active, Active to Done. If we change the state of the workitem from Active to Resolved, then change it from Resolved to Closed. Then this work item will appear in the query results.

In addition, even if you use the UI query, we cannot accurately query the result of the work item status changing from A to B. To achieve this goal, we need to use REST API.

So, we could use the feature Revisions/any(r:r/state eq '{state}') to reduce the data pulled into the report to a certain extent.