Top 100 Rows in RS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Top 100 Rows in RS

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-04-11 : 10:14:20

Hi i'd like to give the user the option to enter in the top X to return the number of rows.

X = a number.
The default will be 100.

Is this possible to do in RS and if so can i get some pointers thanks

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-11 : 10:23:05

Yes you can, you can have txt box or a dropdown that allows user to select a value of Int for the TOP (?).And In the query/sp in dataset refer to the top parameter being passed.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-04-11 : 10:26:30

ok i can't seem to be able to get the text box option am in the reports parameter dialouge box but what options do i select within it to get me my text box ?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-11 : 10:32:58

Apologies rookie for the confusion.You would just need to create a parameter Top of Int data type.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-04-11 : 10:38:07

ok i've created my top parameter of data type int.
Now in my query where do i use it is it at the start as in normal SQL

Select top @top col1,col2
from tbl1

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-11 : 11:08:00

Assuming I have a report parameter Top

DECLARE @Top1 INT
SELECT @Top1 = @Top
SELECT TOP(@Top1) * from dbo.tableA
ORDER BY key DESC

Do try this out.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-04-11 : 11:29:07

Hi i've created my top parameter
and i have put in the code below.. but am getting an error with reagrds the variable @top when i go to run the report i get my text box to enter in the value

Is this line correct "SELECT @Top1 = @Top"

DECLARE @Top1 INT
SELECT @Top1 = @Top
SELECT TOP(@Top1) * from dbo.tableA
ORDER BY key DESC

Do try this out.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-11 : 12:09:17

I have tested this line of code and is correct
DECLARE @Top1 INT
SELECT @Top1 = @Top

SELECT TOP(@Top1) * from dbo.tableA
ORDER BY key DESC

When you execute this query in data tab, it would ask you to enter a value for report parameter Top.To test if the report return correct information.

Do run this same query on your database as
SELECT TOP(5) * from dbo.tableA
ORDER BY key DESC

and compare the results of both.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-04-11 : 12:20:42

Does it matter if you have the liat of column names rather that just the * ?

Also am using SQL 2005 its given me a error on brackets after the word Select TOP (@Top1)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-11 : 12:47:37

Rookie, could you show the error mssg you are getting.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 13:03:44

It wont. Can you post more details of your error?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources