It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

 

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?

4 answers

1 accepted

Hello Jodi,

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

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:

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.

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.

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?

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'

2019-07-04_13h18_06.png

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

Like sami qaiser likes this

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?

 

Query Test.PNG

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

 

Thanks in advance

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.

Like sami qaiser likes this

Thank you for the support

Hi Natalie

is the table transformer a paid or free macro?

Hi Aisya,

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

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?

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?

Silly question but how do I check that? 

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

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

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 *

2020-04-16_10h40_00.png

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

2020-04-16_10h39_40.png

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

2020-04-16_10h40_19.png

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

2020-04-16_10h41_04.png

2020-04-16_10h40_39.png

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

Please let me know if it helps.

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:

table2.PNG

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

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.

Hi @Natalie Paramonova [StiltSoft] ,

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

Thanks anyway for checking!

Christoph

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

How To: get the most out of the new Confluence navigation and Home

Hi Community, Elaine again from Confluence Cloud Product Management. Most of you have used the new Confluence navigation and Home for some time by now. Not sure what changed or just want to lear...

314 views 2 9
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you