Creating a group on a 'Count' formula

[bpngo]

I would like to be able to create a group based on the count of the detail lines. Example below:


GH1 No Coverage
GH2 Bill
D Detail line 1
D Detail line 2
D Detail line 3
D Detail line 4
D Detail line 5
GF2 5 (count of the detail lines)

GH2 John
D Detail line 1
D Detail line 2
D Detail line 3
D Detail line 4
D Detail line 5
GF2 5 (count of the detail lines)

2 people with 5 detail lines.

GH2 Mary
D Detail line 1
D Detail line 2
D Detail line 3
D Detail line 4
GF2 4 (count of the detail lines)

GH2 Sue
D Detail line 1
D Detail line 2
D Detail line 3
D Detail line 4
GF2 4 (count of the detail lines)

GH2 Jane
D Detail line 1
D Detail line 2
D Detail line 3
D Detail line 4
GF2 4 (count of the detail lines)

3 people with 4 detail lines.

This is what I’m looking for:

Group header 5 detail lines
Bill
John
Group header 4 detail lines
Mary
Sue
Jane

Sounds simple, but I can’t get it. I thought I could group by the ‘count of the detail lines’ formula field, but it doesn’t appear on the Group Expert screen. I hope I explained this properly. Any suggestions? I’m using Crystal XI.

Thanks,
Brian

 

[lbass]

Go into the field explorer->SQL expression and create a SQL expression {%cnt} like this:

(
select count(`ID`)
from table A
where A.`name` = table.`name`
)

...where ID is some recurring field. You might have to build in criteria from your record selection formula if your main report is limited in some way.

Then you can insert a group on the SQL expression.

-LB