I have a report based on a dataset that has a "top()" statement in it.
SELECT TOP (10) name, val FROM tab 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, val FROM tab 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, val FROM tab ORDER BY val DESC
end
if (@.N = 20)
begin
SELECT TOP (20) name, val FROM tab ORDER BY val DESC
end
if (@.N = 999)
begin
SELECT name, val FROM tab ORDER BY val DESC
end
|||I guess there are many ways to do it, here is a working example:USE AdventureWorks
DECLARE @.TOP INT
SET @.TOP = 10
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 SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER 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 SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;
why doesnt this form work
SELECT TOP (@.TOP)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER 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 code is 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?)
