Table transformation how to check for sub tasks and ignore

Question

hi,

below is my jql filter i am using

project = ISOCQAT3 AND component = Deploy AND "End date" >= 2024-01-01 AND "End date" < 2025-01-01 ORDER BY "End date" ASC

and below is the table transformation query

select T1.'T' as 'Issue Type'

, T1.'Start date', T1.'End date' from T1

and the output is something like

Fist line is a sub task of the second line which is a story.

i should exclude stories if it has sub tasks and dont display, and display only the first line in this case.if the story does not have any sub-tasks i should display that story.

how do i do that please? can something be done for this in the selection query itself or can it be done in the table transformer code?

thanks

Nagarajan.

Holly Esparza_2aiixr · Answer

If you have Scriptrunner, it would be easy to do this in a Jira JQL filter rather than using Table Transformer:

issuefunction not in hasSubtasks() or issuetype = sub-task

Nags Subramanian · Answer

thanks. 'Sub-tasks' is NULL check on the table transformer query worked perfectly fine.

Stiltsoft support · Answer

What concerns the macro organization in general, you may combine your macros in different ways.

For example, you may filter your table with the help of the Table Filter macro and then put the Table Transformer macro on top of it to apply the ROWNUM() function and rename your columns. Then the ROWNUM() function will be applied to the filtered table and not to the original one.

Also, regarding your initial case: if you need to just select rows that don't have sub-tasks, then you can just apply a filter on the Sub-Tasks field.

From the screenshot from your original question I can see that you have stories and sub-tasks in one table. So, for stories the Sub-Tasks field can be empty or not empty and for tasks it is always empty.

So, you may use the Table Filter macro and filter empty cells of the Sub-Tasks field. Or your may filter with the help of the Table Transformer macro using the WHERE clause:

SELECT * FROM T1

WHERE 'Sub-Tasks' IS NULL