Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,465,985
Community Members
 
Community Events
176
Community Groups

How to list all tasks for an epic using Table Transformer

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

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

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

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

@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

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.

@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?

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.

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!

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).

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

Atlassian Community Events