In some way, what you're asking is similar to this KB article.
In a nutshell, the current implementation of the Table Data Reference in AF was never meant for the users to trend these table values. As such, AF currently has no concept of time in the Table Data Reference, meaning even though it supports data/time format in a column in a table, it really doesn't know that 00:12 is 12 minutes past midnight. Since there is no concept of time, you really can't do summaries. This is related to what you're seeing in that only the last value is pass to AF from ProcessBook because, once again, the Table Data Reference was meant to give a value at a snap shot in time, and not trends.
Let me know if this makes sense to you as it is difficult to convey this information without some hand waving and a white board :-).
Also, you are not the first to want to do this, we already have a feature enhancement request in our queue. So that is the good news, the bad news is that this is most likely not going to be in scope for AF 2011 release.
I already used the knowledge based article describing how to trend AF Table data, using another tag (PI Point) as a reference point for the lookup into the underlying table, e.g.
SELECT GasTotal FROM ProductionData WHERE WellID = '%Element%' AND ReportDate <= #%Time%# AND GasTotal <> @ComparisonTag ORDER BY ReportDate DESC
This will give you the value on or before the timerange associated with an object within ProcessBook if using a PI Value object. The timestamp it shows is the timestamp associated with the found row.
I can also use this attribute in a Trend object and I get all rows returned. The unfortunate thing is that you have to prepare your PIPoint reference tag to have a timestamp at the point you expect it to have in the source RDB table, and a value which the RDB field is not expected to have., So this would not work where the data is stored at ad-hoc times.
I realise that AF is looking at a value at a given time. But I do not think that it is unreasonable for the AF reference to SUM all rows between two given times, otherwise why would there be the summary function for an AF Table reference if nothign can use it?
I have kind of got something that works by referencing other AF Attributes which are of DR PI Point. For "Month To Date" attribute I first of all created 2 PI Points, one for "Beginning of Month" and one for "End of Month". These were PE tags which are
BOM(‘*’) for the begin month, and BONM(‘*’) – (60*60*24), for end of month.
My AF Lookup Query is then
SELECT Sum(GasTotal) FROM ProductionData WHERE ReportDate >= @[Beginning of Month] AND ReportDate <= #%EndTime%# AND WellID = '%Element%'
This looks to give me back what I want, i.e sum of all values for the beginning of the selected month up until the end time that the user selects. PI Value objects allow the EndTime value to be set on individual objects on a ProcessBook display. The unfortunate thing is that you now need VBA code to be able to set each object up with the correct EndTime for it to work, which is not nice!
But I still believe that ProcessBook and WebParts should be passing through the start/end time through to the underlying AF attribute to fix this bug.
Thank you for your follow up, now I have more context on what you have already try/done; it wasn't so apparent from your original post and that was why I provided the link to the KB article . Subsequent to your reply, I had some conversation with others, notably tech support and I was alerted that there is a PLI associated with ProcessBook that is related to the problem that you're seeing so I would suggest we work to solve that via tech support as it's a bug and not so much a development issue.
Getting back to your most recent reply, I acknowledge that there are some features and functions that many people are now asking for in the AF Table data reference that are beyond the original intention of this DR, one of which is the ability to understand time context. So rest assured that we're listening so keep the suggestions and requests coming.