Sum The Next Three Records in a Crystal Report

[ANDREWeire]

Hi.

I am using Crystal Reports 8.0 and have created a report that pulls data from a Microsoft Access Table. The data is pulled into the reports sorted by the date.


Job Description Factor Date
--------------------------------------------
J001 Test 1 12/06/05
J002 Test 2 13/06/05
J003 Test 5 14/06/05
J004 Test 2 15/06/05
J005 Test 1 16/06/05
J006 Test 3 17/06/05
J007 Test 1 18/06/05
J008 Test 4 19/06/05


I want to include a field that SUMS the <Factor> value for the current record plus the next 3 records. The result would be as follows. The same result would be achieved very easily in Excel!!!!!

Job Description Factor Date
--------------------------------------------
J001 Test 1 12/06/05 10
J002 Test 2 13/06/05 10
J003 Test 5 14/06/05 11
J004 Test 2 15/06/05 07
J005 Test 1 16/06/05 09
J006 Test 3 17/06/05
J007 Test 1 18/06/05
J008 Test 4 19/06/05


I have tried to use a running total (based on record number) but no joy with that one since I cannot come up with a suitable "Reset Formulae". Somehow don't think that one would work.

Beginning to think that I will have to do some proper coding and use loops, etc. Would appreciate any help with this.

Thanks

 

[lupins46]

You can't do this in CR because it can't see that far ahead.

You can get the sum of the latest 4 records so if you can put up with presenting the list in reverse order you can get the figures you want.

 

[ANDREWeire]

Thanks.... How would I get the sum of the latest four records?

 

[lbass]

This is bdreed35's solution from another thread, which should work if you sort the date in descending order:

numberVar array Accum;
numberVar x;
numberVar y := 0;

if OnFirstRecord then
ReDim Accum [1]
else
ReDim Preserve Accum [UBound(Accum)+1];

Accum [UBound(Accum)] := {table.factor};

If UBound(Accum) < 5 then
y := Sum(Accum)
Else
(
for x := (UBound(Accum)-3) to (UBound(Accum)) do (
y := y + Accum [x];
);
y;
)

-LB

 

[Madawc]

You might be able to group by a date-range, an extra group within your current group. At least you could in Crytal 10: I've used 8.5 but never 8.0.

Madawc Williams (East Anglia, UK)

 

[ANDREWeire]

Hello again.

I am still struggling with this problem. Not exactly sure whether grouping by the date range would help since there is a unique date against each record.

What I am trying now is this. I am creating an array function. It doesn't work yet and I will try and explain where I think I am having trouble. I have never created or worked with an array function before.

-----------------------------------------
WhilePrintingRecords;

NumberVar Array records;
Redim records[1000];

NumberVar Array Factors :={schedule_table.Factors};
Redim speeds [1000];

Numbervar recordnum;

For recordnum := {schedule_table.ID} to ({schedule_table.ID}+3) Do
(records[recordnum] := Factors[recordnum]);

Sum(records)

-----------------------------------------

My logic of this is as follows. I created an array variable called records that will hold up to 1000 entries.

The formula then checks the actual record number (field:= ID) and only populates the array funtion for the matching records (from ID up to ID+7). I then SUM the array funtion.

My logic is that this would give me the respective Factors related to each of the rows I am interested in. My problem is that I cannot get the Factors array to reference the values held in the schedule_table.Factors field.

I am confused even writing this !!!

If this is impossible please let me know. I would like to learn how to use array functions properly since I suspsect I could use them in a lot of other reports. Thanks to all who have answered. Really appreciate it.

Cheers

 

[lbass]

Please explain why you did not use the formula I suggested, as it tests out here.

-LB