Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Count of Group Instances Post Reply Post New Topic
Author Message
Speedemon0202
Newbie
Newbie
Avatar

Joined: 26 Sep 2009
Location: United States
Online Status: Offline
Posts: 4
Quote Speedemon0202 Replybullet Topic: Count of Group Instances
     Posted: 23 Nov 2009 at 5:55am
Hello!

I have a report which groups varieties of apples, with a group below that which is printed quarterly that summarizes total bins of fruit packed, among other information.

What I would like to do is create an formula that shows the average bins packed per quarter.  This should be a simple sum(bins)/4, but not all apple varieties were packed in all four quarters.

Now, what I have done is created a formula with my datestamp named @GroupQuarterly, then grouped my data off of that formula quarterly, and I have also created multiple groups in this same manner (i.e. Yearly, Quarterly, Monthly and Weekly) to conditionally suppress them as the grouping type is selected dynamically from a parameter I created.

The whole report works great, I just need a way to find out how many quarters there were for each variety in the specified date range so I can divide the total bins in each variety by the quarters to get an average bins per quarter.

I have tried count(GroupName ({@GroupQuarterly}, "quarterly")), but I am informed by Crystal that the field cannot be summarized.

If anybody can help me out, I'll buy donuts!

Thanks
Mike-
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 23 Nov 2009 at 6:43am
DBlank would use running totals, but like variables and formulas.  of course, as I type, the thought occurs to me, have you tried CountDistinct({@GroupQuarterly}) in the group footer/header? (never mind, it won't work, as you can't summarize a formula)
 
Otherwise, I would do something like(which can be incorporated into the formula for calculating the {@GroupQuarterly}):
shared numbervar quarters;
shared stringvar strQuarters;
 
if instr(strQuarters,{current quarter})=0 then (
  quarters := quarters + 1;
  strQuarters := strQuarters + "|" {current quarter};
);
 
 
{current quarter} would be how you are designating the quarter in your formula...this formula counts the quarters uniquely.  you would probably want to reset the value at the start of the group and use it in the footer.
 
HTH
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Nov 2009 at 7:54am
FYI- I can't visulaize the design here but you can
summarize a formula field as long as the formula does not use functions that are print time (like next, previous) or any other summary function already in it. Therefore you might be able to go with Lockwelle's suggestion of using distinctcount:
DistinctCount({@GroupQuarterly})
or
DistinctCount({@GroupQuarterly}, group field)
IP IP Logged
Speedemon0202
Newbie
Newbie
Avatar

Joined: 26 Sep 2009
Location: United States
Online Status: Offline
Posts: 4
Quote Speedemon0202 Replybullet Posted: 23 Nov 2009 at 2:37pm
Thank you guys, I really appreciate your help, but I think we are on different pages.

There is no formula to calculate {@GroupQuarterly} aside from just the date field.

I created a formula, and dropped my datestamp field in there, then I made that a group, and set that group to print ever quarter.  The same thing as using a straight date field, except it makes it easier to differentiate between four date groups.

When I use a count on the group, it returns the count of dates for that variety in the quarter.

Sorry to be a nuisance, but it seems there should be a simple and concise way to accomplish this task. :-p

Thanks again! :-D
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Nov 2009 at 2:42pm
Then you had it nearly correct with your
count(GroupName ({@GroupQuarterly}, "quarterly"))
except you need to replace the GroupName with the field you grouped on and make it a distinctcount.
You can alter the "quarterly" per date typing you are using at each group level. e.g.
DistinctCount(Varietyfield,{@GroupQuarterly}, "quarterly")


Edited by DBlank - 23 Nov 2009 at 2:45pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Nov 2009 at 2:47pm
If you end up using these for division tehn you may want to do a if then to handel division by 0 errors. Something like:
if isnull(DistinctCount(Varietyfield,{@GroupQuarterly}, "quarterly")) or DistinctCount(Varietyfield,{@GroupQuarterly}, "quarterly")=0  then 0 else
SUM(bins, {@GroupQuarterly}, "quarterly") / DistinctCount(Varietyfield,{@GroupQuarterly}, "quarterly")
 
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.