Cumulative Flow Diagram (CFD) sample report

Članak
02/09/2022
7 min. za čitanje

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

This article shows you how to display the Stories CFD for a specified team. An example is shown in the following image.

Sample - CFD - Report

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Prerequisites

You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your *Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/WorkItemBoardSnapshot?"
        &"$apply=filter( "
            &"Team/TeamName eq '{teamname}' "
            &"and BoardName eq 'Stories'  "
            &"and DateValue ge {startdate} "
        &") "
        &"/groupby( "
            &"(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath),  "
            &"aggregate($count as Count) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/WorkItemBoardSnapshot?
        $apply=filter(
            Team/TeamName eq '{teamname}'
            and BoardName eq 'Stories' 
            and DateValue ge {startdate}
        )
        /groupby(
            (DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath), 
            aggregate($count as Count)
        )

Substitution strings

Each query contains the following strings that you must substitute with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

{organization} - Your organization name
{project} - Your team project name, or omit "/{project}" entirely, for a cross-project query

{teamname} - The name of the team to display the CFD for.
{startdate} - The date to start the CFD chart from. Format: YYYY-MM-DDZ. Example: 2019-04-01Z represents 2019-April-01. Don't enclose in quotes.

Query breakdown

The following table describes each part of the query.

Query part

Description

$apply=filter(

Start filter()

Team/TeamName eq '{teamname}'

Return items for a specific team

and BoardName eq 'Stories'

Return items on the 'Stories' backlog. You can specify other backlog names, such as 'Epics', and 'Features'

and DateValue ge {startdate}

Start CFD on or after the specified date. Example: 2019-04-01Z represents 2019-April-01 2019-July-01

)

Close filter()

/groupby(

Start groupby()

(DateValue, ColumnName, LaneName, State, WorkItemType,AssignedTo/UserName,Area/AreaPath),

Group by DateValue (used for trending), ColumnName, and any other fields you want to report on. Here we include LaneName to enabling filtering by LaneName

aggregate($count as Count)

Aggregate as count of work items.

)

Close groupby()

Query filters

To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to an EntityType.

For example, the EntitySet Name="WorkItemSnapshot" corresponds to the EntityType Name="WorkItemSnapshot". The OData metadata for EntitySet Name="WorkItemSnapshot" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.

<EntitySet Name="WorkItemSnapshot" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.WorkItemSnapshot">
  <NavigationPropertyBinding Path="Date" Target="Dates"/>
  <NavigationPropertyBinding Path="RevisedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="Teams" Target="Teams"/>
  <NavigationPropertyBinding Path="Processes" Target="Processes"/>
  <NavigationPropertyBinding Path="Project" Target="Projects"/>
  <NavigationPropertyBinding Path="Area" Target="Areas"/>
  <NavigationPropertyBinding Path="Iteration" Target="Iterations"/>
  <NavigationPropertyBinding Path="AssignedTo" Target="Users"/>
  <NavigationPropertyBinding Path="ChangedBy" Target="Users"/>
  <NavigationPropertyBinding Path="CreatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ActivatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ClosedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ResolvedBy" Target="Users"/>
  <NavigationPropertyBinding Path="Tags" Target="Tags"/>
  <NavigationPropertyBinding Path="ChangedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ClosedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CreatedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ResolvedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="StateChangeOn" Target="Dates"/>
  <NavigationPropertyBinding Path="InProgressOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CompletedOn" Target="Dates"/>
</EntitySet>

Power BI transforms

Expand Area, Iteration, AssignedTo columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

Choose the expand button.
Select the fields to flatten.
The table now contains entity field(s).
Repeat steps 1 through 3 for all fields representing entities: Area, Iteration, AssignedTo.

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

Right-click a column header and select Rename...
You also may want to rename the query from the default Query1, to something more meaningful.
Once done, choose Close & Apply to save the query and return to Power BI.

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Sample -Release Burndown - Fields

For a simple report, do the following steps:

Select Power BI Visualization Stacked Area Chart.
Add the field "DateValue" to Axis
- Right-click "DateValue" and select "DateValue", rather than Date Hierarchy
Add the field "ColumnName" to Legend
Add the field "Count" to Values
On the Filter for "ColumnName", select only the values you want to appear on the chart. For example, you may want to unselect "New" and "Done"

The example report:

Sample - CFD - Report

Sorting columns in correct order

The above sample will display columns in alphabetical order. To sort the columns in the order specific on the board, do the following steps:

Use the query below to create a new query in Power BI. When done, rename the query to "ColumnOrder"

Power BI query
OData query

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/BoardLocations?"
        &"$apply=filter( "
            &"Team/TeamName eq '{teamname}'  "
            &"and BoardName eq 'Stories'  "
            &"and IsCurrent eq true "
        &") "
        &"/groupby ((ColumnName,ColumnOrder)) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/BoardLocations?
        $apply=filter(
            Team/TeamName eq '{teamname}' 
            and BoardName eq 'Stories' 
            and IsCurrent eq true
        )
        /groupby ((ColumnName,ColumnOrder))

Sort the report in the correct order

Once back in Power BI, expand ColumnOrder query and select ColumnName.
Select Modeling menu.
Select Sort by Column and choose "ColumnOrder".
Select Manage Relationships and ensure there's a relationship between "CFD.ColumnName" and "ColumnOrder.ColumnName".
- It's likely that the relationship was autodetected.
In the report created above, add "ColumnOrder.ColumnName" to Legend, replacing "CFD.ColumnName".

The report will now be sorted by correct column order:

Sample - Release Burndown - Report

Note

If any work items were in a column that has since been deleted, they will appear as "Blank" in the above report.

Pull in data from multiple teams

If you're pulling data in from multiple teams to aggregate across teams, you must ensure every team in the report has exactly the same set of columns on their boards. Otherwise, you're pulling in varying columns from every team. It's also a good idea to add a Slicer Visualization to your report, with Team.TeamName as a field. This step allows quick filter of the report by team.

Cumulative Flow Diagram (CFD) sample report

Prerequisites

Sample queries

Substitution strings

Query breakdown

Query filters

Power BI transforms

Expand Area, Iteration, AssignedTo columns

Rename fields and query, then Close & Apply

Create the report

Sorting columns in correct order

Sort the report in the correct order

Pull in data from multiple teams

Full list of sample reports

Povratne informacije

Cumulative Flow Diagram (CFD) sample report

Je li ova stranica od pomoći?

Prerequisites

Sample queries

Substitution strings

Query breakdown

Query filters

Power BI transforms

Expand Area, Iteration, AssignedTo columns

Rename fields and query, then Close & Apply

Create the report

Sorting columns in correct order

Sort the report in the correct order

Pull in data from multiple teams

Full list of sample reports

Related articles

Povratne informacije