SID-00621: SpreadSheetPlugIn & QuerySearch Issue

Status:	Answered	TWiki version:	4.2.4	Perl version:	5.8.1
Category:	SpreadSheetPlugin	Server OS:	Linux 2.4.9-e.25smp	Last update:	16 years ago

I have an issue that is stumping me. I am using query search to select topics that meet certain criteria, then displaying them in a table using FormattedSearch. Then I am attempting to use %CALC...% to summarize the results of the search. The full source is:

%TABLE{ %TRACKERCOLHEADINGATTRIBUTES% columnwidths="70,30,,180,70,70" dataalign="center,center,left,left,right,right" sort="on" initsort="1" initdirection="up" footerrows="1"}%
| *Week Of* | *Edit* | *Activity* | *Resource* | *Estimated Effort (Days)* | *Actual Effort (Days)* |
%SEARCH{ type="query" web="%WEB%" nosearch="on" nototal="on" "form.name='ActivityForm' AND (ResourceName='%INCLUDINGTOPIC%' OR ResourceName/(ResourceForm[name='ResourceManagerName'].value='%INCLUDINGTOPIC%')) AND WeekOf='%CALC{$GET(thisweek)}%'" format="| $formfield(WeekOf) | [[%SCRIPTURLPATH{"edit"}%/%WEB%/$topic?t=%GMTIME{"$epoch"}%;nowysiwyg=1&action=form&redirectto=%SCRIPTURL{"view"}%/%WEB%/%INCLUDINGTOPIC%?pane1tab=4&pane2tab=1][ $percntICON{edittopic}$percnt ]] | [[$topic][$percntFORMFIELD{$quotCustomerName$quot topic=$quot$formfield(CustomerId)$quot}$percnt $percntFORMFIELD{$quotOpportunityTitle$quot topic=$quot$formfield(OpportunityId)$quot}$percnt: $formfield(ActivityName)]] | $formfield(ResourceName) | $formfield(EstimatedEffort) | $formfield(ActualEffort) |"}%
| | | |  <b>Total:</b> | %CALC{$FORMAT(COMMA,1,$SUM($ABOVE()))}% d | %CALC{$FORMAT(COMMA,1,$SUM($ABOVE()))}% d |

In the above, the spreadsheet plugin formula $ABOVE() always evaluates to "R0:C5..R0:C5" -- e.g., the column is correct, but the row is not. If I change the search criteria

WeekOf='%CALC{$GET(thisweek)}%'"

so that it is anything other than a %CALC...% operation, everything works as hoped. I can replace it with a literal '09 Nov 2009' (which is what the calc evaluates to as I write this), and I get the right topics selected and the correct totals. As soon as I change it to a %CALC...%, I still get the right topics (meaning the search is working fine), but the total doesn't work. I suspect some kind of odd interaction between the %CALC...% statement in the query search criteria and the %CALC...% statement following the generated table.

I am calculating 'thisweek' as follows:

%CALC{$SET(thisweek,$FORMATTIME($TIMEADD($TIME(%DATE%),-$MOD($EVAL(($FORMATTIME($TIME(%DATE%),$wd)-2)+7),7),day),$day $mon $year))}%

which is intended to calculate the date of the immediately preceding Monday (there may be a more straightforward way to calculate this - brute force and ignorance in a crunch!)

Full configuration available via e-mail.

-- MikeStraka - 2009-11-12

Discussion and Answer

I think the issue is that CALC is evaluated after SEARCH, e.g. too late for your search query.

Not sure if this works, but try turning this around, something like:

%CALC{$NOP(%)SEARCH{ ... AND WeekOf='$GET(thisweek)'" format="| $formfield(WeekOf) ... }$NOP(%)}%

The SEARCH is excaped so that it runs after the CALC. If this does not work, try using several CALC to compose the SEARCH, such as this:

%CALC{$NOP(%)SEARCH{}% type="query" ... AND WeekOf='%CALC{$GET(thisweek)}%'" format="| $formfield(WeekOf) ... $formfield(ActualEffort) |" %CALC{}$NOP(%)}%

-- PeterThoeny - 2009-11-13

Peter - Thanks for this. I think you're onto something, but maybe the other way around. I need to keep the CALC in the footer row from running until after the table is generated. Since it is showing ROW() = 1, it seems it is executing first. I've attached a couple of files that may help clarify, one is the source of a test page I created and the other is a PDF of the rendered page. There are three examples illustrated. In the first example, where there is no CALC embedded in the SEARCH, everything is fine. In the next two examples, one using a CALC to get one of the comparison operators for the search and the other using a CALC to create a conditional format, the CALC in the footer is returning values that make it seem as if it is executing before the SEARCH.

I tried escaping the footer using:

%CALC{$NOP(|) ROW=$ROW() | ... | $FORMAT(COMMA,1,$SUM($ABOVE())) d $NOP(|)}%

-- MikeStraka - 2009-11-13

I work with Mike and was able to look at the rendered page source. I noticed that the footer row was directly after the header row, then all the data rows followed. This would explain why the calculation was wrong. The footer row is being generated by the TablePlugin. It seems to work fine in the working examples, and is at the bottom of the table in the source. For some reason, the $percntCALC$percnt in the footer is causing it to be placed earlier in the source. I removed the footer declaration from the TablePlugin call, which put the calculation at the end of the table as it should be, but the result was still as if it was being calculated from row 1.

-- MatthewCardozo - 2009-11-16

Closing this question after more than 30 days of inactivity. Feel free to reopen if needed. Consider engaging one of the TWiki consultants if you need timely help. We invite you to get involved with the community, it is more likely you get community support if you support the open source project!

-- PeterThoeny - 2010-01-03

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.