Extract from Azure DevOps analytics a snapshot of sprint planning hours

Question

I need to take a snapshot of the Azure DevOps analytics that gives me the number of hours predicted on the sprint planning date. If possible, grouped by parent ID.

I will also need a second view, grouping by activity, but starting from the first I believe I can reach the second.

Below is an attempted consultation, but it didn't work out very well. It returns an error message that I cannot understand why.

https://analytics.dev.azure.com/{org}/{project}/_odata/v3.0-preview//WorkItemSnapshot?$filter=(DateValue ge Iteration/StartDate and DateValue le Iteration/StartDate and WorkItemType eq 'Task')/groupby((Activity), aggregate($count as Count, RemainingWork with sum as WorkTotal))

Error:

{"error":{"code":"0","message":"VS403483: The query specified in the URI is not valid: ')' or ',' expected at position 136 in '(DateValue ge Iteration/StartDate and DateValue le Iteration/StartDate and WorkItemType eq 'Task')/groupby((Activity), aggregate($count as Count, RemainingWork with sum as RemainingWorkTotal))'..","innererror":{"message":"')' or ',' expected at position 136 in '(DateValue ge Iteration/StartDate and DateValue le Iteration/StartDate and WorkItemType eq 'Task')/groupby((Activity), aggregate($count as Count, RemainingWork with sum as RemainingWorkTotal))'.","type":"Microsoft.OData.ODataException","stacktrace":""}}}

Complementing my need, I need to obtain the values that are highlighted in yellow in the image below:

https://ibb.co/WfHdtBq

Walter Walter · Accepted Answer · 2020-12-30T06:59:11

Please try to use "$apply=filter" instead of "$filter=". $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. Please refer to this document.

In addition, I think you can use DateValue le Iteration/EndDate instead of DateValue le Iteration/StartDate. DateValue ge Iteration/StartDate means begin trend at Iteration start and DateValue le Iteration/EndDate means end trend at Interation end. You can refer to the sample reports-OData query.

Here is my query and it works on my side:

https://analytics.dev.azure.com/{org}/{project}/_odata/v3.0-preview/WorkItemSnapshot?
$apply=filter(
    DateValue ge Iteration/StartDate 
    and DateValue le Iteration/EndDate 
    and WorkItemType eq 'Task'
    )
    /groupby(
        (Activity), 
    aggregate($count as Count, RemainingWork with sum as WorkTotal)
    )

Result:

Extract from Azure DevOps analytics a snapshot of sprint planning hours

1 Answers