Grouping by Max Count Values

[teddles]

Help wanted please.
I need to group on a database field that has multiple instances of the same value, with the group being based on the total count of each distinct value.
For example:

Field Values
-pear
-orange
-pear
-plum
-plum
-apple
-orange
-pear

For the above data set there are:
3x pear
2x orange
2x plum
1x apple

Report structure sought:
GrpH1: 3 Instances
Detail: pear

GrpH1: 2 Instances
Detail: orange
Detail: plum

GrpH1: 1 Instance
Detail: apple

Using CR8.5
Hopefully I've supplied sufficient info. Any help greatly appreciated.

Teddles

 

[lbass]

Group on {table.fruit}, insert a count on the detail level {table.fruit}, and then go to report->topN/Group Sort and choose "count of {table.fruit}", descending. Do not limit the "N"--leave it at "All."

-LB

 

[bdreed35]

Sounds like you want to use the Top N Expert to create this report.

Try these steps:
Create a group on the Field you want to count.
You can suppress the detail section in the case above.
Create a summary count of you field.
Insert, Summary. Select your field, choose count and choose the goup.
Next, select Top N/Sort Group Expert from the Report menu.
You want all, then choose you field count, and then choose descending.
You should be all set after that.


~Brian

 

[teddles]

Thanks Brian & LB for your advice.
I have in fact got my report structured as you suggest, however it doesn't meet my needs.
I'm afraid I'm guilty of oversimplifying the problem and haven't clearly put my case.
By way of further explanation, I want to be able to provide summary info on other fields assoc with fruit type based on maximum count.
For example:
If there are 2 instances of both apple & plum in the data set, then these would both be grouped under "2 Instances", with summarised data assoc with fruit type in the group footer (not mentioned in my original post!).

GH: 2 Instances
Detail: apple
Detail: plum
GF: various summary fields

Thanks
Teddles

 

[lbass]

You can't group by summary groups. There might be a way to create faux groups, but then it would be difficult creating summaries based on them.

Depending on the number of "group" instances, it might be easier to create a separate subreport for each "group", using the same recordset each time, but using a group select statement that varies, e.g.,

count({table.fruit},{table.fruit}) = 3

This example would return only those groups that had three records. You could then use the subreport footer to summarize the results for groups with this count, although you would have to use running totals to avoid counting non-group selected records.

The subreports (unlinked) would need to be placed in report header or report footer sections of the main report.

-LB

 

[teddles]

Thanks LB
I'll adopt this approach - have only got 3-4 subreports to worry about. Have minimal experience with the use of subreports so might have some parameter & display issues associated with these sub reports. I'll see how I go!.

Thanks again
Teddles

 

[IdoMillet]

As an alternative to subreports, you can do the following:

Use global variables to accumulate the summary values (one for each summary you need). Reset these variables in the Group Header #1b only if OnFirstRecord or if the group count of the previous group is different. Otherwise, the GH1 formula should add the summary of that group into the variable.

You will need to hold the group count of the previous group in a variable too because the Previous() function will not let you refer to summaries.

Create a Group Header #1a to act as a "Group Footer" for the groups above it. This is where you will display the summaries using ther gl;obal variables. That section should be suppressed except for cases where the group count of the previous group is different.

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com