how to "set rowcount" in Crystal Reports XI?

[M39Sthlm]

Hi,

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!!

/M

 

[lbass]

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.

-LB

 

[M39Sthlm]

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?

 

[lbass]

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

-LB

 

[M39Sthlm]

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.

 

[M39Sthlm]

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. ;-)

 

[lbass]

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.

-LB