Sub-summaries

[Christopher Dixon]

I have a sub-summary part called 'Category' to categorise Sales.

I have a calculation field called 'Margin', to measure profitability (profit/sales*100).

I have Summary field, which is the Average of 'Margin', to enter into the sub-summary part, to enable the margin to be calculated for each category of sales.

I sort the report by 'Category', but unfortunately the report shows the same margin figure in all categories. This figure is the margin for the grand total.

Any ideas anyone?

Chris Dixon.

[comment]

What kind of fields are profit and sales?

[Christopher Dixon]

The field 'Profit' is a Calculation field (Sales Invoice - Total Cost). 'Sales Invoice' is a Calculation field (Sales Inv 1 + Sales Inv 2 etc). 'Sales Inv 1' and 'Sales Inv 2' etc are number fields.

'Total Cost' is a Calculation field (Cost Materials + Cost Labour + Own Labour.) 'Cost Materials' is a Calculation field Sum(Supplier::Amount) from a related field called 'Suppliers'. 'Amount' in the related file is a number field. 'Cost Labour' is the same as 'Cost Materials', but 'Own Labour' which refers to a related file called 'Timesheets', is a Calculation field called 'Value', which is Case(Name ='x', Hours*25, Name='y',Hours*27 etc). 'Hours is a number field.

The field 'Sales' is actually the aforesaid 'Sales Invoice'.

Thanks for your interest

Chris Dixon.

[comment]

I don't see anything in your description that would make it not work. What happens if you define a calculation field =

GetSummary ( YourSummaryAverageField ; Category )

[Christopher Dixon]

I have created a Summary field called 'Average of total Margin' which is the average of the field 'Total Margin', and then created a calculation field called 'getsum margin' which is 'Getsum(Average of total margin,Category), but the result is the same. That is, in every category the result is the margin of the whole selection.

I am sorting correctly by 'Category'.

Thanks for your help.

Chris Dixon.

Thanks

[comment]

Could you post a simplified example showing the problem? The thing that confuses me is that you call your field "Total Margin", but if I understand correctly, it's not a total of anything - it is the margin of a single record. Same goes for "Total Cost".

Earlier you mentioned that:

'Sales Invoice' is a Calculation field (Sales Inv 1 + Sales Inv 2 etc).

This hints at improper structure: you should not have multiple fields like Field1, Field2, etc. - these should be broken into separate records. I don't know if this interferes with your effort to get a proper summary or not, so an example would be in order.