How to Return the Story Records from Enterprise Insights That Match the Jira Align Story Grid View

[#1]

If you have Enterprise Insights, you may have asked yourself:  "How do I get the same story records that match the Jira Align Story Grid view for a given program and program increment/quarter?" 

This article walks through the logic behind how Jira Align identifies stories associated with a program increment/quarter for a program.  For those that understand SQL, later in the article there is SQL query sample that demonstrates the logic that you can run with your Enterprise Insights instance for learning purposes.

Jira Align uses two criteria to determine the associated program increment/quarter for a story:

• The team sprint assigned to a story
• If no team sprint is assigned to a story, the Program Increment/Quarter field within the story

Stories Assigned to a Team Sprint

Within Jira Align, team sprints are associated with program increments/quarters. When a story is assigned a team sprint Jira Align uses the program increment/quarter that the team sprint is associated with and it ignores the Program Increment/Quarter field in Story Detail Panel. The screenshot of the Story Detail Panel below is an example of a story that is assigned to the Genius team Sprint 26.

The screenshot of the Sprint Detail Panel below shows the relationship of the Sprint to a Program Increment or Quarter.

Unassigned Stories

For stories in the Unassigned Backlog, Jira Align looks at the program increment/quarter in the Program Increment/Quarter field in the Story Detail Panel. The Program Increment/Quarter field is populated from the Program Increment/Quarter assigned to the parent Feature that the story is linked to in Jira Align. In the screenshot below of the Story Detail Panel, the Story is not assigned to a Sprint (Unassigned Backlog) and the Program Increment field shows R5.

SQL Query Example

The solution can be accomplished in a few different ways in a SQL query. To clearly show the logic in Jira Align, the example SQL query below uses two queries concatenated by a union clause to produce a single result set. The first query queries stories assigned to a sprint within a Program Increment and the second query queries stories not assigned (Unassigned Backlog) to a sprint but are assigned to a Program Increment from the parent Feature.

---

SELECT /*Query for stories assigned to a sprint within a Program Increment/Quarter*/
ST.[Story ID]
,ST.[Story Name]
,P.[Program ID]
,P.[Program Name]
,PI.[Program Increment ID]
,PI.[PI Name]
,PO.[Portfolio ID]
,PO.[Portfolio Name]
FROM current_dw.Story AS ST
LEFT JOIN current_dw.Sprint AS SP ON ST.[FK Sprint ID] = SP.[Sprint ID]
INNER JOIN current_dw.Program AS P ON P.[Program Id] = ST.[FK Program ID]
INNER JOIN current_dw.[Program Increment] AS PI ON PI.[Program Increment ID] = SP.[FK Program Increment ID]
INNER JOIN current_dw.Portfolio AS PO ON PO.[Portfolio ID] = P.[FK Portfolio ID]
WHERE
P.[Program Name]= 'Domain Hosting' -- Replace Program Name
AND PI.[PI Name] = 'R5' -- Replace PI Name

UNION

SELECT /*Query for stories not assigned (Unassigned Backlog) to a sprint but are assign to the Program Increment/Quarter. Program Increment/Quarter is assigned based on parent feature Program Increment/Quarter assignment*/
ST.[Story ID]
,ST.[Story Name]
,P.[Program ID]
,P.[Program Name]
,PI.[Program Increment ID]
,PI.[PI Name]
,PO.[Portfolio ID]
,PO.[Portfolio Name]
FROM current_dw.Story AS ST
LEFT JOIN current_dw.Sprint AS SP ON ST.[FK Sprint ID] = SP.[Sprint ID]
INNER JOIN current_dw.Program AS P ON P.[Program ID] = ST.[FK Program ID]
INNER JOIN current_dw.[Program Increment] AS PI ON PI.[Program Increment ID] = ST.[FK Program Increment ID]
INNER JOIN current_dw.Portfolio AS PO ON PO.[Portfolio ID] = ST.[FK Portfolio ID]
WHERE
ST.[FK Sprint ID] = 0 /*Return stories that are not assigned to a sprint*/
AND P.[Program Name]= 'Domain Hosting' -- Replace Program Name
AND PI.[PI Name] = 'R5' -- Replace PI Name

---

The screenshots below shows how the record count of the results of the SQL query match the Story Grid View work item count in the Jira Align UI.

Query results return 232 story records

The bottom of the Story Grid view displays a count of 232 records