How can I limit the number of records in the entire report?

[CognosNewbie99]

I want to limit a report to only the top 100 records. I can only find ways to limit the records allowed per page, but I need to know how to limit the records for the entire report

 

[jlopeman]

One way would be to run the report off of a SQL command.

Another way would be to create a counter and then a selection criteria that evaulates off the counter... #counter <= 100

 

[CognosNewbie99]

I'm not sure I know what you mean. I'm pretty new to Crystal. Do I create a formula for the counter?

 

[kray4660]

If you know write TSQL, you could form a command (Crystal Reports 10 and higher, I think) that selects the top N records.

I hope this helps.

 

[CognosNewbie99]

I have Crystal 8.5 so that won't work, thanks though.

 

[lbass]

Top 100 of what? What field is being evaluted for whether it is in the top 100? What other fields would be in the report.

A simple way is to sort in descending order on the that field, and then suppress records with:

recordnumber > 100

However, all records would be drawn into the report, just not visible.

There is a method where you can modify the "show SQL query" in version 8 or 8.5 to bring in only the top records , but it is a little complex and depends upon your database.

-LB

 

[CognosNewbie99]

I'm pulling in:
Equipment Group (Which is a formula: Left(eqnum,6))
Description
Total Cost (Which is another formula: unitcost * curbal)

I had an issue with the equipment groups but that's taken care of. Now I am sorting by the total cost, descending order. I only want the 100 records with the greatest total cost to show up

 

[lbass]

Your total cost formula appears to be a detail level formula. Did you insert a sum on it at the group level? In this case, all you have to do is go to report->group selection->top: 100->sum of {@cost}->descending.

-LB

 

[CognosNewbie99]

Can I use that formula even if I'm not getting the sum?

 

[lbass]

This is not a formula. Please explain your report structure--have you inserted a group on equipment? Is Total cost a summary at the equipment group level? Or only at the detail level? Are you saying you want to ignore the group structure and allow only the top 100 detail values regardless of group?

-LB

 

[CognosNewbie99]

I was originally not grouping by anything and only wanted the top 100 records. But I figured out a way to do it. I have another question if you are willing to help?
It's a different report. This is the data I pull in:
itemnum
description
unitcost
totalcost(formula:unitcost*curbal)
I am sorting this by unitcost, descending. I only want the top 100 records. Can it be done on this one without grouping?

 

[lbass]

1) Please explain the solution you used for the first report so others can benefit from this thread.

2) I still am not clear whether totalcost is a summary or an actual detail-level database field. One of the two options I noted earlier would still apply--either a group sort, if you can insert a summary, or a record sort and detail suppression.

-LB

 

[CognosNewbie99]

1) In order to use the method you posted (report->group selection->top: 100->sum of {@cost}->descending) I had to insert a summary on the totalcost field. It is just a detail level field, I created a simple formula to give me the required information and placed it in the details section. When I inserted the sum I was forced to create a group, so all I did was suppress the group header and footer so that the report still looked the same then used the method listed above.

2) totalcost is not a summary, it is a simple formula placed in the details section. I can't insert a group then sort because I am originally sorting by the unit cost and that is what I need to group by, but I don't want actual groups, I need every itemnum to be separate from all others.

 

[lbass]

So by top 100 you are looking for the top 100 unit costs? If you sort descending by this field, then suppress the detail section with:

recordnumber > 100

If this is not what you mean, please show a sample of how you want the report to appear, indicating what report sections you are showing.

-LB

 

[CognosNewbie99]

The recordnumber > 100 works. Thank you so much!