?
Solved

Creating a Summary of Running Totals

Posted on 2003-10-30
6
Medium Priority
?
402 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:nickholt1972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 

Author Comment

by:nickholt1972
ID: 9648894
Sorry, forgot to mention that I'm working with Crystal Reports version 8.5
0
 
LVL 77

Accepted Solution

by:
peter57r earned 540 total points
ID: 9649085
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
 

Author Comment

by:nickholt1972
ID: 9649210
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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 
LVL 77

Expert Comment

by:peter57r
ID: 9649261
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
 

Author Comment

by:nickholt1972
ID: 9649478
"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
 
LVL 77

Expert Comment

by:peter57r
ID: 9649553
Thanks.

Pete
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question