Show a roll up table of data from tables on children pages

[Jodi Chalupa]

Here's the scenario:

• 1 to many child pages for a growing number of projects.  Each child page has a table with these columns: project name, date, status
• parent page that will display each child page's column data. The parent page has the same columns: project name, date, status
• Since the number of rows on the parent page is unknown(more projects keep getting added), I need to key off of the project name and pull the updated values of date and status from the child pages.

Excerpt macros dont seem to be able to capture the row from the child page,only one value from one cell.  I'm trying to use the Table Plus macro but not having much luck there.  Any suggestions?  Any examples?

[Andrey Khaneev _StiltSoft_ · Accepted solution]

Hello Jodi,

Page Properties + Page Properties Report macro should solve your problem.

[John Hall]

It sure would be nice if the "Table Data" and "Page Properties" macros worked together.  We'd like to use the: "Page Properties" and "Page Properties Report" to generate summary reports, but we want the information to have much better hygiene provided by the "Table Data" and associated macros.

[Natalie Paramonova _Stiltsoft_]

Hi Jodi,

If you need to pull tables with more than one data row (Page Properties Report pulls only one data row from Page Properties), you can also use the combination of the Table Excerpt Include and Table Transformer macro. Table Excerpt Include provides an ability to pull any data (any size tables) from child pages, from specific pages, from pages by labels. Table Transformer merges all the tables in one.

[sami qaiser]

Hi Natalie,

This Worked for me. is it possible to merge two rows with same data.

I have 2 cols one for date and other for notes. I want to merge rows with same date.

Any suggestion on that?

[Natalie Paramonova _Stiltsoft_]

Hi Sami,

You can try to use the following query:

SELECT 'Date', SUM('Notes' + " ") AS 'Notes' FROM (SELECT * FROM T*)
GROUP BY 'Date'
ORDER BY 'Date'

If the option doesn't resolve your use case, please give me more details.

[sami qaiser]

Thank you Natalie,
This worked great. I am trying to display all the notes in new line as they are in the original notes but they appear to be in one single line. I tried CHAR[10] in Sql and \n but didn't worked. Any suggestion on that?

[sami qaiser]

or is it possible to get the innerHTML in the table cell?

Thanks in advance

[Natalie Paramonova _Stiltsoft_]

Hi Sami,

Unfortunately, there are no options to resolve this issue for now. We'll try to fix it shortly. I'll keep you posted on it.

[sami qaiser]

Thank you for the support

[Aisya Roesley]

Hi Natalie

is the table transformer a paid or free macro?

[Natalie Paramonova _Stiltsoft_]

Hi Aisya,

The Table Transformer macro is included in a paid app - Table Filter and Charts for Confluence.

[Aisya Roesley]

Hmmm I can find the filter and chart but not the transformer. I’m currently working on 6.12.4 - could that be the reason?

[Natalie Paramonova _Stiltsoft_]

Table Transformer is available starting from version 5.0.0 of the app. This version is compatible with Confluence 6.12.4. Could you please check what version of the app is installed?

[Aisya Roesley]

Silly question but how do I check that? 

[Natalie Paramonova _Stiltsoft_]

You can find version in the Administration panel. You can ask your Administrator to check the version and to update the app if needed.

[Christoph Marschall]

Hi Natalie,

Sorry for dragging this old topic to the surface again.

Above you mentioned "Unfortunately, there are no options to resolve this issue for now. We'll try to fix it shortly. I'll keep you posted on it."

Is this related to the issue of linebreaks getting lost when using the table transformer macro?

Because this is exactly the issue I'm currently struggling with. Is there any solution/workaround to solve this issue already?

(In all attempts I made to process the content of a table using SQL-query, the linebreaks are already missing in the string I'm trying to process)

Thanks in Advance

Christoph

[Natalie Paramonova _Stiltsoft_]

Hi Christoph,

Unfortunately, we haven't found the solution after investigating the issue. But I've found a workaround. I'll describe it in the previous example with meeting notes.

1. Merge all the tables with a simple query: SELECT * FROM *

2. Wrap the Table Transformer macro with the Pivot Table macro.

3. Aggregate data by Date and use the Literal operation type. 

4. Configure the Pivot Table macro: hide controls, totals, and the source table; set an appropriate Literal value delimiter.

Then you can rename the Literal of Notes column for a better view.

Please let me know if it helps.

[Christoph Marschall]

Hi Natalie,

Many thanks for your detailed explanation. Unfortunately this workaround doesn't help in my case.

I don't want to hijack this topic, but let me briefly summarize my issue:

I would need to process a multiline table cell within the SQL-query of Table Transformer, as I need to split each line into a separate cell.

This split works well with any kind of delimiter EXCEPT linebreak.

I spent hours trying to figure out what's the problem, until I found that the linebreak is apparently lost as soon as I "touch" the content of the cell in any way.

(actually even renaming the column during SQL SELECT i.e. " T1.'Items' as 'Renamed' " seems to cause the linebreak to be lost)

My hope was that there is some workaround I could use within the SQL query to replace the linebreak early-on with another delimiter that's not lost, so I can then process it normally a few lines later (I also tried SQL REPLACE to replace \n with another char but failed there as well).

Just for illustration, here's an example:

Table:

Table Transformer SQL-Query

SEARCH /
AS @a
EX('Items'->split("x"))
/
RETURN(@a->Title as Title, _ as Items)
FROM T1;

The query will split the "Items" into separate rows, which works great for other delimiters (like "x" in above example).

Unfortunately I don't have "x" in my actual table, I only have "\n" as delimiter...

--> Is there any SQL-command I might have missed which could "catch and replace" the \n into something else before the character gets lost?

Many thanks again

Christoph

[Natalie Paramonova _Stiltsoft_]

Hi @Christoph Marschall ,

Thank you for the detailed explanation. I talked to our development team. Unfortunately, there is no ability to work with '\n' delimiters or line breaks for now. I'm sorry, in your case, we can't recommend any workaround.

[Christoph Marschall]

Hi @Natalie Paramonova _Stiltsoft_ ,

I see. Will have to use a different tool then.

Thanks anyway for checking!

Christoph

[Katie L]

Sorry to again hijack this question.

I have a problem similar to @Christoph Marschall 's comment.

I have a bunch of Risks and their Linked Issues that I'd like to separate them into individual rows. For example:

Original table:

Risk	Linked Issues
R1	C1-1,C1-2,C1-3
R2	C1-4,C1-5,C1-6
R3	C1-1,C1-3,C1-5

 I'd like the SQL to transform the table into:

Risk	Linked Issues
R1	C1-1
R1	C1-2
R1	C1-3
R2	C1-4
R2	C1-5
R2	C1-6
R3	C1-1
R3	C1-3
R3	C1-5

The Linked Issues have "," (comma) as their delimiter.

I have tried Pivot table but it doesn't work in this case.

@Natalie Paramonova _Stiltsoft_ and Christopher, do you have any ideas on how I can write the SQL for this case?

Thank you so much really appreciate it.

[Katerina Kovriga _Stiltsoft_]

Hi @Katie L ,

Please check this recent question - the last answer given by me on the 17th of September. The first three screenshots relate to your case - a similar table wrapped into the Pivot Table macro (through the Table Toolbox macro cause it is Cloud and we had nested macros) and the two screenshots of the Pivot Table macro settings.

If the Pivot Table still freezes your page, please raise a support ticket here. We'll try to investigate the issue. As for now we couldn't reproduce it for our test instance.

[Robert Reiner _smartics_]

Another approach besides Page Properties Macro and Page Properties Report Macro would be a macro that merges tables from other pages. 

Inside this table merger macro you could use the Excerpt Macro and Excerpt Include Macro to bring one piece of text (that is: the whole table) per page on the parent page. For multiple transclusions from one page or multiple pages you would need a third-party macro that supports multi-excerpt (I assume that adding a new Excerpt Macro for each new page is not feasible).

I'm not sure if this solution approach meets your third point (which I do not fully understand). But here we go ...

The commercial projecddoc Toolbox (available on the Atlassian Marketplace for Confluence server) provides macros to do this:

• Use Section Macro or Content Marker Macro to define the tables you need to transclude from another page
• Use the Transclude Documents Macro (supports multi-excerpt from one or more pages) to select the (child) pages by a query you want to transclude from
• Put the transclusion macro(s) in the Table Merger Macro

If you have a large number of pages to transclude from and a large number of tables you need to merge you'll need to check your performance requirements since the transclusion and merging takes place at request time.

Biased Alert: I'm one of the authors of the projectdoc Toolbox.

[John Corwith]

How about putting your data in an Excel spreadsheet? Each child page gets it's own worksheet. The parent page gets the combined data off a worksheet in the same file that combines all the data. Specific worksheets (pages) in the Excel file can be specified in the Office Excel macro on each page