Correct syntax for calculating an average in a Sub Summary report.

[Twizzle]

Hi

I just need some help with the correct syntax for calculating an average in a Sub Summary report.

I have a database with share parcels in it & each of my companies holdings in other companies are recorded there. Now I'm reasonably used to Summary & Grand Summary Totals in an FM report and for me they have usually been a mere Total or Sub Total of a figure but I am having difficulty in getting an average price to appear correctly in a Sub Total.

The Grand Total Average 'share price' appears correctly in my reports but no matter what syntax I try I can't get it to appear correctly in the Sub Total. The field 'AverageEntryPrice' is derived by Type: Calculation. Unstored,=Bvalue Total/Total Held.

This works well in the Total part of the report but whatever I try in the Sub Total section I can't get it to appear correctly. Currently in the Sub Total section I have "Sub Tot Avge Entry Price" Type: Summary Options: Total of AverageEntryPrice (running with restart), when sorted by Invest Converted::BO.

So I thought this should give me the average price that each of my companies pays for shares in other companies but it does not work. Of course if I do a report for each of my companies individually I can get the Avge price for that companies holdings as it appears in the Grand Total section but it would be good to get all 3 companies in the one report.

Has anyone go any suggestions.

 

[comment]

Your question is unclear - mainly because we don't know what data you have to begin with. So this part:

16 minutes ago, Twizzle said:

The field 'AverageEntryPrice' is derived by Type: Calculation. Unstored,=Bvalue Total/Total Held.

is meaningless because we don't know what Bvalue Total and Total Held are. If these are summary fields, and you want to divide their sub-summary values for each company, then you need to use =

GetSummary ( Bvalue Total ; Company ) / GetSummary ( Total Held ; Company )

where Company is the breakfield in your report. But this is just a guess.

 

[Twizzle]

1 hour ago, comment said:

Your question is unclear - mainly because we don't know what data you have to begin with. So this part:

is meaningless because we don't know what Bvalue Total and Total Held are. If these are summary fields, and you want to divide their sub-summary values for each company, then you need to use =

GetSummary ( Bvalue Total ; Company ) / GetSummary ( Total Held ; Company )

where Company is the breakfield in your report. But this is just a guess.

 

1 hour ago, comment said:

Thank you comment your 'guess' was correct & I am very grateful

.