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,463,507
Community Members
 
Community Events
176
Community Groups

Can I use SQL to change cell formatting of calculated (sum, average, count, etc.) table rows?

I have a a series of pages with tables of sprint metrics, with an auto-calculated average row.  I am combining those with Table Excerpt Include, then using Table Transformer to flag #Stories Missed values more than 0 in red text using this SQL:

SELECT *,
FORMATWIKI("{cell:textColor=" +
    CASE
        WHEN '#Stories Missed' >0 THEN "Red"
        ELSE "Black"
    END
+ "}" + '#Stories Missed' + "{cell}")
AS '#Stories Missed',
FROM T*

This produces the table below.  The auto-calculated average rows' formatting with gray shading and bold text are overridden by the SQL.  Is there any way to exclude the calculated rows from the SQL formatting changes, or better still, leave the gray shading and bold formatting but make the average value red if > 0? 

I am very new to SQL coding, so I suspect this is possible but may not be so within the Confluence/Stiltsoft macros currently available.

Annotation 2023-01-10 163949.jpg

 

1 answer

Hi @Scott Gillespie,

Generally, to preserve the initial cell format (for example, bold or italic text, links, etc.), use commas instead of pluses. For example, your query may look like this:

SELECT *,
FORMATWIKI("{cell:textColor=",
CASE
WHEN '#Stories Missed' >0 THEN "Red"
ELSE "Black"
END
, "}" , '#Stories Missed' , "{cell}")
AS '#Stories Missed'
FROM T*

But in this specific case, I’m afraid, the FORMATWIKI function will still override the initial formatting. Your cells are formatted not manually but come from the previous macro: the different HTML format won’t be caught and preserved by the Table Transformer macro.

Thanks for this explanation.  Is it correct to infer that if I set up a table using a different macro, or rather manual formatting via SQL, that it would then be possible to have different formatting for base data and calculated cells?

Hi @Scott Gillespie,

I’m afraid you are not quite right here: the FORMATWIKI function with plusses overrides the initial cell formatting and ruins links, bold text, etc. It concerns manually created tables and tabular Confluence macros such as Jira Issues, Page Properties Report, etc.

But if you use commas, the initial cell formatting will be preserved.

When you use other macros (for example, the Table Filter macro) to calculate data, they add their “virtual” formatting (grey background) to make accent on the specific cells (these cells are also virtual, you don’t have total rows in your initial tables). Their contents is treated like plain text, there is no any html format to preserve. So, commas won’t be able to help here, your case is not possible to implement.

But if your screenshot is not full and you have additional columns in your table and count the Total row under some name, you may exclude these Total rows manually.
For example, your table may look like this:

Tue 15-1.png

Then you may use a more complex SQL query to apply formatting on the Total rows as well:

SELECT *,
CASE
WHEN 'Project name' = "Total"
THEN FORMATWIKI("{cell:bgColor=#F4F5F7|textColor=Black}", "*", '#Stories Missed', "*", "{cell}")
WHEN '#Stories Missed' > 1 AND 'Project name' NOT LIKE "Total"
THEN FORMATWIKI("{cell:textColor=Red}", '#Stories Missed', "{cell}")
WHEN '#Stories Missed' <= 1 AND 'Project name' NOT LIKE "Total"
THEN FORMATWIKI("{cell:textColor=Black}", '#Stories Missed', "{cell}")
END
AS '#Stories Missed'
FROM T*

Tue 15-2.png

Like Stiltsoft support likes this

Hi Katerina,

This is simply excellent--thank you so much!  You understood exactly what I was trying to do, and your code segment works perfectly! I updated it to account for my different cell text labels and different threshold value (>0).  I added another CASE statement to apply red text to another column (see SQL and screen shot below).

This is enough for my purposes, but as a learning exercise, I wanted to apply red bold text to the circled "Average" cells.  I couldn't get it to work, either because I'm making an error in the code, or because the Average values are "virtual" as you say--I am not clear if I can evaluate them, although it seems like I should be able to. 

SELECT *,

CASE
WHEN 'Engagement Page' = "Average"
THEN FORMATWIKI("{cell:bgColor=#F4F5F7|textColor=Black}", "*", '#Stories Missed', "*", "{cell}")
WHEN '#Stories Missed' > 0 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Red}", '#Stories Missed', "{cell}")
WHEN '#Stories Missed' <= 0 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Black}", '#Stories Missed', "{cell}")
END
AS '#Stories Missed',

CASE
WHEN 'Engagement Page' = "Average"
THEN FORMATWIKI("{cell:bgColor=#F4F5F7|textColor=Black}", "*", '% Velocity Difference', "*", "{cell}")
WHEN '% Velocity Difference' > 10 OR '% Velocity Difference' <-10 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Red}", '% Velocity Difference', "{cell}")
WHEN '% Velocity Difference' < 10 AND '% Velocity Difference' >-10 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Black}", '% Velocity Difference', "{cell}")
END
AS '% Velocity Difference'

FROM T*

Presentation2.jpg

Hi @Scott Gillespie,

To change the color of your "Average" numbers, check the corresponding parts of your query:

SELECT *,
CASE
WHEN 'Engagement Page' = "Average"
THEN FORMATWIKI("{cell:bgColor=#F4F5F7|textColor=Black}", "*", '#Stories Missed', "*", "{cell}")
WHEN '#Stories Missed' > 0 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Red}", '#Stories Missed', "{cell}")
WHEN '#Stories Missed' <= 0 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Black}", '#Stories Missed', "{cell}")
END
AS '#Stories Missed',
CASE
WHEN 'Engagement Page' = "Average"
THEN FORMATWIKI("{cell:bgColor=#F4F5F7|textColor=Black}", "*", '% Velocity Difference', "*", "{cell}")
WHEN '% Velocity Difference' > 10 OR '% Velocity Difference' <-10 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Red}", '% Velocity Difference', "{cell}")
WHEN '% Velocity Difference' < 10 AND '% Velocity Difference' >-10 AND 'Engagement Page' NOT LIKE "Average"
THEN FORMATWIKI("{cell:textColor=Black}", '% Velocity Difference', "{cell}")
END
AS '% Velocity Difference'
FROM T*

Here your text color is defined as "Black": textColor=Black. You may change it to "Red" or use any other color as a HEX code.

Like Stiltsoft support likes this

Thank you, again, Katerina.  This confirms the change.  Now I will add logic so the Average row values are red only if they fall into the same ranges as I use for the data rows.  Then this will be the ideal presentation for this page I've been building.

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events