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

[Scott Gillespie]

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.

[Katerina Rudkovskaya [Stiltsoft] · Accepted solution]

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.

[Scott Gillespie]

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?

[Katerina Rudkovskaya [Stiltsoft]]

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:



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*

[Scott Gillespie]

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*

[Katerina Rudkovskaya [Stiltsoft]]

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.

[Scott Gillespie]

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.