When you refer to a summary field inside a calculation, the field evaluates to compute a "grand total" type result just as though the summary field were located inside a footer, header or grand summary layout part. It does not return the "sub total" result that you see in a sub summary part.
To access a subtotal inside a calculation, use the getSummary function and use the same "when sorted by field" specified for your sub summary part as the "break field" parameter in the getSummary function.
Ok, I totally get the logic of that, it makes sense. I can't grasp why my GetSummary is empty, yet the Summary for the same data (broken in the summary part by the same field) works fine...
I have a part based on 'Year' and the summary is Summary(Fees).
So I think....GetSummary(Fees;Year) should give me the same...yet it's just an empty cell. Not even a question mark!
FM is a twisted mistress indeed...
Make sure that your found set is sorted by the Year field.
For GetSummary, the Year field must be defined in the same table as Fees and both need to be defined in the same table that is the data source table for your layout.
The Sub Summary layout part can use a "sorted by" field from another table, GetSummary cannot. This sometimes requires adding an extra calculation field that just copies the "sort" field into the same table as your summary field. (And then you sort on this calculation field.)
Is Get Summary affected by multiple sort fields? I'm sorting by customer and year, both of which are now on the source table (thanks for that!)
It's still an empty result - almost like I need Get Summary (fees;Customer;Year)
You need to specify the same sort field as that specified for your sub summary layout part in order to get the same sub total.
Make sure that this field is defined as a calculation field. It can't be a number field with an auto-enter calculation using this expression.
As you cannot use a summary in a calculation field, I've calculated the IF's on their own field and summarised that instead.
It seems to work...
Thanks Phil :)
But you can use a summary field in a calculation field. I do that all the time.
But if you are using IF to determine whether or not the value from that record should be part of the summary, then your approach is one of several that may be used to produce that result.