Dataset: EmployeeSalesYearOverYear

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Data source name    
 Referenced data set   EmployeeSalesYearOverYear 
 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      
Sales Sales System.Decimal      
Total: 5 field(s)

Collapse image Filters


No filters exist

Collapse image Query


SELECT        P.FirstName + ' ' + P.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS Year, DATEPART(Month, SOH.OrderDate) AS MonthNumber, DATENAME(Month, 
                         SOH.OrderDate) AS Month, SUM(DET.LineTotal) AS Sales
FROM            Sales.SalesPerson AS SP INNER JOIN
                         Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN
                         Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID INNER JOIN
                         Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID INNER JOIN
                         HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID INNER JOIN
                         Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
WHERE        (DATEPART(Year, SOH.OrderDate) <= @ReportYear - 1 OR
                         DATEPART(Year, SOH.OrderDate) = @ReportYear AND DATEPART(Month, SOH.OrderDate) <= @ReportMonth) AND (SOH.SalesPersonID = @EmployeeID)
GROUP BY P.FirstName + ' ' + P.LastName, SOH.SalesPersonID, DATEPART(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate), DATENAME(Month, SOH.OrderDate)

Collapse image See also


About this documentation