Solved

Creating a Summary of Running Totals

Posted on 2003-10-30
6
397 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
  • 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to add a total 6 39
Join issues with Crystal Report 10 69
Store total calculated duration sessions. 44 81
Crystal Report GH Suppress Formula not acting as expected 10 32
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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