Generating Equivalent Process Step Cycle Time Report Values in Jira Align Enterprise Insights
Summary
The process step cycle time report displays three charts related to process steps to show statistics on work items flowing through a value stream. This article will share a query that can be utilized in Enterprise Insights to simulate how many days a Feature spends on each Process Step.
Solution
The view [Feature Process Step Assignment History] keeps track of all the changes on the Process Step related to a Feature, so it saves on the field [Assignment Start Date] when it was moved to this Step, and to [Assignment End Date] when it left it.
Use the sample query below replacing the Progam and PI IDs.
declare @ProgramId int = <programIdHere>
declare @ProgramIncrementId int = <programIncrementIdHere>
select
f.[feature id],
f.[external feature id],
f.[feature name],
ps.[process step name],
fpsa.[Assignment Start Date],
IIF
(
fpsa.[Assignment End Date] = '9999-12-31 00:00:00.000',
--GETDATE(),
NULL,
fpsa.[Assignment End Date]
)
AS [Assignment End Date],
IIF
(
fpsa.[Assignment End Date] = '9999-12-31 00:00:00.000',
DATEDIFF(DAY,fpsa.[Assignment Start Date],GETDATE()),
DATEDIFF(DAY,fpsa.[Assignment Start Date],fpsa.[Assignment End Date])
)
AS [Days in Step],
p.[program name],
pi.[pi name]
from current_dw.[Feature Process Step Assignment History] fpsa
inner join current_dw.[feature] f on fpsa.[fk feature id] = f.[feature id]
inner join current_dw.[process step] ps on fpsa.[fk process step id] = ps.[process step id]
inner join current_dw.[program] p on f.[FK Program ID] = p.[Program ID]
inner join current_dw.[Program Increment] pi on f.[FK Program Increment ID] = pi.[Program Increment ID]
where
p.[Program ID] = @ProgramId
AND pi.[Program Increment ID] = @ProgramIncrementId
order by f.[feature id],fpsa.[feature process step assignment history id]The same idea can be used for Epics/Stories adjusting it to use the table [Epic Process Step Assignment History]/ [Story Process Step Assignment History]
Was this helpful?