SID-01680: Rolling up tables or referencing tables from other tables

Status:	Answered	TWiki version:	5.0.2	Perl version:
Category:		Server OS:		Last update:	12 years ago

I thought I've seen this discussed in the past, but my searching hasn't turned anything up...

I have a series of topics each with a table. I need to create a roll-up summary of the values in each of these other tables. I was thinking that some use of the search and the SpreadSheetPlugin might do it for me, but I can't figure out how to reference table cells in different tables. Is that even possible? Any suggestions on how to roll values up to summary tables?

-- Joshua Tharp - 2013-04-11

Discussion and Answer

Oh, and I can't really use forms...

-- Joshua Tharp - 2013-04-11

Funny, I just helped out a client with this.

Install the latest SpreadSheetPlugin so that you can use hash variables to store results of various tables.

There is no way to reference tables in other topics unless they are included. To get some statistics on tables in multiple topics you can do a number of includes (manually or via SEARCH format), and do some spreadsheet magic within those pages to remember the results in hashes. Place the spreadsheet formulas directly below the tables or in the last table row. They produce no output, they simply populate hashes. After the include you can display the hash values to get your statistics.

You are likely only interested in the result, e.g. you don't want to see all included pages. Put the included pages into a div with style="display: none;" to hide them.

See a somewhat outdated example at SpreadSheetRollupTest.

-- Peter Thoeny - 2013-04-11

I think I'm following... How is the example outdated? Is it just the comment vs. the div?

-- Joshua Tharp - 2013-04-11

Yes, and just using a simple variable instead of hashes to store mode detailed statistics.

-- Peter Thoeny - 2013-04-11

Would you clarify what you mean by using hashes?

-- Joshua Tharp - 2013-04-12

I just found the documentation for hashes in the latest plugin (I know, I know... you said the latest version)

-- Joshua Tharp - 2013-04-12

Example: To collect the total of multiple columns in an included topic do this in the last row:

|  | %CALC{$SETHASH(sum_%TOPIC%, $COLUMN(), $SUM($ABOVE())}% | %CALC{$SETHASH(sum_%TOPIC%, $COLUMN(), $SUM($ABOVE())}% | ... |

In the including topic you can build a new table that shows a matrix of the sum of the included topics. For example, use %CALC{$GETHASH(sum_TopicX, 2)}% to retrieve the sum of column 2 of TopicX. You can build that table automatically using a $LISTEACH() that iterates over the list of included topics.

See also blog Advanced TWiki Spreadsheet Formulas: Power and Flexibility - Not For Grandma.

-- Peter Thoeny - 2013-04-12

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.