Cumulative Flow Diagram (CFD) sample report

Azure DevOps Services | 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.

Sample queries

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

Substitution strings

Each query contains the following strings that you must substitute with your values. Do not 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. Do not enclose in quotes.

Query breakdown

The following table describes each part of the query.

Query partDescription
$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()

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.

  1. Choose the expand button.

    Power BI + OData - expanding an entity column

  2. Select the fields to flatten.

    Power BI + OData - expanding an entity column

  3. The table now contains entity field(s).

    Power BI + OData - expanding an entity column

  4. 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.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  3. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

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, perform the following steps:

  1. Select Power BI Visualization Stacked Area Chart.
  2. Add the field "DateValue" to Axis
    • Right click "DateValue" and select "DateValue", rather than Date Hierarchy
  3. Add the field "ColumnName" to Legend
  4. Add the field "Count" to Values
  5. 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, perform the following steps:

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

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

Sort the report in the correct order

  1. Once back in Power BI, expand ColumnOrder query and select ColumnName.
  2. Select Modeling menu.
  3. Select Sort by Column and choose "ColumnOrder".
  4. Select Manage Relationships and ensure there is a relationship between "CFD.ColumnName" and "ColumnOrder.ColumnName".
    • It is likely that the relationship was auto-detected.
  5. 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 are pulling in data 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 are pulling in varying columns from every team. It is also a good idea to add a Slicer Visualization to your report, with Team.TeamName as a field. This allows quick filter of the report by team.

Full list of sample reports