I Need help with stopping at Nth record 1

[dotnetprogrammer]

I need CR to stop from pulling further data, when first 10 records (Not top 10, just first 10 records) are placed on the report. Any help will be appreciated!

 

[Lion3000]

Just using the "Format Formula" (x+2) from the "Format Section" under the pulldown menu: Insert -> Section. Click on the button (x+2) beside the Suppress (No Drill-Down) for the section that displays the records, normally, Details section and write the following code:

RecordNumber > 10

Note: If you are using a group, you have to do the same for (if it is Group 1) Group Header 1 and Group Footer 1 sections.

I hope that would help.

 

[MalcolmW]

What is "first" depends on how your query is constructed, how your table(s) store the data, etc. If you do care which 10 are picked, then you might want to go to the trouble of defining how you want the records ordered, and then you can group and use the Top N feature.
If you don't care which 10 are picked, well, you might as well use the Top N feature cause you don't care which ones are picked as long as there are 10. [sig][/sig]

 

[dotnetprogrammer]

MalcolmW,
Yes, I do care about which 10 are picked, and I have looked at Top N feature, I could be wrong but it seems that CR TopN feature needs user intervention, which I am trying to avoid.

Currently, My SQL data is queried and “preformatted” by MS Access, i.e., grouped, sorted and the TopN is picked, etc. Then I pull that MS Access “preformatted” data into VB/CR8 to generate my reports. The main reason I am using MS Access is because of the ease of picking TopN through it. I can make direct calls to the SQL Server, but I do not know how to limit my query to TopN using SQL command.

I like to skip MS Access and make direct calls to SQL Server using VB code. Eventhough, I am yet to have a “Total Code Solution,” Ossama Gharib has posted a great solution for my need, That solution will greatly simplify my work for now. But, if you have a VB (code) or a query solution for the TopN query, please do help me out.
Thank you for your response!

Ossama Gharib,
Thank you for your response. As I have noted to MalcolmW, that solution you have posted will greatly simplify my work for now. Once again, I do appreciate your help – very much!

fh [sig][/sig]

 

[MalcolmW]

For SQL Server,

SELECT TOP 10 field, anotherfield, etc
FROM etc
WHERE etc
ORDER BY etc

This will return only the first 10 records to CR. As you can't edit the SELECT statement in CR, you will have to use either the Crystal Query tool, or create a stored procedure or view.
[sig][/sig]

 

[dotnetprogrammer]

MalcolmW,
I had tried SELECT TOP 10 field,..., our SQL 6.5 did not respond to it. We are moving to SQL 7.0 warehouse. When the warehouse is up (which may take about a month), first thing I will try is your tip above.
Thanks for being persistent help, I do appreciate it! [sig][/sig]

 

[Guest_imported]

I believe the solution suggested by Lion3000 will suppress printing the records, but will still retrieve them. Someone please correct me if I'm wrong.

I have the same need to retrieve first x (parameter driven) rows from a table that may contain millions of rows.

I want to keep vb out of the solution. in Oracle a simple (where rownum < p-num) would work, but I cannot pass this with CR.