Cannot limit results with ReportViewer

Hello,
I have been attempting to set up a report using the ReportViewer
control in VS2005. The report graphs the TOP X number of records based
on certain criteria. Naturally, I modified the report with a filter
for the TOP N results. This works, however, in the case of ties on the
last entry, all tied instances will get graphed. In my case, this may
be alot of entries causing the bar graph image to display unreadable
data since there are too many result to graph.
So, I attempted the following with no success since the server is SQL
2000 and not SQL 2005:
- Using the TOP keyword in the select query does not work with a
parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
- I cannot use a stored procedure and use the "SET ROWCOUNT" command
with the select query.
- I cannot use the LIMIT keyword in SQL 2000.
Can anyone come up with a solution, other than upgrading to SQL 2005?
Thanks in advance,
Andrewcheck this one......
SELECT w1.CATEGORYID, w1.UNITSINSTOCK,COUNT(*) As Rank
FROM
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w1
INNER JOIN
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w2
ON w1.CATEGORYID = w2.CATEGORYID
AND w1.UNITSINSTOCK <= w2.UNITSINSTOCK
GROUP BY w1.CATEGORYID, w1.UNITSINSTOCK
HAVING COUNT(*) <=@.parameter
ORDER BY w1.CATEGORYID,w1.UNITSINSTOCK DESC
Regards
Dinesh
therealbigcoops@.gmail.com wrote:
> Hello,
> I have been attempting to set up a report using the ReportViewer
> control in VS2005. The report graphs the TOP X number of records based
> on certain criteria. Naturally, I modified the report with a filter
> for the TOP N results. This works, however, in the case of ties on the
> last entry, all tied instances will get graphed. In my case, this may
> be alot of entries causing the bar graph image to display unreadable
> data since there are too many result to graph.
> So, I attempted the following with no success since the server is SQL
> 2000 and not SQL 2005:
> - Using the TOP keyword in the select query does not work with a
> parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
> - I cannot use a stored procedure and use the "SET ROWCOUNT" command
> with the select query.
> - I cannot use the LIMIT keyword in SQL 2000.
> Can anyone come up with a solution, other than upgrading to SQL 2005?
> Thanks in advance,
> Andrew