nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Scott Gillespie_ibixpx

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

Accepted answers

Katerina Rudkovskaya _Stiltsoft_

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.

All comments

Katerina Rudkovskaya _Stiltsoft_

Scott Gillespie_ibixpx

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:

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

Scott Gillespie_ibixpx

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*

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer