Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Sum of Group Distinct Post Reply Post New Topic
Author Message
herman
Newbie
Newbie


Joined: 01 Mar 2011
Location: United States
Online Status: Offline
Posts: 30
Quote herman Replybullet Topic: Sum of Group Distinct
    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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
herman
Newbie
Newbie


Joined: 01 Mar 2011
Location: United States
Online Status: Offline
Posts: 30
Quote herman Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
herman
Newbie
Newbie


Joined: 01 Mar 2011
Location: United States
Online Status: Offline
Posts: 30
Quote herman Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
herman
Newbie
Newbie


Joined: 01 Mar 2011
Location: United States
Online Status: Offline
Posts: 30
Quote herman Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Apr 2011 at 10:57am
try suppression using
MAXIMUM(table.QTY, table.TICKER) = SUM(table.QTY, table.TICKER)
IP IP Logged
herman
Newbie
Newbie


Joined: 01 Mar 2011
Location: United States
Online Status: Offline
Posts: 30
Quote herman Replybullet Posted: 04 Apr 2011 at 11:05am
That did it.  You are a genius!  I can't thank you enough for your help.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.