Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

M39Sthlm (TechnicalUser) (OP)
13 Jan 10 10:03
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!! smile

/M
lbass (TechnicalUser)
13 Jan 10 12:18
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 (TechnicalUser) (OP)
13 Jan 10 12:37
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 (TechnicalUser)
13 Jan 10 12:45
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 (TechnicalUser) (OP)
13 Jan 10 12:56
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 (TechnicalUser) (OP)
14 Jan 10 8:58
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
lbass (TechnicalUser)
14 Jan 10 9:25
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

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!

Back To Forum

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