MPG calculation in CR 9, simple? not for me!

I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.

Format the Gals field. At the suppress formula: isnull (previous({galsfield}))

new formula, @.MPG: ({milesfield}-previous({milesfield}))/{galsfield)

new summary, grand total average @.MPG|||Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||I'm using 8.5 so there might be a difference. I need to try a couple things at work monday.

Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||bump|||Anybody else want to take a stab at this?|||I'll assume you're grouping by vehicle and ordering by odo, and that your 3 rows of sample data are for one vehicle.
I guess you want, per vehicle, the number of gals to be the sum of all records except the first, and the number of miles to be the last record minus the first.

So you could put a formula in the group header to subtract the gals and store the first odo, a formula in the details to add the gals, and a formula in the footer to subtract the stored first odo from the current odo.

e.g.
group header:
whileprintingrecords;
numbervar total_gals:= total_gals - {table.gals};
numbervar vehicle_gals := -{table.gals}; --or maybe {table.gals} * -1
numbervar first_vehicle_odo := {table.odo};

details:
whileprintingrecords;
numbervar total_gals := total_gals + {table.gals};
numbervar vehicle_gals := vehicle_gals + {table.gals};

group footer:
whileprintingrecords;
numbervar first_vehicle_odo;
numbervar vehicle_miles := {table.odo} - first_vehicle_odo;
numbervar total_miles := total_miles + vehicle_miles;
--and display miles/gallon for the vehicle
vehicle_miles / vehicle_gals --You should add a divide by zero check here for when there's only one record

report footer:
whileprintingrecords;
numbervar total_gals;
numbervar total_miles;
--and display miles/gallon for the report
total_miles / total_gals --divide by zero check again

By the way, I've not tried this explicitely but it might give you a start / ideas.sql