| 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? |
|
|
|
|
Top 100 Rows in RS