How to use Table Excerpt Include / Table Transformer to combine Pivot tables with different layouts

[Scott Gillespie]

Using Confluence 7.19.6 

I am building a dashboard page to combine Jira metrics for completed, incomplete and changed stories from multiple engagement teams.  Each team creates a page using Jira Sprint Report data pasted into tables.  Table Transformer is used to merge those tables, and a Pivot table is created to summary counts of stories and story points (see images).

 

Table Excerpt Include is used in the Dashboard page to pull in multiple pivots like above, nested in a Table Transformer to generate final table for metrics reporting.

If the Pivot table fields are consistent, this works perfectly.  However, the Pivot columns vary because some teams may not have incomplete or removed stories, for example, or have changed stories, like in the 2nd table above. 

Table  Excerpt Include cannot summarize all these pivots into a single report table due to the Pivot field inconsistency, and so Table Transformer cannot identify fields properly.

I added dummy items to the individual engagement pages, and that works in making the layouts consistent. However, the dummy items are picked up as actual records, so the counts are wrong.

Is there a way to use Table Transformer with Table Excerpt Include to create "dummy" columns where real data is missing?  For example, using the table samples above, I'd need the first table to have an empty "Change / Completed" column for Stories and one for Story Points to align with the second table.

[Stiltsoft support · Accepted solution]

Hi @Scott Gillespie ,

The workaround may be as following: use an additional Table Transformer macro between your Pivot Table macro and Table Excerpt macro for every source table that you need to collect via the Table Excerpt Include macro.

With this extra Table Transformer macro we'll recreate all the columns even if they don't exist in the specific pivot table at the time.

Please, see an example below:

Here the "Old long headers" stand for the long one-row headers that you'll get after wrapping your pivot table in the Table Transformer macro. You'll need to rename them in a short way manually and use strictly the same names for every table (so that the Table Excerpt Include macro will be able to generate a master report later).

So, let's pretend that the "Old long header 1" column is always present and "Old long header 2", "Old long header 3", "Old long header 4" columns may be or may be not present in the table.

For example, there is no the "Old long header 3" column on the screenshot.

But the following SQL query will help us:

SELECT
'Old long header 1' AS 'New header 1',
IFNULL('Old long header 2') AS 'New header 2',
IFNULL('Old long header 3') AS 'New header 3',
IFNULL('Old long header 4') AS 'New header 4'
FROM T*

As you can see, the missing column is now present.

Hope it will help your case.

[Scott Gillespie]

This works perfectly!  Thank you so much for this quick answer.  I suspected this was the right approach, but I didn't have enough SQL knowledge to hit on the right solution.

[Stiltsoft support]

And for other people who may come across this question: please take a look a these blog posts where we show how to create combined reports step-by-step

• How to Aggregate and Reuse Data From Multiple Confluence Tables
• How to Create Reports from Multiple Confluence Tables

Hope it helps your cases!

[Miriam Hopton]

I'm here to comment I LOVE your app and it really helped transform our tables in Confluence.

[Stiltsoft support]

Thank you! :)

I'll pass your comment to the team.