Dataset: EmployeeSalesDetail.rsd

CollapseAll image

Collapse image Dataset properties


 Property   Value 
 Path   C:\Users\Alen\Desktop\SSRS items\AdventureWorks 2012 SSRS Samples - Copy\EmployeeSalesDetail.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
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 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