November 20, 2007 at 3:10 pm
I'm using Reporting Services 2005. Can you please tell me how I can display the top 5 products and the user can expand each product to see the detail info. In the report I group by Product but can not filter the group base on the item counts to only show the top 5 products. I can accomplish pulling the top 5 product in a stored proc but can get the detail if I use stored proc.
top 5 products
Product
Pencils 150
Pens 130
Staples 70
Erasers 50
Whiteout 10
expand each product to see detail
Pencils
John11/1/200720
Sue11/15/200750
Jane11/13/200740
Jim11/10/200740
Pens
etc...
Any help/suggestion is greatly appreciated.
Thanks
November 20, 2007 at 5:35 pm
Something like this should work (change your table and field names, as necessary):
SELECT s.*, p.ProductName, t.TotalSales --Might as well include the group total
FROM SalesLineItem AS s
INNER JOIN (
SELECT TOP 5 WITH TIES ProductID, TotalSales = Sum(LineTotal)
FROM SalesLineItem
GROUP BY ProductID
ORDER BY Sum(LineTotal) DESC
) AS t ON t.ProductID = s.ProductID
INNER JOIN Product AS p on p.ProductID = s.ProductID
November 28, 2007 at 12:16 pm
Check this out:
http://www.simple-talk.com/sql/sql-server-2005/reporting-at-the-top/
This is a link to a similar report. It was sent in the SQL Server Central daily email on 11/21.
I have created a report like it, and it works well.
Bill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply