Need help extracting Schedule State Cycle Time data from Excel Add In or Lookback API

[modonnell2017]

I would like to generate a list of work items (User Stories, Defects, Tasks) that include the following for each:

 

FormattedID	State	StateValue	StartDate	EndDate
US35239	ScheduleState	Backlog	8/31/2017 8:08	11/27/2017 1:46
US35239	ScheduleState	Defined	11/27/2017 1:46	12/4/2017 5:20
US35239	ScheduleState	In-Progress	12/4/2017 5:20	12/11/2017 7:43
US35239	ScheduleState	Completed	12/11/2017 7:43	12/12/2017 8:08
US35239	ScheduleState	Accepted	12/12/2017 8:08

 

I have been unsucessful creating this through the Excel Add-In and I am getting lost quickly in the lookback API document as I couldn't find any relevant query examples there either.

 

I know this can be accomplished directly through agile central using the custom page "Cycle Time Data App" but I would rather not have to pull in that specific data out of Agile Central when everything else i need comes directly from the Ecxel Add In.

 

Any assistance would be greatly appreciated.

[Legacy User]

You would have to use the Lookback API to get the data for all of those state changes.  I'm not a lookback api guy.  It's very complex (at least the ones I've looked at) and even the Agile Central guru's don't get it right all the time (case in point, many of the lookback apps don't ignore deleted artifacts).  If you can settle for just cycle time from In-Process to Accepted, you can do it with Excel.  There are dates in the user story for In-Progress Date and Accepted Date.  You simply have to do the math for the difference of the two dates, ignoring any non-working days.  I've written that formula before in Excel so I know it can be done.