Query DevOps Work Items Planned & Completed or Resolved in an Iteration

[Anonymous]

Hi

I need to build a Power BI report that shows all teh work items that were planned in an Iteration (Sprint) and those that wer completed/resolved in that Sprint.

As some work items are moved from one sprint to next I need these to be captured too.

I can't seem to get a query working to show this. I must be missing something. 

Can anyone help?

Much appreciated!

Rosie

[Anonymous · Accepted solution]

Solution posted here: Solved: Re: HELP - Power BI Report to Show Azure DevOps Sp... - Microsoft Power BI Community

[V-pazhen-msft]

@Anonymous 

Not clear, can you explain your question with sample data.

Read this post to describe your sample:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Paul Zheng _ Community Support Team

[Anonymous]

@V-pazhen-msft I am unable to save the .pbix file sample data here in a post due to security SW blocking. Is there another way I can send it to you?

It's from this file we need the report created "Work Items Planned & Completed For Each Iteration (Sprint)".

[Anonymous]

Hi Paul,

I don't really have any sample data ... we haven't set up teh report yet. However here is some more detail than can probably make it clearer:

1. I am querying an Azure Devops Project that is set up to the Scrum Process

2. A work item has an original Iteration Path ("sprint") it belongs to, however it may not be closed in that sprint.

Example: there was not enough time to complet teh Work Item (Product Backlog Item, Bug) in sprint 3 so it's moved to sprint #4.

In this case teh query would need to provide the following output for Sprint #4:

 - nº of work items planned: this will include the above work item that transitioned from sprint #3 

 - nº work items in State "Done" - tsi would include all work items planned and completed in sprint #4

Does this help to clarify?

Currently the queries I can find in MIcrosoft docuemnation will provide the number of work items originally planned in teh sprint and does not include work items that have transitioned form other previous sprints. An example is the query using the below:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$filter=... eq 'Project Name\Iteration 1'

Tahnsk for taking time to try and answer, really appreciated!

Rosie