Dataset: EmployeeSalesYearOverYear.rsd

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Path   C:\Users\Alen\Desktop\SSRS items\AdventureWorks 2012 SSRS Samples - Copy\EmployeeSalesYearOverYear.rsd 
 Data source   AdventureWorks 

Collapse image Parameters


Name  Data type  Nullable  Omit from query  Read only 
@ReportYear Object False False False
@ReportMonth Object False False False
@EmployeeID Object False False False
Total: 3 parameter(s)

Collapse image Fields


Name  Field source column  Data type 
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 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