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.
nickholt1972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nickholt1972Author Commented:
Sorry, forgot to mention that I'm working with Crystal Reports version 8.5
0
peter57rCommented:
Try this:
Create formula field which is a concatenation of the grouping fields and the field you want a distinct count on.
So the formula would look like:

cstr(fieldnameforoutergroup) & cstr(fieldnameforinnergroup) & cstr(distinctcountfield)

For example, if I have a table orders and fields cust, rep, and item , grouped by cust and rep and I want the distnct count totals for different items then my formula field would look like:
cstr({Orders.Cust}) & cstr({Orders.rep}) & cstr({orders.item})


Now create a distinctcount summary of this formula field.

You can drag it to any summary level or the grand total level.

Pete

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nickholt1972Author Commented:
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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

peter57rCommented:
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


0
nickholt1972Author Commented:
"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.
0
peter57rCommented:
Thanks.

Pete
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.