Dataset: EmployeeSalesDetail

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Data source name    
 Referenced data set   EmployeeSalesDetail 
 Command type    

Collapse image Parameters


No parameters exist

Collapse image Fields


Name  Source column  Data type  Caption  Value  Is calculated field 
Employee Employee System.String      
Subcategory Subcategory System.String      
Sales Sales System.Decimal      
SalesOrderID SalesOrderID System.Int32      
SalesOrderNumber SalesOrderNumber System.String      
Product Product System.String      
OrderQty OrderQty System.Int32      
UnitPrice UnitPrice System.Decimal      
Category Category System.String      
Total: 9 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT PER.FirstName + ' ' + PER.LastName AS Employee,
   PS.Name AS Subcategory, SUM(DET.LineTotal) AS Sales, 
   SOH.SalesOrderID,  SOH.SalesOrderNumber, 
   P.Name AS Product, 
   SUM(DET.OrderQty) AS OrderQty, DET.UnitPrice, 
   PC.Name AS Category
FROM Sales.SalesOrderHeader SOH 
   INNER JOIN [Sales].[SalesPerson] SP ON SP.[BusinessEntityID] = SOH.[SalesPersonID]
   INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
   INNER JOIN [HumanResources].[Employee] E ON SOH.[SalesPersonID] = E.[BusinessEntityID] 
   INNER JOIN [Person].[Person] PER ON PER.[BusinessEntityID] = SP.[BusinessEntityID]
   INNER JOIN Production.Product P ON DET.ProductID = P.ProductID 
   INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID 
   INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (DATEPART(Year, SOH.OrderDate) = @ReportYear) AND 
   (DATEPART(Month, SOH.OrderDate) = @ReportMonth) AND 
    (SOH.SalesPersonID = @EmployeeID)
GROUP BY PER.FirstName + ' ' + PER.LastName, 
   DATEPART(Month, SOH.OrderDate), SOH.SalesOrderID, SOH.SalesOrderNumber, 
   P.Name, PS.Name, DET.UnitPrice, PC.Name

Collapse image See also


About this documentation