Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating a Summary of Running Totals

Posted on 2003-10-30
6
Medium Priority
?
403 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
Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. 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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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