Drilldown Report Writer with Charting

��USE [Northwind]

GO

/****** Object:  StoredProcedure [dbo].[Report$Sales$Manager$Sales_by_Customer_en]    Script Date: 02/24/2010 12:15:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[Report$Sales$Manager$Sales_by_Customer_en]

@Start_Date datetime,

@End_Date datetime

AS

/*

EXEC Report$Sales$Manager$Sales_by_Customer_en @Start_Date='1997/07/16', @End_Date='2009/01/01'

*/

BEGIN

	SET NOCOUNT ON;



	select top 10

		Customers.CustomerID as Ident

		,Customers.CompanyName

		,convert(varchar(40), 

			convert(money,(sum([Order Details].UnitPrice*Quantity*(1-Discount))))

		, 0) as Total

		, sum([Order Details].UnitPrice*Quantity*(1-Discount)) as Total1

	from 

		[Order Details]

	left join 

		Products on Products.ProductID = [Order Details].ProductId

	left join 

		Categories on Categories.CategoryID=Products.CategoryID

	left join 

		Orders on Orders.OrderID = [Order Details].OrderID

	left join 

		Customers on Customers.CustomerID = Orders.CustomerID

	where

		ShippedDate between @Start_Date and @End_Date

	group by

		CompanyName, Customers.CustomerID

	order by

		Total1 desc

END