Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


how to "set rowcount" in Crystal Reports XI?

how to "set rowcount" in Crystal Reports XI?

how to "set rowcount" in Crystal Reports XI?


I'm building a report against sybase sql anywhere 8.

I need a top N limitation to 10 in my query. I know that it works if using "set rowcount 10" before sql query and then "set rowcount 0" after. I have succesfully tried this in interactive sql, no problem

But how do I implement this in my crystal report?? Help would be greatly appreciated!! smile


RE: how to "set rowcount" in Crystal Reports XI?

TopN of what? A group? Individual records? If you mean the top 10 of some group value, then go to report->group sort->topN: 10. If of all records, you can either use record sorting (descending) and then suppression:

recordnumber > 10

...or you can use a command as your datasource where you build the top10 requirement directly into the query.


RE: how to "set rowcount" in Crystal Reports XI?

Top N of individual recorts.

I will look at record sorting with supression, will that work in a crosstab?

My crosstab and charts are placed in the report header.

About your last interesting suggestion:

How do i use a command as my datasource building top 10 directly into the query? This sounds interesting, where can I find some info about this technique?

RE: how to "set rowcount" in Crystal Reports XI?

To use the command feature, when you open a new report, select "add command" (above your list of tables), and then ONLY use this as your datasource for the report.

The syntax depends upon your database. Using Access, a command would look something like this:

Select top 10 table.`amt`, table.`ID`
from table
where table.`ID` < 1000 //just to show a where clause
Order by table.`amt` desc


RE: how to "set rowcount" in Crystal Reports XI?

Thank you, I will try.

I'm using Sybase SQL Anywhere 8 as database.

I have a sql query working in interactive sql tool.

If I use

set rowcount 10
my sql query.....
set rowcount 0

So I will need to add my s2et rowcount 10" line before the actual query beings. The last set rowcount 0 may not be necessary since it's a report.

RE: how to "set rowcount" in Crystal Reports XI?

lbass: Thank you very much. The command feature in Crystal Reports is extremely good and flexible. A very good alternative to using views or procedures. This did the trick. I realized now that sybase sql anywhere 8.03 also works with a simple "select top 10" query! set rowcount 10 may be petter performance wise though.
My problem now is switching to the command feature without having to rewrite my reports. winky smile

RE: how to "set rowcount" in Crystal Reports XI?

I'm not sure of the best way to do the switch. I don't think you can simply reset the datasource location, since multiple tables are now referenced in one command.

What I've been doing is creating the command and adding it to the existing report and then going into all formula areas (including formatting), running totals, charts, etc., and replacing table names with "command". With charts, I added the new field, and only then removed the field it was replacing so that the chart features were still maintained. Then when all references to the tables have been eliminated, I remove the tables.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close