Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Record Count Post Reply Post New Topic
Author Message
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Topic: Record Count
     Posted: 06 Aug 2010 at 5:42am
I have created this report that gives me a total number of records by group in an Aged bucket of 0-30, 31-60, 61-90 and over 90. Some Age buckets do not have any records in them so it is blank.  When crystal goes to count the record it is counting all the record for that group.  See example below of what crystal is doing. It is counting every record instead of the record with a positive number. I want to count the number of record in say 0-30 should be 10, 31-60 s/b 5, 61-90 s/b 1 and over 91 s/b 3

Is there a way where i can have crystal count the records like it would count the record in excel and not include blank spaces.


Thank you very much.
     

      


Incident WO Date Opened Days Open Urgency Aged 0-30 Aged 31-60 Aged 61-90 Over 91Days










C









134680 38720 2/24/2010 6/11/1900 C


164

136354 39173 3/11/2010 5/27/1900 C


149

137483 39501 3/24/2010 5/14/1900 C


135

144559 41890 5/25/2010 3/13/1900 C

74

146530 42816 6/10/2010 2/26/1900 C
57


147009 42937 6/15/2010 2/21/1900 C
52


147360 43075 6/17/2010 2/19/1900 C
50


149212 43955 7/5/2010 2/1/1900 C
33


149252 44001 7/6/2010 1/31/1900 C
32


0 44032 7/7/2010 1/30/1900 C 31



136359 44216 7/9/2010 1/28/1900 C 29



150468 44482 7/14/2010 1/23/1900 C 23



152606 45177 7/30/2010 1/7/1900 C 8



152670 45194 7/30/2010 1/7/1900 C 8



152673 45195 7/30/2010 1/7/1900 C 8



136354 45276 8/3/2010 1/3/1900 C 3



151917 45417 8/5/2010 1/1/1900 C 1



153485 45430 8/5/2010 1/1/1900 C 1



153531 45458 8/6/2010 1/0/1900 C 1


Total



19 19 19 19 19

IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 06 Aug 2010 at 5:52am
Have you tried a distinct count?
 
I hope this helps.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Aug 2010 at 6:25am

From your previous post I will assum you created formuals to put the rows into your date buckets.

You can create 3 Running Totals (RTs) do do your counts and under the evaluate section use a formula to include ony the rows with your dates as you desire the same way you created the buckets
 
IP IP Logged
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Posted: 06 Aug 2010 at 6:59am
the distinct work but the funny thing is when I did it before it would not work even when I refresh the data. I had to save the report exit out and open it back then do the distinct count.

yes I have created the formulas to put the rows in buckets.
IP IP Logged
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Posted: 06 Aug 2010 at 7:00am
thanks very much for your help
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Aug 2010 at 7:39am
as an FYI-
assuming the 'date bucket formulas' are crystal formulas be carefull with the DistinctCount. If you did not group and put eh DC at the group level and the DC is of the formula fields they will be 1 more than the actual number as the 'blank' rows will be counted as 1 distinct row.
IP IP Logged
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Posted: 06 Aug 2010 at 8:05am
@DBlank you are so right I'm sitting here wondering why I'm off buy 1 here 2 there. The numbers are not tying out.  Everything is crystal formulas and the distinct count is at the group level.


Edited by kbnyny - 06 Aug 2010 at 9:05am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Aug 2010 at 11:39am
Use 3 RT's to do conditional counting.
Example:
name=30daycount
Field to summarize=incident
type=DistinctCount
evaluate=use a formula
table.date in last30days
reset=never
place in report footer
make 2 more but change your evaluate formulas to look at the 31-60 and 60-90 (same formulas you used to make the 'buckets')
IP IP Logged
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Posted: 09 Aug 2010 at 4:59am
DBlank - I followed your instruction and it all worked out. Thanks a lot for you help. 
IP IP Logged
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