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

Scott Gillespie_ibixpx

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

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.