Using Sub-Summary results on another record and layout
I have a report which by use of sorting and sub summaries gives me a figure of £X per client, per year. A filter uses a global value to remove years I don't want to see.
On the same row as £X, it has a field which returns the number of staff (S).
I then need a completely different report to show unique rows per member of staff with £X showing on the same record. For example, if on report one I had a customer with 5 staff, I need 5 records creating.
I've created a script to count staff, create (S) number of records and then loop back for the next customer.
Now, I need to get the sub summary figure of £X to appear on each instance of the staff before that too loops back around for the next customer.
I appreciate this sounds nuts but the particular demands of this task require either a table populated with new data, or an export to Excel and copy/paste work there. It'd look so cool to keep everything in FM!
So, the question is either:
1) How can I get summary data to a brand new record? I've tried GetSummary and $variables and neither has worked. I understand the former only works on the same table though.
2) I then thought 'Oh, can I create muliple sub summaries based on staff quantity?' but I think sub summaries always rely on the data being there first of all.
Apologies if this sounds mixed up, this task defies the laws of physics!