nickholt1972
asked on
Creating a Summary of Running Totals
I have in group 1 a series of running totals, and in group 2 I want a total of these totals.
Using the same summary for group 2 doesn't work because its a count / distinct count issue and I get a total either too high or too low.
I can't create a summary of the result of running total fields, I can't put the results into formulae and summarise those because it seems you can't do a summary of calculated formulae.... or can you?
Example of report:(with detail unsuppressed)
Format: report area - data (summary type where applicable)
D - accountnumber:001
D - accountnumber:002
D - accountnumber:002
D - accountnumber:003
GF1 - joe bloggs - 3 (distinctcount of accts worked)
D - accountnumber:101
D - accountnumber:101
D - accountnumber:001
D - accountnumber:102
GF1 - john smith - 3 (distinctcount of accts worked)
D - accountnumber:201
D - accountnumber:201
D - accountnumber:201
D - accountnumber:001
D - accountnumber:102
D - accountnumber:202
D - accountnumber:203
GF1 - derek normalperson - 5 (distinctcount of accts worked)
GF2 - ??? I want it to say 11 (3 + 3 + 5) but a count of all the records in the details section would give me 15 and a distinct count would give me 8 (i.e. 8 different accounts worked by the team).
Any ideas? I've been working on this for nearly three full days with no success.
Using the same summary for group 2 doesn't work because its a count / distinct count issue and I get a total either too high or too low.
I can't create a summary of the result of running total fields, I can't put the results into formulae and summarise those because it seems you can't do a summary of calculated formulae.... or can you?
Example of report:(with detail unsuppressed)
Format: report area - data (summary type where applicable)
D - accountnumber:001
D - accountnumber:002
D - accountnumber:002
D - accountnumber:003
GF1 - joe bloggs - 3 (distinctcount of accts worked)
D - accountnumber:101
D - accountnumber:101
D - accountnumber:001
D - accountnumber:102
GF1 - john smith - 3 (distinctcount of accts worked)
D - accountnumber:201
D - accountnumber:201
D - accountnumber:201
D - accountnumber:001
D - accountnumber:102
D - accountnumber:202
D - accountnumber:203
GF1 - derek normalperson - 5 (distinctcount of accts worked)
GF2 - ??? I want it to say 11 (3 + 3 + 5) but a count of all the records in the details section would give me 15 and a distinct count would give me 8 (i.e. 8 different accounts worked by the team).
Any ideas? I've been working on this for nearly three full days with no success.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pete,
I see what you're trying to do there and I like the idea. By adding the groupnames to the field i'm summarising, it makes it unique throughout the groupings. Only problem is that when i've done that, I still can't summarise it. When i right-click on the formula, 'Insert Summary' isn't an option. I can't use it in a running total field and i can't summarise it with another formula.
To get round this i tried concatenating it with numbers to make it a number field, but it still doesn't work.
I'll keep plugging away,
Many thanks,
Nick.
I see what you're trying to do there and I like the idea. By adding the groupnames to the field i'm summarising, it makes it unique throughout the groupings. Only problem is that when i've done that, I still can't summarise it. When i right-click on the formula, 'Insert Summary' isn't an option. I can't use it in a running total field and i can't summarise it with another formula.
To get round this i tried concatenating it with numbers to make it a number field, but it still doesn't work.
I'll keep plugging away,
Many thanks,
Nick.
I'm not sure whether I was clear enough then, since I have no problem creating the summaries.
The fields in my formula are table fields, not group titles fields.
I don't have to include the concatenated field in the report. I just insert a summary and select the formula field as the one to summarise using distinct count. It doesn'y really matter what groupinglevel you choose initially as you can drag the resulting textbox to any group footer (move or copy).
Pete
The fields in my formula are table fields, not group titles fields.
I don't have to include the concatenated field in the report. I just insert a summary and select the formula field as the one to summarise using distinct count. It doesn'y really matter what groupinglevel you choose initially as you can drag the resulting textbox to any group footer (move or copy).
Pete
ASKER
"Sun is shining, weather is sweet, makes me want to move my dancing feet"
Pete, you're a genius. If I had more than a measily 135 points available, i'd give them all to you.
The principal you demonstrated was devilishly simple yet I would probably never have thought of it. So, thanks, very very much. It gets those dammed Operational Managers off my back.
Cheers,
Nick.
Pete, you're a genius. If I had more than a measily 135 points available, i'd give them all to you.
The principal you demonstrated was devilishly simple yet I would probably never have thought of it. So, thanks, very very much. It gets those dammed Operational Managers off my back.
Cheers,
Nick.
Thanks.
Pete
Pete
ASKER