Generating Equivalent Process Step Cycle Time Report Values in Jira Align Enterprise Insights
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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?