What value is returned by a summary field depends on the context in which it is used. If you are using a sub summary layout part to display an average for each station, take a look at the getSummary function, it can access the same total shown in the sub summary part in order to use it in a calculation. The "break" field parameter should be the same as the "when sorted by" field in your sub summary part.
Perfect - thanks
Now I have a Group Field (Jan, Feb etc) and monthly average. Now I want to use these value in a calculation but in the get summary function there isn't a choice for which mon.
Correct. GetSummary is not designed to work that way. My answer assumed that you had a report like this:
Station 1: average value
Station 2: average value
Station 3: average Value
Station 4: average Value
Station 1: average value
and so forth...
The Row shown as Jan 2013 would be a sub summary layout part and a summary field placed in it would produce a value based on all 4 stations over Jan, 2013. The Station 1, 2 ... rows could also be sub summary layout parts where putting the same field in it produces a summary value based on the monthly data for just that specific station. Get summary allows you to access the same summary field values as those shown in the sub summary layout parts such that you can use them in a calculation. You can then place such a calculation field in the sub summary layout part that uses the same break field to show a result computed from the same summary field produced sub total.
So it is very possible that you have a different report format in mind that needs a different approach.
I've used the Getsummary to calculate monthly totals by station and can display these values in a layout as sub-summary. I can also use the AvgET in a calculation (i.e. PlantET).
What I want to do is get a sum of the Avg_ET for a single station for only a few months and then use this in a calculate to get PlantET. For example, wine grapes for station 13 for April to Jul. This value would then be in another layout that would go to a customer.
thanks - with your help I'm getting there
And how will the user specify "April to July"? Perhaps by entering/selecting data in a pair of fields?
In any case, you can set up self join relationships and/or SQL queries in ExecuteSQL that can compute an aggregate value such as a sum or average from a set of related records. A relationship can match ranges of date in two different ways:
Table1::DateStart < Table2::Date AND
Table1::DateEnd > Table2::Date
And you can narrow the group of records down by including additional match fields such as matching by a station ID.
The WHERE and JOIN clauses in SQL can also produce the same set of values to be averaged or summed.
Hmm I said "two different ways" and then only described one. Here's the other way such a "date range relationship" is often set up:
Table1::DateList = Table2::Date
Where DateList is a return separated list of all dates from date start to date end. Custom functions or looping scripts are typically used to put such a list of dates into DateList.