I want to do something like this: SELECT TOP @variable. I cannot use stored procedure for this report, 'cause I do not have access to prod db, I'm just sending rdl for upload. How to make it work on dataset level or through the report layout?I know SQLServer doesn't recognize "Select Top @variable" statement. SELECT top &variable, Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_numberWHERE GROUP BY Ticket.problemOrSELECT Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_numberWHERE GROUP BY Ticket.problemHAVING (COUNT(Solutions.solution_date) > CAST(@num AS int)) doesn't work Please, help!

You can use an expression based query, i.e.="SELECT TOP (" & Parameters!Variable.Value & ") Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]FROM Ticket INNER JOIN Solutions ON Ticket.ticket_number = Solutions.ticket_number WHERE GROUP BY Ticket.problem"

you can use also "set rowcount " command (sql)ex : --setup the max number of rowsSet rowcount @topSelect ..... from XXX-- reinitializeset rowcount 0

Is there a way to do this from within Visual Studio 2005? For example, if I open the Data tab for a report that currently returns 7000+ rows, I can enter "TOP 100" directly into the SELECT statement and all works as expected. But when I try either of the methods mentioned above, I get syntax errors.

Hiyou can do without any Dynamic Query---------declare @NoOfRecordsSet @NoOfRecords = 10set Rowcount @NoOfRecordsSELECT * FROM TABLENAMESET Rowcount 0

If you are using SQL Server 2005 the following should do the needfuldeclare @limit intSet @limit=5select top(@limit) * from TableName

Unfortunately I'm stuck with SQL 2K.

Rather than using ROWCOUNT you can use the following statement:CREATE PROCEDURE TEST@TestInt INTASBEGIN DECLARE @SqlQuery NVARCHAR(4000); SET @SqlQuery = 'SELECT TOP '+CAST(@TestInt as CHAR)+'* FROM TABLE_TEST'; EXEC(@SqlQuery)END

I was trying something like this, but I was just forgetting the parenthesis XD Thank you!

it was helpful