Using Table Transformer SQL to summarize totals from table excerpts

[Scott Gillespie]

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

[Katerina Rudkovskaya [Stiltsoft] · Accepted solution]

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:



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

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]

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

But now, I have a peculiar error.  

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

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

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]

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:

• Powered by Atlassian Confluence 7.19.6 (ip-10-3-20-178.eu-central-1.compute.internal: 365f9bf8)

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

[Katerina Rudkovskaya [Stiltsoft]]

Yes, the "Powered by Atlassian Confluence 7.19.6" suggests that you are on Server/Data Center. But it's the version of your Confluence and not the Table Filter and Charts for Confluence app. Your Confluence administrator should be able to check the version of the add-on for you and update if required.

Meanwhile, try to check the source tables and clear the browser cache.

[Scott Gillespie]

I'll check with the admin and get back to you shortly.  I also confirmed the source tables haven't changed, and cleared my browser cache, but this didn't resolve the issue. 

This may just confirm what you already have said, but in case it helps, if I edit either Table Excerpt Include macro and check Output Cached Results, I get the same error messages in the preview.  The preview works fine if that setting is unchecked.

[Scott Gillespie]

Katerina, we have version 8.8.3 of Table Filter and Charts add-on.

[Stiltsoft support]

Hi @Scott Gillespie ,

Please update the app (the current version is 9.8.2) and check if the issue persists.

If nothing helps, 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.

[Scott Gillespie]

I'm checking with admins on plans for upgrading to 9.8.2, although I'm skeptical such an upgrade will happen soon.

Should I go ahead and submit the page storage format and related in, or will that be unacceptable since we're on an earlier release?

[Stiltsoft support]

Yes, you may still create a support request but specify the version that you are on and mention that it will take time to update.

[Scott Gillespie]

- Deleted incorrect posting -