Dataset: EmpSalesMonth

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Data source name    
 Referenced data set   EmpSalesMonth 
 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      
Year Year System.Int32      
MonthNumber MonthNumber System.Int32      
Month Month System.String      
Category Category System.String      
Sales Sales System.Decimal      
Total: 6 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT PER.FirstName + ' ' + PER.LastName AS Employee, 
   DATEPART(Year, SOH.OrderDate) AS [Year], 
   DATEPART(Month, SOH.OrderDate) AS [MonthNumber], 
   DATENAME(Month, SOH.OrderDate) AS [Month], 
   PC.Name AS Category, SUM(DET.LineTotal) AS Sales
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(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate), 
   DATENAME(Month, SOH.OrderDate), PC.Name

Collapse image See also


About this documentation