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

问题描述

我指的是这个 MS docs 使用 OData 查询构建 Power BI 报表时.

I am referring to this MS docs when building Power BI report using OData query.

我稍微修改了查询以显示 Sprint Burndown，逐个 sprint 并且一切正常，直到我意识到当团队完成所有故事点时，正确地，burndown 区域应该像我们一样一直归零可以在 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 查询

My OData query

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"

它可能看起来不像你看到的那么优雅我没有做服务器端 groupby，但我也想要 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 'Completed' ".如何在 Power BI 中正确可视化 Azure DevOps Burndown 图表?

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 Burndown Chart 未显示 0 故事点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！