Solved

Creating a Summary of Running Totals

Posted on 2003-10-30
6
401 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 135 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
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
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 June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course 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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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