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; } } }

Using Table Transformer SQL to summarize totals from table excerpts

Scott Gillespie_ibixpx

Confluence Cloud

I have 3 Confluence "sprint performance" tables with data pasted from Jira for sprint stories that are complete, incomplete, or removed, respectively. These are for multiple engagements, so there are multiple sets of these 3 tables, 1 set per engagement.

I am creating a dashboard page that rolls up all this information using Table Excerpt Include. I then use Table Transformer to join these tables using the "Engagement" field.

Dashboard goal is to show one row for each engagement, with the total count of stories (based on Key field), and sum of Story Points. I am struggling with the SQL to pull only the total values for each engagement's "sprint performance" table.

A segment of my SQL, and one set of sprint tables, and desired dashboard table are below. I know I can use the CASE statement to locate the total line for each table, but I don't know how to pick up the Key count total and the Story Points sum and put on one row with the engagement name.

I've struggled with this for too long, so time to call in the experts. Thanks in advance for any suggestions.

SELECT
T1.'Engagement',
CASE
WHEN T1.'Engagement' = "Total"
THEN ???
END
AS 'Complete Stories',

CASE
WHEN T2.'Engagement' = "Total"
THEN ???
END
AS 'Incomplete Stories',

CASE
WHEN T3.'Engagement' = "Total"
THEN ???
END
AS 'Removed Stories',

FROM T1 OUTER JOIN T* ON T1.'Engagement' = T*.'Engagement'

Engagement	Delivery	Key	Story Points
Project 1	Complete	D-12344	2
Project 1	Complete	D-12345	3
Project 1	Complete	D-12344	2
Project 1	Complete	D-12345	3
Total		4	10

Engagement	Delivery	Key	Story Points
Project 1	Incomplete	D-24162	3
Total		1	3

Engagement	Delivery	Key	Story Points
Project 1	Removed	D-23455	5
Project 1	Removed	D-23457	3
Total		2	8

Engagement	Complete Stories	Incomplete Stories	Removed Stories	Complete Story Points	Incomplete Story Points	Removed Story Points
Project1	4	1	2	10	3	8
Project n	n	n	n	n	n	n

Accepted answers

Katerina Rudkovskaya _Stiltsoft_

Hi @Scott Gillespie,

We can suggest wrapping your original tables into the Table Excerpt macros before the Table Filter macro. Then you will collect them without “Totals” and your master report combined via the Table Excerpt Include macro (here you need to check the “Show as a report table” option) will look as following:

Tue 2-1.png

Then you wrap your Table Excerpt Include macro into the Pivot Table macro and aggregate your data:

Tue 2-2.png
Tue 2-3.png
Later you may wrap the Pivot Table macro in the Table Transformer macro and rename the columns if necessary.

Hope it helps your case.

All comments

Katerina Rudkovskaya _Stiltsoft_

Tue 2-2.png
Tue 2-3.png
Later you may wrap the Pivot Table macro in the Table Transformer macro and rename the columns if necessary.

Hope it helps your case.

Scott Gillespie_ibixpx

Katerina--Thank you very much--this is just what I needed!

But now, I have a peculiar error.

I merge two tables using Table Excerpt Include wrapped in Table Transformer.
The above is wrapped in Pivot Table per your guidance.
The above is wrapped in a second Table Transformer to provide a cleaner output.

Annotation 2023-04-03 113453.jpg

I can preview all the data in every macro above. However, when I update the entire page, I get these errors:

Annotation 2023-04-03 113638.jpg I'm at a loss to understand why these errors appear when the edit mode previews all work correctly. I thought there might be some refresh issue with the different pages, but this error persists. Any suggestions?

Katerina Rudkovskaya _Stiltsoft_

Hi @Scott Gillespie,

Seems that the Table Excerpt Include macros don’t return the tables properly (red errors). And the Table Transformer #1, Pivot Table and Table Transformer #2 just “complain” that there are no tables for them to work with (blue information blocks).

I can suggest that it may be so if smth was changed in the source tables wrapped in the Table Excerpt macros (column names can have typos, for example). The preview is correct cause you get it from cache but after the page publishing everything goes wrong.
So please clear your browser cache and check all the source tables wrapped into the Table Excerpt macros.

If it doesn’t help, then refer to our support. Please attach the page storage format (upper right corner of the page -> menu … - View storage format) of the problem page and all the pages containing your Table Excerpts. We’ll recreate the case and investigate the issue.

And beforehand please check if you are really on Cloud (from the screenshot I can see that you may use the old Legacy Cloud editor or (and that is more likely) you are on Server/Data Center). If you are on Cloud, then your version of the app is always up to date. But if you are on Server/Data Center, you may need to update – the current version is 9.8.2.

Scott Gillespie_ibixpx

- Deleted incorrect posting -

Scott Gillespie_ibixpx

Thanks again, Katerina. I was told we are on Confluence Cloud, but started looking based on your comment and this is what appears in the footer of my pages:

I assume that means we're on Server/Data Center and more than 2 full versions behind--is that correct?

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

group

table

table-filter

table-filter-and-charts

table-transformer

aggregate

pivot-table