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

Transform Jira issue table with Table Transformer

Aurélien Thazet December 20, 2023

Hello, 

 

I'm using Jira Issue/Filter to retrieve all my Epics from my Jira project. 

screen2 (2).png

 

Because the Fix Version/S filed is pretty redundant and as I will have many other versions to come, I would like to group my columns based on this field like this:

screen2 (1).png

 

Is there a way to this ? My company already bought the Table Filter and Charts for Confluence and I've tried many things but I wasn't to reach the targeted result.

 

Many thanks for you help,

Aurélien

8 answers

2 votes
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 20, 2023

And @Aurélien Thazet , 

You may also try the following query that leads to the same result - maybe it looks more neat and simple:

SELECT
'Fix Version/S',
CONCAT_VIEW_AGGR(FORMATWIKI('Summary' , " \n")) AS 'Summary',
CONCAT_VIEW_AGGR('Due') AS 'Due',
CONCAT_VIEW_AGGR('Start Date') AS 'Start Date',
CONCAT_VIEW_AGGR(FORMATWIKI('Status' , " \n")) AS 'Status'
FROM T*
GROUP BY T1.'Fix Version/S' 

2 votes
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 20, 2023

Hi @Janice ,

Please add some custom tags for us:

  • table-filter-and-charts
  • table-filter
  • table-transformer

 Thank you!

2 votes
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 20, 2023

Hi @Aurélien Thazet ,

We can suggest the following solution for your case:

Wed 6-1.png

SELECT
'Fix Version/S',
FORMATWIKI(SUM('Summary' + "\n")) AS 'Summary',
FORMATWIKI(SUM(FORMATDATE('Due', "dd M yy") + "\n")) AS 'Due',
FORMATWIKI(SUM(FORMATDATE('Start Date', "dd M yy") + "\n")) AS 'Start Date',
FORMATWIKI(SUM('Status' + "\n")) AS 'Status'
FROM T*
GROUP BY T1.'Fix Version/S'

Wed 6-2.png

Hope it helps your case.

1 vote
Aurélien Thazet December 21, 2023

There was a typo issue indeed. It's work like a charm now!

 

Thank you for your help ! 

0 votes
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 21, 2023

Can you share the macro preview with the only 'Fix Version/S' column on display? I mean without any grouping and other columns, just with a simple query as

SELECT T1.'Fix Version/S' FROM T1

The name of the column should be chosen from the autocomplete menu (type in T1. and wait for the options to choose from).

0 votes
Aurélien Thazet December 21, 2023

Thanks for your help.

 

I've managed to get the dates to display correctly. However, the problem with the Fix Version/S field is still there. I think the syntax is correct because:

  • When I use the default SQL query, it displays correctly.
  • When I try another syntax (like "Fix versions", "Fix Version", ...), the completion helper displays an error message indicating that the field I'm looking for does not exist.
0 votes
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 20, 2023

Try to use the autocomplete option while typing in your SQL query: type T1. and wait for the options, then choose the required column name from the dropdown list.

Maybe your "Fix Version/S" field comes from the Jira not with this specific syntax.

Do you see this column in the Table Transformer macro preview with the default SQL query "SELECT * FROM T*"?

If yes, then type in "SELECT T1.'Fix Version/S' FROM T1" and check the macro preview once more.

What concerns the first suggestion, I'm not sure what is happening and what specific dates are not converted properly. The query takes the dates coming from Jira and concats them using a specific pattern that you type in the FORMATDATE function.

You may go to the Table Transformer macro settings and set the Date Format there that corresponds your initial date format that you see in the Table transformer macro with the default "SELECT * FROM T*" query.

What concerns the second suggestion, you may also add the " \n" separator to the query:

CONCAT_VIEW_AGGR(FORMATWIKI('Due' , " \n")) AS 'Due',
CONCAT_VIEW_AGGR(FORMATWIKI('Start Date' , " \n")) AS 'Start Date',

You may also share the previews here or via our support portal to elaborate more.

0 votes
Aurélien Thazet December 20, 2023

Hello, 

 

Thank you for your quick response. I've tried both solutions and I'm facing problems with each of them:

  • First suggestion
    • Doesn't show any value in Fix Version/S field
    • Some dates in Due Date and Start Date column show "NaN undefined NaN" while I've set date in Jira
  • Second suggestion
    • Doesn't show any value in Fix Version/S field
    • Dates aren't displayed per row

 

Many thanks,

Aurélien

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events