Transform Jira issue table with Table Transformer

[Aurélien Thazet]

Hello, 

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

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:

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

[Stiltsoft support]

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' 

[Stiltsoft support]

Hi @Janice ,

Please add some custom tags for us:

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

 Thank you!

[Stiltsoft support]

Hi @Aurélien Thazet ,

We can suggest the following solution for your case:

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'

Hope it helps your case.

[Aurélien Thazet]

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

Thank you for your help ! 

[Stiltsoft support]

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

[Aurélien Thazet]

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.

[Stiltsoft support]

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.

[Aurélien Thazet]

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