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

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.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

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

Accepted answers

All comments

Stiltsoft support

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.

Stiltsoft support

Hi @Janice ,

Please add some custom tags for us:

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

Thank you!

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'

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

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

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

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

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

Thank you for your help !

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer