Azure DevOps OData Sprint燃尽图未显示0故事点 [英] Azure DevOps OData Sprint Burndown Chart not showing 0 story point

问题描述

我指的是使用OData查询构建Power BI报表时的MS文档。

我对查询进行了少许修改，以显示Sprint Burndown，sprint逐个打印，一切正常，直到我意识到团队完成所有故事要点为止，正确的燃尽区域应一直为零，就像我们在Azure DevOps UI的Sprint> Analytics中看到的那样，但在我的OData中查询，因为没有故事点，所以不会显示数据的最后一天，因此我的可视化效果永远不会为零（但实际上是这样）。

I have slightly modified the query to display Sprint Burndown, sprint-by-sprint and it all works fine until I realize when the team has completed all story points, by right the burndown area should go all the way to zero like we can see in Sprint > Analytics in Azure DevOps UI, but in my OData query, the last day of the data will not shown because it has no story point, resulting my visualization will never go to zero (but in actual fact it does).

您可能会问为什么我要执行自定义的Power BI报表，为什么要在多个组织中进行可视化显示，这就是为什么我们希望在单个Power BI报表中具有所有视图的整体视图。

You might ask why am I doing customized Power BI report, I am doing this visualization across multiple organizations that's why we want to have an overall view of all of them in single Power BI report.

我的OData查询

let
   Source = OData.Feed ("https://analytics.dev.azure.com/"& Organization &"/_odata/v3.0-preview/WorkItemSnapshot? "
        &"$apply=filter( "
            &"Project/ProjectName eq '{ProjectName}' "            
            &"and WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate ge 2020-01-01Z "
        &") "
 &" &$expand=Project,Iteration,Teams"
        ),
    #"Expanded Project" = Table.ExpandRecordColumn(Source, "Project", {"ProjectName"}, {"Project.ProjectName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded Project", "Iteration", {"IterationName", "IterationPath", "StartDate", "EndDate"}, {"Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate",  "Iteration.EndDate"}),
    #"Expanded Teams" = Table.ExpandTableColumn(#"Expanded Iteration", "Teams", {"TeamName"}, {"Teams.TeamName"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Teams",{"WorkItemId", "CompletedDate", "StoryPoints", "Teams.TeamName", "Project.ProjectName", "Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate",  "Iteration.EndDate", "DateValue"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Teams.TeamName", "Project.ProjectName", "Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate", "Iteration.EndDate", "DateValue"}, {{"Total Story Points", each List.Sum([StoryPoints]), type number}})
in
    #"Grouped Rows"

它看起来不像您看到的那样优雅，我没有在进行服务器端分组，但是我想拥有teamName信息也是如此，我尝试在查询中进行扩展和groupby，但始终出现错误。

It might not look as elegant as you can see I am not doing server side groupby, but I want to have teamName information as well, I tried expanding and groupby in query but keep getting error.

我也知道没有0故事点的原因是由于 StateCategory ne'Compl eted 。如何正确显示Power BI中的Azure DevOps燃尽图？

Also I know the reason of not having the 0 story point is because of the StateCategory ne 'Completed' ". How do I properly visualize Azure DevOps Burndown chart in Power BI?

p / s：如果有人能提出可视化总体范围和理想趋势的建议，也会更好。

p/s: It would be better if anyone can suggest on visualizing the total scope and ideal trend as well.

推荐答案

以下查询将适用于计算总范围

The following query will work for calculating the total scope

https://analytics.dev.azure.com/{orgName}/{ProjectName}/_odata/v3.0-preview/WorkItemSnapshot?
        $apply=filter(
        Teams/any(d:d/TeamName eq '{TeamName}')
        and Iteration/IterationName eq '{IterationName}'
        and (WorkItemType eq 'User Story') 
        and StateCategory ne null 
        and (DateValue ge Iteration/StartDate 
            and DateValue le Iteration/EndDate))
        /groupby((DateSK),aggregate($count as TotalScope))

但是，您必须为此查询遍历每个团队，但我无法找到任何基于团队对结果进行分组的方法。
您可以使用Teams Rest API获得所有团队。
希望这会有所帮助。 ：）

Howerever you will have to iterate throught each team for this query , I couldnot find any way to group the results based on teams. You can get all the teams using the Teams Rest API. Hope this helps. :)

这篇关于Azure DevOps OData Sprint燃尽图未显示0故事点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！