Dataset: TopCustomers

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Data source name   AdventureWorks 
 Referenced data set    
 Command type   Query 

Collapse image Parameters


Name 
@ProductSubcategory
@StartDate
@EndDate
@ProductCategory
Total: 4 parameter(s)

Collapse image Fields


Name  Source column  Data type  Caption  Value  Is calculated field 
StoreName StoreName System.String      
SaleAmount SaleAmount System.Decimal      
ProductSubcategoryID ProductSubcategoryID System.Int32      
ProductCategoryID ProductCategoryID System.Int32      
Total: 4 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT TOP 5 S.Name AS StoreName, SUM(SOH.SubTotal) AS SaleAmount, PS.ProductSubcategoryID, 
            PS.ProductCategoryID
FROM        Production.Product P INNER JOIN
            Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
            Sales.SalesOrderDetail SOD ON P.ProductID = SOD.ProductID INNER JOIN
            Sales.Customer CU INNER JOIN
            Sales.SalesOrderHeader SOH ON CU.CustomerID = SOH.CustomerID INNER JOIN
            Sales.Store S ON CU.CustomerID = S.BusinessEntityID ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE       (SOH.OrderDate > @StartDate) AND 
            (SOH.OrderDate < @EndDate) AND (PS.ProductCategoryID = @ProductCategory) AND (PS.ProductSubcategoryID IN (@ProductSubcategory)) 
GROUP BY    S.Name, PS.ProductSubcategoryID, PS.ProductCategoryID
ORDER BY    SUM(SOH.SubTotal) DESC

Collapse image See also


About this documentation