How to list all tasks for an epic using Table Transformer

Angela Bultemeier December 3, 2022

Hello! I am using a Table Transformer macro to join 2 tables. For simplicity, T1 has 1 Epic and T2 has 1 Task. The Epic Link for the Task in T2 is the Epic in T1.

I'd like to display the Epic in column 1 and the Task in column 2 (ideally, all tasks in the Epic, but I am starting with just one for simplicity).

I can easily do this with Initiatives/Epics when T1 has my Initiatives query and T2 has my Epics query using this SQL:

SELECT T1.'Key' AS Initiative, T2.'Key' AS EPIC
FROM T1 LEFT JOIN T2 ON (T1.'Key' = T2.'Parent Link')

However, when I try similar logic for the Epic/Task, it doesn't work. Here are some SQL snippets I have tried, none of which work:

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T2.'Key' IN T1.'Issues in Epic')
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Issues in Epics'->split(" , ")->indexOf(T2.'Key'::string) > -1)
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Child in Epics'->split(" , ")->indexOf(T2.'Key'::string) > -1)
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Key' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

SELECT T1.'Key' AS EPIC, T2.'Key' AS TASK
FROM T1 LEFT JOIN T2 ON (T1.'Epic Name' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

None of these queries work. Any advice?

Result of the Initiatives/Epic join (perfecto!):

InitiativesAndEpics.jpg

Result of the Epic/Task join:

EpicsAndTasks.jpg

1 answer

1 accepted

3 votes
Answer accepted
Angela Bultemeier December 3, 2022

@Katerina Kovriga _Stiltsoft_ - you seem to be an expert. I'd love any advice you might have. :) 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 3, 2022

Hi @Angela Bultemeier ,

Please provide us with two screenshots of the tables that you are trying to merge: with visible headers and 1-2 rows of data for an example. If your data is sensitive, you may recreate these tables manually using dummy data but preserving the initial cell structure. We'll try to provide you with a suitable query.

Like # people like this
Angela Bultemeier December 4, 2022

Ah - your question solved my dilemma. I had incorrectly assumed  that T1 had Epic objects and T2 had issue objects, but I had failed to consider the columns defined by the display options of the Jira/Issue Filter I was using to create T1 and T2. Including Epic Name and Epic Link in the display options as columns for T1 and T2 obviously resolved my quandary. Thank you!

Like # people like this
Angela Bultemeier December 5, 2022

@Stiltsoft support  Hello! I thought I had solved this yesterday, but I must have mis-poken.

Table 1 is a Jira/Issue Filter that returns one epic:

T1.jpg

 

Table 2 is a Jira/Issue Filter that returns one task:

T2.jpg

Sample output with the SQL Query "SELECT * FROM T*":

SampleOutputNoFilter.jpg

Sample output with the SQL Query:

SELECT T1.'Key' AS Epic, T2.'Key' AS Task
FROM T1
LEFT JOIN T2 ON (T1.'Key' = T2.'Epic Link')
GROUP BY T1.'Key', T2.'Key'

SampleOutputWithJoin.jpg

I have almost the exact same Table Transformer code working with Initiatives and Epics, but can't figure out why this isn't working. :( Any help appreciated.

For reference, here is my Initiative/Epic SQL Query that works:

SELECT T1.'Key' AS Initiative, T2.'Key' AS Epic
FROM T1
LEFT JOIN T2 ON (T1.'Key' = T2.'Parent Link')
GROUP BY T1.'Key', T2.'Key'

And the resulting output that works:

SampleOutputWithJoinForInitiatives.jpg

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2022

Please give us the two screenshots of your tables that you are trying to merge (without any macros).

From the screenshots of your Jira Issues we can see that you have, for example, epic links in your tables - maybe they will be suitable for merging.

Angela Bultemeier December 6, 2022

@Stiltsoft support - I'm confused. I provided screenshots of the tables above. ^^ They are Jira/Issue Filter tables.

T1 uses the filter "issuekey in (offers-6281)" and contains 1 Epic. The columns are Key, Summary, IssueType, Status, Resolution, Epic Name, Epic Link.

T2 uses the filter "labels in (AlphaLiveData) and contains 1 Task. The columns are Key, Summary, IssueType, Created, Priority, Status, Epic Link, and Epic Name.

What am I misunderstanding?

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 7, 2022

Hi @Angela Bultemeier ,

We've meant the screenshots of your full separate tables - I understood later that the third screenshot was a combination of your separate tables. The first row belongs to the first table and the second row - to the second table.

So, here is how I've got your case:

Wed 3-1.png

SELECT T2.'Key' AS 'Epic',
FORMATWIKI(SUM(T1.'Key' + " \\ ")) AS 'Task'
FROM T1 LEFT JOIN T* ON T1.'Epic Name' = T2.'Epic Name'
GROUP BY T2.'Key'

Wed 3-2.png

As you may notice, when we concatenate rows, the links are missing. So if you need links for your tasks, you may use the following workaround with the Pivot Table macro:

Wed 3-3.pngWed 3-4.png

SELECT T2.'Key' AS 'Epic',
T1.'Key' AS 'Task'
FROM T1 LEFT JOIN T2 ON T1.'Epic Name' = T2.'Epic Name'

Wed 3-5.pngWed 3-6.png

Now your links are present in both columns. And you may use additional Table Transformer macro to rename the second column in a prettier way if required.

Hope that this helps or at least gives you some ideas for the case.

Angela Bultemeier December 7, 2022

Thanks, @Stiltsoft support . I got it to work! Thank you! Some context since the solution is a bit different than what was prescribed.

As a side note, no need to use a Pivot table to get links. This is the code we use:

FORMATWIKI(CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'), "|https://jiraURL/browse/", T1.'Key', "]\\ ")) AS Epic

I understand what you mean by the table screenshots now. Both are Jira/Issue Filters.

T1 Definition

JQL: issuekey in (offers-6281) 

Screenshot of table output:

EpicTable.png

T2 Definition

JQL: labels in (AlphaLiveData) 

Screenshot of table output:

TaskTable.png

Output Based on Suggested SQL Query

SQL Query (adjusted since T1 has the Epic and T2 has the Task): 

SELECT T1.'Key' AS 'Epic',
FORMATWIKI(SUM(T2.'Key' + " \\ ")) AS 'Task'
FROM T1 LEFT JOIN T* ON T2.'Epic Name' = T1.'Epic Name'
GROUP BY T1.'Key'

Screenshot of output:

TransformedTable.png

Conclusion

Note that your question prompted me to output everything from the Jira Issue/Filters table to really analyze the data (thank you!) and I noticed an ampersand in the Epic Name. Removing the ampersand from the Epic Name resolved my woes. Wa-la!

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 7, 2022

Thank you for reaching out! Happy that everything is working now as expected.

If you have any other questions and your data is sensitive, note that you may always create a support request. Our portal is based on Jira Service Desk and it's confidential (so you may share your screenshots freely).

Angela Bultemeier December 8, 2022

Thank you! I'm sharing final data on the Table Transformer for reference below, including code on how we're making each Task a link based on the Task Summary. Tables are as noted in my last comment.

Table Transformer SQL Query:

SELECT

FORMATWIKI(

   CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'),

 "|https://jiraURL/browse/", T1.'Key', "]\\ ")) AS Initiative,

FORMATWIKI(SUM(

  CONCAT("[", T2.'Key', " ", ESCAPEMARKUP(T2.'Summary'), "|https://jiraURL/browse/", T2.'Key', "]\\ "))) AS 'Task'

FROM T1

LEFT OUTER JOIN T2 ON T1.'Epic Name' = T1.'Epic Name'

GROUP BY

FORMATWIKI(

   CONCAT("[", T1.'Key', " ", ESCAPEMARKUP(T1.'Summary'),

 "|https://jiraURL/browse/", T1.'Key', "]\\ ") ),

T1.'Labels',

T1.'Target Start',

T1.'Target End'

Output:

FinalEpicTaskTable.png

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events