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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,456,658
Community Members
 
Community Events
176
Community Groups

Conditional formatting - Background color - Simple SQL help

Edited

Hi!

I have a pretty simple use-case, related to the below type of tables: have text centered for all values and background color green when the value is "Yes".

I spent a long type looking into FORMATWIKI but without success. Thanks!

 

 GardenGaragePool
House1YesNoNo
House2NoYesNo
House3YesNoYes

1 answer

Hi @Meta Box ,

I assume that you are talking about our Table Filter and Charts for Confluence app and its Table Transformer macro?

If this is the case, please try the following SQL query:

SELECT T1.'__',
CASE WHEN T1.'Garden' = "Yes"
THEN
FORMATWIKI("{cell:bgColor=#00CC00|align=center}" + T1.'Garden' + "{cell}")
ELSE
FORMATWIKI("{cell:align=center}" + T1.'Garden' + "{cell}")
END
AS 'Garden'
FROM T1

Thu 9-1.png

Repeat the CASE WHEN abstract for every column that you need to format.

Here is our documentation regarding conditional formatting for you to check.

Also keep in mind the following order of attributes: bgColor, textColor, align, width.

Thank you, it worked perfectly!!!

One more question, what is the code if I want to put a specific background color for empty cell?

Thanks again

Hi @Meta Box ,

The FORMATWIKI function works if your cells are not empty. So, you need to put some characters (for example, "-") into them manually or using an additional internal SELECT for your SQL query.

For example, if my cells are "Yes", "No", " " (empty) and I want to color "Yes" as green and empty cells as red, I can use the following SQL query:

SELECT '__',
CASE WHEN 'Garden' = "Yes"
THEN
FORMATWIKI("{cell:bgColor=#00CC00|align=center}" + 'Garden' + "{cell}")
WHEN 'Garden' = "-"
THEN
FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Garden' + "{cell}")
ELSE
FORMATWIKI("{cell:align=center}" + 'Garden' + "{cell}")
END
AS 'Garden'
FROM (SELECT '__',
CASE WHEN 'Garden' IS NULL
THEN
"-"
ELSE
'Garden'
END
AS 'Garden' FROM T*)

Fri 12-1.png

Here we firstly insert "-" into empty cells and only then color them.

Hi @Katerina Kovriga _Stiltsoft_ - I am trying to use this for my case, but I get this error:

TypeError: Cannot read properties of undefined (reading 'Executive Summary')

Here is my code:

SELECT 'Executive Summary',
CASE
   WHEN INSTR('Executive Summary', "R&D Status - 100")
       THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + T1.'Executive Summary' + "{cell}")
   WHEN 'Executive Summary' = "-"
       THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + T1.'Executive Summary' + "{cell}")
   ELSE
       FORMATWIKI("{cell:bgColor=#FFE784}" + T1.'Executive Summary' + "{cell}")
   END
AS 'Executive Summary'

FROM (SELECT 'Executive Summary',
   CASE WHEN 'Executive Summary' IS NULL
   THEN
      "-"
    ELSE
      'Executive Summary'
   END
AS 'Exective Summary' FROM T*)

Hi @Meta Box ,
We’ve checked your code and here are the tips:
  • Remove the “T1.” indexes for the external SELECT. As you also use an internal SELECT, you can’t refer to your table as “T1.” here.
  • Pay attention to the typo in the last line of your code: not “AS 'Exective Summary' FROM T*)” but “AS 'Executive Summary' FROM T*)”.
  • You may also remove referrers to the 'Executive Summary' columns after both SELECTS. It is not a mistake, but there is no any sense: you select the column, then transform it and show it again with the same name. Your data is just overwtitten.
So, here is how it looks for us:
Thu 1-1.png
SELECT
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)
Thu 1-2.png
Hope it helps your case.
Like # people like this

@Natalie Paramonova [Stiltsoft] - thank you for the response, I am able to get this to work and understand the mistakes. 

However, I am still struggling with SQL nesting issue. If I want to add more columns from T* to the table, how to add to the SELECT statement?

For example, I want to add the Jira Key. How do I add it as the first column and the 'Executive Summary' as the second column?

SELECT
'Key' AS 'Jira Key'
FROM T*

Hi, to obtain several specific columns, you need to list them using commas:

SELECT 'Column 1', 'Column 2'
FROM ...

If you want to list all the columns from the original table, use asterisk:

SELECT *
FROM ...

So, for your original query it will be smth like that:

SELECT 'Column 1' AS 'New name 1', 'Column 2' AS 'New name 2',
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT 'Column 1', 'Column 2',
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)

Hope it helps your case.

Like # people like this

Thank you very much! Now it is working as I expect!

I realize now that since I am specifying the 'Executive Summary' column in the FROM statement, that I must also list ALL of the columns defined in the SELECT statement. 

Basically, the second SELECT statement is nested in the FROM statement for the first SELECT. This was what I was missing.

So the logical syntax for a table with 2 columns to apply conditional formatting to 1 of the columns which contains empty cell would be:  

SELECT
'Column 1' AS 'New name 1',
CASE [code to format 'Column 2'] END AS 'Column 2'
FROM
(SELECT
'Column 1',
CASE [code to change value of 'Column 2'] END AS 'Column 2'
FROM T*)

Thank you again!

Like Stiltsoft support likes this

@Natalie Paramonova [Stiltsoft] - I have another basic formatting question using FORMATWIKI:

Is this correct syntax to apply 2 formats to the same column cells?

FORMATWIKI("{cell:bgColor=#FA7E70|width=250px}" + 'Summary'+ "{cell}") AS 'Summary'

This does not work for me.

Hi @Keith Lanier,

The code is correct, and it works fine for me. For example, regarding your previous query it will be looking as:

SELECT
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D|width=500px}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70|width=500px}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784|width=500px}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)

Tue 9-1.png

You may check the names of your columns for typos (in the table and in the query itself), sometimes it happens.

Like # people like this

Thank you! It is working for me now. 

I am not sure if I had a hidden character or something that was causing it not to work initially.

@Katerina Rudkovskaya _Stiltsoft_  - now I have another request:

How can combine column "Key" with column "Summary" and keep the Key URL hyperlink in the combined column?

Here are the 2 columns which I want to combine:

'Key' AS 'Key'
, FORMATWIKI("{cell:width=15%}" + 'Summary' + "{cell}") AS 'Capabilities'

Here is what I tried:

FORMATWIKI("{cell:width=15%}" + 'Summary' + "\n" + 'Key' + "{cell}") AS 'New'

But, I want to keep the URL for the 'Key':

 

Screenshot 2022-12-13 135558.png

Hi @Keith Lanier,

Try to replace your pluses by commas, it preserves initial text formatting:

FORMATWIKI("{cell:width=15%}", 'Summary', "\n", 'Key', "{cell}")

Like # people like this

Thanks again - that is a great tip!

For future reference, where can I find a user guide that documents all the syntax?

I've searched and can only fine more "how to" examples, but no real syntax user guide for all the options/formats/functions.

Hi @Keith Lanier,

We have a rather big section within our documentation related to the Table Transformer macro:

Hope it helps.

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events