Using Table Transformer SQL to summarize totals from table excerpts

Scott Gillespie March 28, 2023

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'

 

EngagementDeliveryKeyStory Points
Project 1CompleteD-123442
Project 1CompleteD-123453
Project 1CompleteD-123442
Project 1CompleteD-123453
Total 410
    
EngagementDeliveryKeyStory Points
Project 1IncompleteD-241623
Total 13
    
EngagementDeliveryKeyStory Points
Project 1RemovedD-234555
Project 1RemovedD-234573
Total 28

 

EngagementComplete StoriesIncomplete StoriesRemoved StoriesComplete Story PointsIncomplete Story PointsRemoved Story Points
Project14121038
Project nnnnnnn

2 answers

1 accepted

3 votes
Answer accepted
Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 29, 2023

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.

Scott Gillespie April 3, 2023

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.

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.jpgI'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_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 3, 2023

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 April 3, 2023

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?

Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 4, 2023

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.

Like Stiltsoft support likes this
Scott Gillespie April 4, 2023

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 April 4, 2023

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

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 4, 2023

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 April 4, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 5, 2023

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.

0 votes
Scott Gillespie April 3, 2023

- Deleted incorrect posting - 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events