Tut Spreadsheet Rollup

This question and answer were posted at twiki.org in the Support web as TWiki:Support/RollupSpreadsheetCalcsToParentTopic.

Question

Is it possible to "roll up" calculations performed by Spreadsheet plugin into another topic? For example, to pluck out the Totals row from multiple child topics and roll them up into the parent?

Answer

Problem:

How to show a grand total of several topics containing tables with Spreadsheet plugin formulae.

Solution:

  • In each topic, add the total to a variable, like this in a footer row cell:
    %CALC{"$SET( grandtotal, $EVAL( $GET( grandtotal ) + $SUM($ABOVE()) ) )"}%
  • Include each topic in a master topic; hide the included topics if you are only interested in the grand total.
  • Display the grand total like this:
    %CALC{"$GET( grandtotal )"}%

Example

1. Include SpreadSheetRollupTestA, SpreadSheetRollupTestB, SpreadSheetRollupTestC here, but hidden in HTML comments. Write this:

<!--
%INCLUDE{"SpreadSheetRollupTestA"}%
%INCLUDE{"SpreadSheetRollupTestB"}%
%INCLUDE{"SpreadSheetRollupTestC"}%
-->

To get this empty text:

2. Show grand total. Write this: %CALC{"$GET( grandtotal )"}%

To get this: 11185
Topic revision: r2 - 02 Dec 2014, VickiBrown
This site is powered by Foswiki Copyright © by the contributing authors. All material on this wiki is the property of the contributing authors.
Foswiki version v2.1.6, Release Foswiki-2.1.6, Plugin API version 2.4
Ideas, requests, problems regarding CFCL Wiki? Send us email