Print Page | Close Window

Sum of Group Distinct

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12797
Printed Date: 02 May 2024 at 7:40am


Topic: Sum of Group Distinct
Posted By: herman
Subject: Sum of Group Distinct
Date Posted: 04 Apr 2011 at 8:44am
How do I sum a group of only distinctcount records?  This is for a security holdings report...I have some securities that have been purchased on multiple dates which I need to sum, but the others without multiple dates I don't need to sum. 
 
The example of what I talking about is below...how do I surpress the BAC & CAT sum, but keep the BWA & CMI sum's?  Thanks.
 
 
Ticker Sec Name Trade Date Qty
BAC BANK OF AMERICA CORP 11/11/1911 800
800
BWA BORG WARNER INC 10/28/2010 200
BWA BORG WARNER INC 12/20/2010 125
BWA BORG WARNER INC 2/16/2011 200
525
CAT CATERPILLAR INC 11/11/1911 100
100
CMI CUMMINS INC 11/11/1911 100
CMI CUMMINS INC 2/16/2011 300
400


Replies:
Posted By: DBlank
Date Posted: 04 Apr 2011 at 9:04am

group on TICKER

Create a Running Total
Field to summarize=QTY
type=SUM
evaluate = use a formula
COUNT(Ticker,Ticker)>1
Reset=Group1 (if you want sums per group)
Place in group footer
or
Reset= Never if you want a report total
Place in report footer

Posted By: herman
Date Posted: 04 Apr 2011 at 9:23am
How do I surpress the row or record if it's zero?  I tried going thru the section expert, but can't get it to work.

Posted By: DBlank
Date Posted: 04 Apr 2011 at 9:26am
NOTE: suppressing does not exclude it from a count or sum
What do you want to suppress? The QTY? the whole row?The whiole Group?

Posted By: herman
Date Posted: 04 Apr 2011 at 9:33am
I would like to surpress the row if the sum total is the same as what the qty amount is for what the Ticker group is.  In other words, if the Ticker CAT is 100 for the single holding I don't also need another row that giving me the sum of 100.  This sum row can just be surpressed.  Is this possible to do?  Thanks for your help!

Posted By: DBlank
Date Posted: 04 Apr 2011 at 9:38am
if you want the group footer to be suppressed
go in the section expert
select the group footer
click on teh formula field for "suppress no drill down"
add your criteria here which can be a numebr of things but just use a count of your group rows =1 like...
COUNT(table.TICKER,table.TICKER)=1

Posted By: herman
Date Posted: 04 Apr 2011 at 10:32am
This works in most cases on the report, but there are some where it's still pulling in sum total row with a single holding.

Posted By: DBlank
Date Posted: 04 Apr 2011 at 10:35am
are you suppressing any rows in any other way?
and when you say with one holding can there be more than one row of data even though it is one holding?

Posted By: DBlank
Date Posted: 04 Apr 2011 at 10:57am
try suppression using
MAXIMUM(table.QTY, table.TICKER) = SUM(table.QTY, table.TICKER)

Posted By: herman
Date Posted: 04 Apr 2011 at 11:05am
That did it.  You are a genius!  I can't thank you enough for your help.


Print Page | Close Window