Previous Records

[old123]

Crystal Ver 9
ODBC - AS400

I have a report showing records from a certain time period. Here is the output

Bus No Date WO NO Meter Diff
001 08/05/2005 xxxx 187019 00

Group Footer Bus No Count Avg 00

Bus No Date WO NO Meter Diff
002 07/18/2005 xxxx 218816 00
002 07/20/2005 219680 864
002 08/30/2005 228755 9075
002 09/07/2005 230513 1758

002 Count 4 Avg 3899


and so on
Report Footer Fleet Avg XXXXX

My problem is that i want to calculate avg between 07-09 however i don't want to start the counter with zero.

I want my report to pick previous record ( that is last road call happened prior to july) This way i will have correct pic.

Here are the formulas
Diff/ to calculate differnce)

IF {FM_300L2.FMASET} = PREVIOUS({FM_300L2.FMASET}) THEN
(
if {FM_300L2.FMPMR} >= PREVIOUS({FM_300L2.FMPMR}) then
{FM_300L2.FMPMR}- PREVIOUS({FM_300L2.FMPMR}) else
{FM_300L2.FMPMR}
)
ELSE
0

To calculate avg.

whileprintingrecords;
numbervar summiles;
numbervar counter;
numbervar grtotmiles :=grtotmiles + summiles;
numbervar grtotcnter :=grtotcnter + counter;
if counter > 0 then summiles/counter else 0

To calculate Fleet Avg

whileprintingrecords;
numbervar grtotmiles;
numbervar grtotcnter;
if grtotcnter > 0 then
grtotmiles/grtotcnter else 0;

Thanks

 

[Madawc]

I'd say you needed to accumulate your totals using running totals with formulas. Then add them up using formula fields. More flexible and easier to get right - you can have a diagnostic line to display the separate parts of the calculation, if necessary.



Madawc Williams (East Anglia, UK)

 

[Madawc]

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.


Madawc Williams (East Anglia, UK)