create new tag
, view all tags

Bug: Spreadsheet calculations in footer fail on empty search

There appears to be some issue with using functions from the SpreadSheetPlugin in a formatted search to a table. When a search does not return values, an empty row in the resultant table which causes most calculations referring to the results in the footer row to fail. See example below...

I guess the solution would be to sommehow make sure that if the row before a calculation is not a table row, then the calculations render as space.

Test case

Compare the successful query

InstallationSorted ascending Description Computed
Total: 3 Corporate: 3 Corporate: 3
Motorola, Global Software Group Corporate Corporate
Motorola, Semiconductor Products UK Corporate Corporate
Motorola, Semiconductor Products US Corporate Corporate

with the same but returning no results...

Installation Description Computed

Total: -1 %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%: 1 %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%: 1


TWiki version: Beijing
TWiki plugins: twiki.org
Server OS:  
Web server:  
Perl version:  
Client OS: Win2K
Web Browser: IE

-- ThomasWeigert - 29 May 2003

Follow up

I have seen this problem at work as well. This is not a bug in the SpreadSheetPlugin, it does what it is told to do. The problem is that an empty search returns an empty line, breaking apart the table header and footer into two tables.

Example that illustrates the problem:

%SEARCH{ ... format="| $topic |" }%
| Total: %CALC{"$ROW(-2)"}% |

The %SEARCH{}% occupies one line. In case there is one hit it fills that line (without introducing a extra new line; for two hits it returns two lines etc; for no hits it returns nothing (ergo the empty line).

The correct solution to this problem is to get rid of the extra line if there is no hit. That needs to be investigated. I'd happily fix that if someone brings a patch.

Fix record

Following your suggestion I came up with the following, relatively simple, strategy:

The extra newline is introduced by the newline after the search in the raw topic. If the search is unsuccessful, an empty string is returned. This suggests that we would not get a newline if we were to just have the trailing row follow the search without a newline, as shown below.

InstallationSorted ascending Description Computed
Total: 0 %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%: 1
Description: 1
Computed: 1

This gets rid of the empty line, but if the search did return something, we have a messed up table, as we now miss a trailing newline. Note that TWiki::Search::searchWeb strips that trailing newline for formatted search. To avoid that, we can introduce an option to %SEARCH% that avoids this. (I am not sure whether leaving the newline in will ever cause a problem, but for backwards compatability, this may be the safer route. The attached diffs to TWiki.pm and Search.pm support this extension.

We are still left with the problem that the spreadsheet calculation does not work right in all cases. In the case of the empty search, we find that the routine TWiki::Plugins::SpreadSheetPlugin::getTableRange has an illegal cell range where the lower end comes before the upper end. This would not be a problem were it not for the lines in this routine that try to fix up this case by swapping the lower and upper limits of the range, thus taking a slice of the table that is incorrect. If we just comment out those two lines, as in the included patch, everything works as expected. (I cannot fully predict the impact of this change. However, I have a set of test cases for the SpreadSheetPlugin, which all passed.)

I cannot demonstrate the result here, due to the needed patch. However, what happens now is that the calc lines are left blank in the case the search was unsuccessful, as simulated below.

InstallationSorted ascending Description Computed
Total: 0    

which could have been generated by the formatted search (note the new keyword newline)

%TABLE{sort="on" initsort="1" initdirection="down" headerrows="1" footerrows="1"}%
%SEARCH{ "Weigert;Private;Firewalled" scope="true" regex="on" web="Main" nototal="on" nosearch="on" newline="false" format="|[[$web.$topic][$formfield(TWikiInstalledAt)]] |$formfield(TypeOfOrganization) | $percntCALC{\"$T(R$ROW():C$COLUMN(-1))\"}$percnt |" }%| Total: %CALC{"$ROW(-2)"}% | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%  | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% |

-- ThomasWeigert - 03 Jun 2003

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formatdiff Search.pm.diff r1 manage 0.5 K 2003-06-03 - 06:11 ThomasWeigert Diff for Search.pm
Unknown file formatdiff SpreadSheetPlugin.pm.diff r1 manage 1.0 K 2003-06-03 - 06:08 ThomasWeigert Diff for SpreadSheetPlugin.pm
Unknown file formatdiff TWiki.pm.diff r1 manage 1.3 K 2003-06-03 - 06:12 ThomasWeigert Diff forTwiki.pm
Edit | Attach | Watch | Print version | History: r5 < r4 < r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r5 - 2007-04-24 - PeterThoeny
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.