Export data to excel from State graph in devops

Copper Contributor

Apologies I am new in a company and using Azure devOps for the first time... 


We follow a sprint lifecycle in for each user story as it passes from developments to test to production. I would like to be able to get dates from azure to track how long this takes...  I want to export the data captured as part of the state graph for all user stories or for a selection of sprints.  I would like a column set something like this ... 


See picture attached 


So criteria columns names might be as follows and the data is in the [...]

  UserID_[number], Uer story_[Title], Sprint iteration_[number_16], new_[date created],  moved to QA[date created], inproduction[datedeployed] etc 


I have excel plugin but this doesn't seem to help.  

Has anyone ever done this before? or have any idea where i might start.



8 Replies

@Az_Pre4me did you figure this out?  how or link to solution?

Hello @Az_Pre4me , @SagarwalHCSW 


I'm very interested in the subject.

If anyone has the solution, please share it with us.

Thank you very much.

@Az_Pre4me  Has anyone found the way to do this?

Hi everyone

I can offer another solution without exporting to Excel.

A Time in State for Azure DevOps extension calculates how long a Work Item is in a particular state. The reports are generated automatically; you can choose any period, set up calendars, etc. There are also charts that visualize the data obtained in the report. Here is a link to it:https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure
If you have a custom State Graph or are referring to a specific representation and want to export its data to Excel, Azure DevOps not offer a direct "export" option. However, you can always create a query that fetches the work items represented in your State Graph and then export the data to Excel.
Hello, everyone!
I have an update on the extension: https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure , which I recommended earlier.

Now, an export to Excel is available there, and you can, according to the generated Query, download all Work Items with their States of interest.

 did anyone find a way to write a query o PBI query to obtain this without a plugin?