Ihave a report based on a dataset that has a "top()" statement in it. SELECT TOP (10)name, valFROMtab ORDER BY val DESC I wanted wanted to return a report with 10 rows as the default. But allow the user to change the default and regenerate the report with more rows. I tried the following; SELECT TOP (@N)name, valFROMtab ORDER BY val DESC Normally in VS2005, the paraemeters work fine for things like the WHERE clause. But when i do the TOP(@N) the query/report parameter synchronization messes up. If i manual go to the parameter tab of the dataset and configure the matching between query and report params it works. But doing anything to the dataset resets this. Its getting very tiring. Any advice for a newbie would be appreciated. John

Hi,you cannot do that without building dynamic SQL which is not best practice leaving you exposed to risks.What you could do if your TOP parameter list is a constrained list like top = 10 or 20 or 50 or 100 or ALL would be to build your query with a series of If or Case statements where you evaluate the value of the TOP parameter and then build the select top query accordingly.Another related question I have posted in this forum was how can I get the "Other" lumped into an extra result row. No easy answer so far, I wait for the silver bullet.Philippe

Lets say i opt for the constrained list of top values as you suggest and write a use if/case as you suggest. Would this not be also be dynamic SQL? I saw another thread where someone was looking for a column that with a rank of the sorted rows. rank name val 1 sam 9.4 2 ted 5.8 3 bob 2.4 if i could do this in the dataset sql, i could return all rows, then i could filter the rows in the report table with rank <= @N

I tried your if suggestion. Works in the dataset designer wizard in VS2005 just after entery sql code. But then testing the report it doesnt. entering the dataset wizard again get me this error, "The Compound statement SQL construct or statement is not supported." code: if (@N = 10) begin SELECT TOP (10)name, valFROMtab ORDER BY val DESC end if (@N = 20) begin SELECT TOP (20)name, valFROMtab ORDER BY val DESC end if (@N = 999) begin SELECT name, valFROMtab ORDER BY val DESC end

I guess there are many ways to do it, here is a working example:USE AdventureWorksDECLARE @TOP INT SET @TOP = 10SELECT TOP (CASE @TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)c.LastName, SUM(s.SubTotal) as SubTotalFROM Sales.SalesOrderHeader sINNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactIDGROUP BY c.LastNameORDER BY SUM(s.SubTotal) DESC;Philippe

That is much simpler. Thx I have one question however; this works in a report i configured SELECT TOP (CASE @TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)c.LastName, SUM(s.SubTotal) as SubTotalFROM Sales.SalesOrderHeader sINNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactIDGROUP BY c.LastNameORDER BY SUM(s.SubTotal) DESC; why doesnt this form work SELECT TOP (@TOP)c.LastName, SUM(s.SubTotal) as SubTotalFROM Sales.SalesOrderHeader sINNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactIDGROUP BY c.LastNameORDER BY SUM(s.SubTotal) DESC; it works in the dataset wizard. when i test the query the wizard prompts for @top and the results work however i run the run the report and the i get error.You mentioned dynamic queries previously.is you form not dynamic? The @top param has to be evaluated at runtime either way. john

If you're using SQL 2005, I suggest looking at ranking functions. It's quite handy, with ROW_NUMBER() http://www.aspfaq.com/sql2005/show.asp?id=11 http://www.sql-server-performance.com/ak_ranking_functions.asp http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk I don't think the previous codeis dynamic query, as TOP was a declared variable not something like EXEC 'SELECT TOP ' + @TOP + ' * FROM table' where you may get unwanted input (injection attack?)

I had the same problem and my solution is as follows. I created a parameterized stored procedure and used it as the source of the dataset with the default for top N.Then simply passed the parameter in RS to the sp. P.