Cumulative Totals (RunningSums)

[jellie]

I'm hoping for some pointers on the best way to achieve the following:

Fields: TaskID, DateWorkPerformed, TimeSpent

The report shows all the times that work on a particular task was performed.

A typical entry for a report on TaskID = 0001 would be
DateWorkPerformed = 01/01/2011
TimeSpent = 01:30

I also need an extra field on each row of the report which shows the rolling total for the time spent on that task in the preceding 90 days.

The report may be date limited i.e. only show entries between 01/01/2010 and 01/01/2011. In this scenario, an entry on 01/01/2010 must still show the rolling total from the previous 90 days even though the records of the previous 90 days aren't currently displayed.

I looked at using RunningSum but that seemed to only sum by groups or over all.

I also looked at adding an extra field to each record, with some vba running when the record was created which calculated the rolling sum. However, the rolling sum wasn't updated if earlier records got subsequently got edited.

What would be the best way to go about solving this?

 

[vbaInet]

Have you thought about using a subreport?

 

[PNGBill]

I don't see a way to include data in your Report Rolling Sum that is not in the Report Record Source.

You could try DSum() or create your own Function to collect data for the last 90 days. This should be quite easy.

The hard part will be where to place the function in your Report.

In the body of the Report, it is Continous and any data will apprear on every record (line)

Have you tried the Report Grouping and Sorting and Headers and Footers ?
Group Headers and Footers allow a Single Control to be placed that covers the respective group.
This would mean that all like records are in the group and then onto the next group.

You can also use Calculated Controls where you can Sum Records or Plus or Minus records. You can use Iif with these but you still be limited to data available from your Report Record Source and the issues with being placed in the Body (every record) or the headers and footers (for the group - but Iif will filter)