Table Transformer with Jira Status merged

[Aurélien Thazet]

Hello everyone, 

I'm using Jira Issues to retrieve some issues of a Jira Project and create a schedule in Confluence thanks to Table Transformer. Here is the current display I have:

Here is my current SQL request:

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

I would like to display only one value per Version line in the Status column. For example with the first version of the table: I want to display a status message named "COMPLETED" in the status cell because each issue status for this version is Done, Failed or Not applicable.

Any advice on how I can do this can of things ?

[Stiltsoft support · Accepted solution]

Hi @Aurélien Thazet ,

You may try to insert another Table Transformer macro on top and use the CASE WHEN statement: https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-UsingIf/Thenconstruct

For example, if you have "To do", "In progress", "Done", "Failed" or "Not applicable" statuses, you may start with the checking if the cell contains the "To do" status. If yes, then you put the "Needs work" status.

Then you check for the "In progress" status and set the "Current work" status.

Other statuses that mean "Completed" go to the ELSE part.

SELECT *,
CASE WHEN 'Status' LIKE "%To do%"
THEN "Needs work"
WHEN 'Status' LIKE "%In progress%"
THEN "Current work"
ELSE "Done"
END
AS 'Overall Status'
FROM T1

Hope it helps your case.

[Stiltsoft support]

Hi @Aurélien Thazet ,

An update from our developers: if all Done, Failed or Not applicable make COMPLETED and any other status makes INCOMPLETE, you may combine your first query and the second query in one Table Transformer macro:

SELECT T1.'Version',
CONCAT_VIEW_AGGR(FORMATWIKI('Date' , " \n")) AS 'Date',
CONCAT_VIEW_AGGR(FORMATWIKI('Status' , " \n")) AS 'Status',
IF(SUM(
IF('Status' = "Done" OR 'Status' = "Fail" OR 'Status' = "Not applicable", 0, 1)) = 0,
"COMPLETED", "INCOMPLETE") AS 'Overall Status'
FROM T*
GROUP BY T1.'Version'

The variant

SELECT T1.'Version',
CONCAT_VIEW_AGGR(FORMATWIKI('Date' , " \n")) AS 'Date',
CONCAT_VIEW_AGGR(FORMATWIKI('Status' , " \n")) AS 'Status',
IF(SUM(
IF('Status' IN ("Done", "Fail", "Not applicable")), 0, 1) = 0,
"COMPLETED", "INCOMPLETE") AS 'Overall Status'
FROM T*
GROUP BY T1.'Version'

is even slightly shorter but gives the same result (the IF function was rewritten in a more elegant way).

[Aurélien Thazet]

Hello, 

I spent a whole day trying to find a solution. It works like a charm, thank you very much!

[Stiltsoft support]

Glad it helped your case!

If you have any questions regarding our macros (not Confluence in general but the macros provided by the Table Filter, Charts & Spreadsheets for Confluence app), you may refer directly to our support portal.

It is confidential and we can't miss any tickets there (in the Atlassian Community we manually search for relevant posts but maybe some of them are missed).