The basic problem is that GetSummary uses the second "break" field to determine the correct subtotal to compute. The records must first be sorted by this break field in order to group them correctly for computing such a sub total. Basically, it works the same as placing a summary field inside a Sub Summary part. The "sorted by" field of the sub summary part serves the same purpose as the "break" field parameter of the GetSummary Function.
It's intended for use with local fields as the sort order is specified at the layout level, but a reference to a related table such as RelatedTable::FieldName takes place at the data level.
It should still be possible to get the summary values you want, but not with GetSummary. If you want, tell us more about how your database is designed and what totals and/or subtotals you need to compute for your report.
I have included links to the following files so hopefully you can show me where I am going wrong:
- InstrumentCostTable - http://dl.dropbox.com/u/2139659/InstrumentCostTable.pdf
- AccountCashTable - http://dl.dropbox.com/u/2139659/AccountCashTable.pdf
- Relationship Diagram - http://dl.dropbox.com/u/2139659/Relationship%20Diagram.png
- Layout setup - http://dl.dropbox.com/u/2139659/Layout%20setup.png
I am trying to get the CashSum total by clientid to show up the layout. I can get the MTMSum by ClientID but the MTMSum is included within the table transcapital_allocationdata_INSTRUMENTCOSTTABLE. I can also get the MTMSum by client by using GetSummary as shown in the field Net Equity. When I try and use GetSummary for the CashSum field which is in another table it returns nothing in the layout.
Once I can get the CashSum total by ClientID, I wish to add it to the MTMSum to obtain total assets by client. From here, I can then hopefully calculate % of MTM/(CashSum+MTMSum) by client.
Any help much appreciated.
It's a bit hard to decipher so check these details:
You have this relationship:
transcapital_allocationdata_INSTRUMENTCOSTTABLE::_kf_ClientID = transcapital_allocationdata_instrumentcosttable_AccountCash 3::_kf_ClientID AND
transcapital_allocationdata_INSTRUMENTCOSTTABLE::ClientAccnt = transcapital_allocationdata_instrumentcosttable_AccountCash 3::clientAccnt
It would seem that this calculation defined in Instrumentcosttable defined to evaluate from the context of transcapital_allocationdata_INSTRUMENTCOSTTABLE will produce the desired subtotal:
Sum ( transcapital_allocationdata_instrumentcosttable_AccountCash 3::CashMovement )
This assumes that you want the total CashMovement of all records in AccountCash with the same client id and client account as a given record in InstrumentCost. This may not be a correct assumption on my part.
That worked. Thank you very much.