Using selected latest date value in further calculation 1

[abatblack]

I have a report that checks the costing run of manufactured parts.
Each part is manufactured against a works order number.
The costing runs are done on an arbitrary time basis by part, there is no common correlation between time of costing between parts nor works orders.
I wish to pick the last costing run for each works order, pick out the costs and use them to do further calculations.


Data looks like this, all fields are stored in one database:

works order part costing run date cost
12345 123 1/1/12 1.2
12345 123 1/2/12 1.35
12345 123 1/3/12 1.35
23456 123 12/1/11 1.1
23456 123 12/2/11 1.25
23456 123 12/5/11 1.37
34567 456 1/1/12 56.33
34567 456 1/6/12 58.22
45678 789 2/1/12 12.3
45678 789 2/2/12 12.6


What i want to end up with is:

works order part costing run date cost
12345 123 1/3/12 1.35
23456 123 12/5/11 1.37
34567 456 1/6/12 58.22
45678 789 2/2/12 12.6

Important bit!: Typically i would then want to do something with the resulting costs, ie do an average of them. eg: Average manufacturing cost of part 123 is 1.36.
And then do things like see how these costs vary over time.


I am finding that i can pick out the cost that is associated with the last date using a variety of methods:
One of which would be: Group by works order and costing run date then select the run date group by comparing against the maximum value etc.
I have found many ways of picking out the latest costing run line. Ordering by run date then picking the first entry line in the group.
However as soon as i have done any sort of comparison of the group against the maximum in the group i am then unable to use the resulting cost in a further calculation.

I am fairly sure it is something about the timing of doing the calculations.

Any suggestions please.

 

[lbass]

Try inserting a group only on works order and then go to report->selection formula->GROUP and entering:

{table.rundate} = maximum({table.rundate},{table.worksorder})

This will display only the most recent record per group. Then insert running totals for your calculations, since the more usual inserted summaries will include non-group-selected records which are still "in" the report, though not displayed. The running totals will automatically ignore the non-displayed records (unlike in the case of section suppression), without adding any special criteria.

Whether this is the best approach depends upon the type of calculations you are planning and whether you are planning to chart, etc.

Alternatively you could create a SQL expression for the maximum date per group and then use that in your record selection formula, so that the entire dataset is limited only to the most recent records.

-LB

 

[abatblack]

Many thanks lbass.
It looks like I havn't been using running totals to their full potential. I need to read up to understand more about them.
The key learn being that running totals don't include the non-displayed records like summations do.
AB