I would like to merge and transpose tables that are used as an excerpt on other pages

[Michèle Page]

Hi there,

I would like to do the following and I'm not sure if the approach is even possible.

Context:

• We are managing several Data Definitions in Confluence
  (1 Data Definition = 1 Confluence Page)
• Every Data Definition has several Single Point of Contacts / Links
  • Link (which is the same for every page)
  • Manager (who is the same for every page)
  • Owner (who differ on each page)
  • Steward (who differ on each page)
• I would like to have a centralized table to manage the SPOCs and Links and displaying the relevant information on the Data Definition pages using the Table Excerpt Include macro

Example of the the tables on the centralized page with the current solution:

Information that is included in all the pages as it's the same for all of them:

Owned by	Link
Name Manager	Name

Information that is only relevant for the respective page

First row is only shown on the centralized page.

Second and third row will be shown on the respective page.

Link to Data Definition 1
Name Owner	Name Owner 1
Name Steward	Name Steward 1
Link to Data Definition 2
Name Owner	Name Owner 2
Name Steward	Name Steward 2
Link to Data Definition 3
Name Owner	Name Owner 3
Name Steward	Name Owner 3
...

Example of the table on the Data Definition pages (e.g., Data Definition 1):

Owned by	Link (always the same table)
Name Manager	Name (always the same table)
Name Owner	Name (Name Owner Data Definition 1)
Name Steward	Name (Name Steward Data Definition 1)

I found a hacky solution, that's kind of working, see below.

✨ What would be an even better solution is, if the centralized page would provide the following overview:

Owned by	Link
Manager	Name

Data Definition	Name Owner	Name Steward
Link to Data Definition 1	Name Owner 1	Name Steward 1
Link to Data Definition 2	Name Owner 2	Name Steward 2
Link to Data Definition 3	Name Owner 3	Name Steward 3
...

Now the hacky solution is as follows.

Centralized page

Table Transformer with the following SQL (all tables have to be listed individually for the SQL to work):

SELECT * FROM T1
UNION ALL
SELECT * FROM T2
UNION ALL
SELECT * FROM T3
UNION ALL
SELECT * FROM T4

As we have already many Data Definitions (and many more to come), my SQL goes up to T42.

Also, I always need to include a "placeholder" row in order to be able to display the Link.

Data Definition pages

Is there a better way to do this? Or is there even a way to display the centralized page as mentioned under ✨?

Thank you for your ideas.

[Daniel Berežnoi]

Hi Michèle,

This is Daniel from Stiltsoft, the creators of Table Filter, Charts, and Spreadsheets for Confluence. 

Could you please let me know whether you can add the links to the data definition right into the excerpts? If this is possible, then you can try the following setup.

The overview excerpt with common data is separated from the rest, as you wished. Using a single Table Transformer, it is possible to collect data from all excerpts into a single table. 

You see, the Table Transformer has an option to transpose the source tables.

By enabling this option, you can pivot the whole table, remove the unnecessary info and collect data from excerpts into the table you wish.

The result will look just as you wished.

Then, on the Data Definition pages, you can transpose source and result tables with Table Transformer, while removing the column with links, if you so wish.

Let me know if you have any questions.

Daniel

[Mia Tamm _Simpleasyty_]

Hi @Michèle Page 

Thanks a lot for sharing your use case — it’s really interesting. I’d like to better understand your goal before suggesting any alternative approach.

• One quick question that came to mind: do you actually want to keep those separate mini-tables on the centralized page, or is the real objective to just have one single unified overview table?
• If the mini-tables are important, could you share a bit more about the reason or the benefit you see in displaying them like that?

I’m asking because sometimes a simpler setup (with just one data source and one unified table) can make things easier to maintain and much less repetitive than managing so many excerpts and SQL unions.

Curious to hear how you imagine the “ideal” page should look — would it be more of a clean overview table, or do you also need those individual blocks preserved?

Thanks again for opening the discussion!

— Mia Tamm