Total Avg Cost per Group 1

[YANKRAY]

Using CR9.0

Created an inventory report with three groups,
Batch (group 1)
Sequence (group 2)
Item (group 3)

There is only one Item per Sequence per Batch, but the Item may repeat itself more than once because of an additional field.

I am trying to calculate the total cost deviation for the entire inventory. I have an average of the cost deviation per item within the group because the cost deviation is the same for each repeated item.

How do I total the average cost deviations for every Item (group 3) total?

 

[lbass]

Create a formula:

//{@accum} to be placed in the group #3 header or footer:
whileprintingrecords;
numbervar grtot := grtot + average({table.cost},{table.item});

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[synapsevampire]

I think that you'll need to add one more variable which counts the number of distinct grtots to make LB's solution work, then you can divide by that.

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;
grtot/distinctcount({table.item})

-k

 

[lbass]

By "total", I assumed yankray was looking for the sum of the averages...

-LB

 

[YANKRAY]

You were correct LB. I used your formula and manually checked the totals. All were correct. Thank-you.

Thanks for the input k.

 

[YANKRAY]

I need to take this report a step further (or I should say a step back)

How do I get the total of the average cost deviations for every Item (group 3) total as a total at the Batch (Group 1) level?

 

[Madawc]

Take a look at running totals. They have some fancy functions that may meet your need.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total.. Or else use the Field Explorer, the icon that is a grid-like box.

Madawc Williams (East Anglia, UK)

 

[lbass]

Change {@accum} to:

//{@accum} to be placed in the group #3 header or footer:
whileprintingrecords;
numbervar grtot := grtot + average({table.cost},{table.item});
numbervar grouptot := grouptot + average({table.cost},{table.item});

Then create a reset formula {@reset} to be placed in the Group #1 header:

whileprintingrecords;
numbervar grouptot := 0;

Create a display formula {@grpdispl} to be placed in the Group #1 footer:

whileprintingrecords;
numbervar grouptot;

-LB

 

[YANKRAY]

That got it LB. Again Thank-you.

Thanks for the input Madawc.