Conditional Row Formatting for JQL Table displayed in Confluence

[Tonja Davis]

Hello,

We have a JQL table that we use to pull our testing effort tickets into an executive dashboard. 

I would like to apply conditional formatting to a row based on a value found in Labels. For instance, If I have "Good" in Labels, I want the row to be colored green, "Warning" I want yellow, and "Bad" I want red. 

I have tried with table transformer using SQL and I cannot get it to work. 

Any suggestions? 

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hello @Tonja Davis ,

Please wrap your source table with Table Transformer and use the SQL query like this:

SELECT
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Labels' + "{cell}")
AS 'Lables',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'column 2' + "{cell}")
AS 'column 2',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'column 3' + "{cell}")
AS 'column 3'
FROM T*

Here are other examples of advanced table cells formatting you may find useful for your cases. 

Katerina
Stiltsoft

[Tonja Davis]

My problem is I am not wrapping a Wikitable. I'm wrapping another macro (Advanced JQL Table) in Table Transformer and trying to parse information from Advanced JQL Table as conditional formatting for the cells 

does that make sense? 

I've expanded the SQL you gave above to include all of the cells that are displayed but I get an error when implementing the SQL even though it's formatted properly. The table displays but none of the cells are properly colored

[Katerina Rudkovskaya _Stiltsoft_]

Yes, our app works with both manually created and macros generated tables.

It seems that we've faced a bug. I'll release the fix for it soon.

Now, please try to replace FROM T* to FROM T1

Would you please confirm that it works for you?

Katerina
Stiltsoft

[Tonja Davis]

Thank you for your assistance. I changed FROM t* to FROM T1 and the error is the same. it doesn't like "FROM" at the end. 

I'll include the query I built from yours here. it's rather long but it's a ticket table so, it is what it is"

SELECT
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Labels' + "{cell}")
AS 'Labels',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Key' + "{cell}")
AS 'Key',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Summary' + "{cell}")
AS 'Summary',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Created' + "{cell}")
AS 'Created',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Updated' + "{cell}")
AS 'Updated',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Assignee' + "{cell}")
AS 'Assignee',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Reporter' + "{cell}")
AS 'Reporter',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Status' + "{cell}")
AS 'Status',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Resolution' + "{cell}")
AS 'Resolution',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
FROM T1

[Katerina Rudkovskaya _Stiltsoft_]

Note that you are using this part of the SQL query twice:

FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END

Please try to delete one of them.

.................

Katerina
Stiltsoft

[Tonja Davis]

Thank you for your help. I did as suggested and - on the upside - the error is gone. 

On the downside, The table is rearranged (minor issue that is not critical) but the cells are also not colored. For transparency, I deleted the one at the bottom (right before "FROM T1" I will try again deleting the one at the top

[Tonja Davis]

deleting the one at the top results in an error. the one at the bottom is the best one to delete.

[Katerina Rudkovskaya _Stiltsoft_]

Yes, it's because the second part is not the full written part of the query.

I use this query and colors apply:

SELECT
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Key' + "{cell}")
AS 'Key',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Summary' + "{cell}")
AS 'Summary',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Created' + "{cell}")
AS 'Created',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Updated' + "{cell}")
AS 'Updated',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Assignee' + "{cell}")
AS 'Assignee',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Reporter' + "{cell}")
AS 'Reporter',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Status' + "{cell}")
AS 'Status',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Resolution' + "{cell}")
AS 'Resolution',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Labels'="Good" THEN "green"
WHEN T1.'Labels'="Warning" THEN "yellow"
WHEN T1.'Labels'="Bad" THEN "red"
ELSE T1.'Labels'
END
+ "}" + T1.'Labels' + "{cell}")
AS 'Labels'
FROM T1

Please copy and paste it into Table Transfromer.

If it doesn't work, please raise the ticket here.
Please attach the page storage format and tell us what version of the app and Confluence you're using.

Katerina
Stiltsoft

[Tonja Davis]

thank you for all your help. I've opened a ticket. 

[Kishan Sharma]

Hi @Tonja Davis Welcome to the community. Please check this thread on a similar use case.

[Tonja Davis]

Hello Kishan, 

I did check that thread, unfortunately, it does not address my concern. there is an expectation that I have a WIKI Table. I do not. I am using Advanced Tables - JQL Table and trying to wrap table transformer around it. That is where my issue lies.