Dataset: EmpSalesMonth.rsd

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Path   C:\Users\Alen\Desktop\SSRS items\AdventureWorks 2012 SSRS Samples - Copy\EmpSalesMonth.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
Category Category System.String
Sales Sales System.Decimal
Total: 6 field(s)

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