Show only the top 10 records per Group

[leeland]

Hello,

Long time reader, first time poster :)

I am working on a report in crystal 8.5, and I have written an SQL statement that will return a random record set...

When I put that view into Crystal I want to group it by a provider...and show only a max of 12 records per that grouping.

(Some groupings will only have a couple of records in it)

What is the correct way to set up such a grouping that limits the record count to 10 to 12 ?



Any help would be appreciated...


I did look into the TOP N/SORT GROUP EXPERT...but wasn't getting the desired results...


Thanks in advance


Leeland

[leeland]

well don't everyone all jump at this one at once...


I figured it out...I had to use a formula and then clear the formula variable at the group footer level

[Ken55]

leeland, can you share the formula? I have the same need but not quite figure it out yet. Thanks!

[pawa]

Hi,
U can create a fomular to count records in each group. Then u supress details when count(records) > 10
Regards.

hientv78@yahoo.com

[Ken55]

Thanks pawa. I got it.

For Crystal newbie likes me.

1. setup a group count.
2. suppress detail if > 10 records.

[SOFTGURU]

Which you are doing it should be slower if you have huge data. I think it should be better if you create a storeprocedure like this..

CREATE PROCEDURE storeproc1
@RC int
As
SET ROWCOUNT @RC

Select .........

[Madhi]

That will select only top @RC of the first group :)

[pallavi.shinde]

Use Nth Largest

[Madhi]

Refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

[Babs827]

How are you creating the count of records in each group?

I can create a summary count for the group but I don't think this is what I'm supposed to be doing?

Thanks,
Babs827