Managers/Employees, Equipment/Warranty

[E__L__Gray]

I have a manager requesting all machines 'under' him, and their warranty.

Can somebody help make a report to gather this information?

I see under any given manager "Direct Reports" but it only shows 4 (example) people. I need the people under them, and under them, etc. along with the name of the machine they are using and the warranty of the machine they are using.

The short story - management would like to begin rotating older machines out.

[E__L__Gray]

Kind of messy, but if that's the only way it works I'll see what we can do about it.

Thanks for the response.

[Susan_A · Accepted solution]

I don't think there's an easy way to cascade all the way down the chain with SQL, but this sample report lists managers and the computers of two levels of "direct reports".

Select Top 1000000 tblADusers.Username As Manager,
  tblADusers.Userdomain As ManagerDomain,
  tblADusers1.Username As DirectReportLevel1,
  tblAssets.AssetID,
  tblAssets.AssetName As Lvl1Asset,
  tblAssets.Domain As Lvl1Domain,
  tblAssetCustom.PurchaseDate As Lvl1PurchaseDate,
  tblAssetCustom.Warrantydate As Lvl1WarrantyDate,
  tblADusers2.Username As DirectReportLevel2,
  tblAssets1.AssetName As Lvl2Asset,
  tblAssets1.Domain As Lvl2Domain,
  tblAssetCustom1.PurchaseDate As Lvl2PurchaseDate,
  tblAssetCustom1.Warrantydate As Lvl2WarrantyDate
From tblADusers
  Left Join tblADusers tblADusers1 On tblADusers.ADObjectID =
    tblADusers1.ManagerADObjectId
  Left Join tblAssets On tblAssets.Username = tblADusers1.Username And
    tblAssets.Userdomain = tblADusers1.Userdomain
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADusers tblADusers2 On tblADusers1.ADObjectID =
    tblADusers2.ManagerADObjectId
  Left Join tblAssets tblAssets1 On tblAssets1.Username = tblADusers2.Username
    And tblAssets1.Userdomain = tblADusers2.Userdomain
  Left Join tblAssetCustom tblAssetCustom1 On tblAssets1.AssetID =
    tblAssetCustom1.AssetID
Order By ManagerDomain,
  Manager,
  DirectReportLevel1,
  DirectReportLevel2

[E__L__Gray]

This is what I have. It's ALMOST what I need, but it needs to break down further. In example, my manager has a few people under him that also manage employees. I can only see those that are managed by my manager, unless I search for the next level manually. Is there a way to break it down to show all under my manager AND the managers under him?

Select Top 1000000 tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate,
tblADusers.Name As [AD.Username],
tblAssetCustom.Department,
tblAssets.AssetID,
tManager.Username As Manager,
tblAssetCustom.Building,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation' End As type,
tblComputersystem.AssetID
From tblComputersystem) tComputersystem On tblAssets.AssetID =
tComputersystem.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Left Join tblADusers tManager On tblADusers.ManagerADObjectId =
tManager.ADObjectID
Where tblADusers.Username <> tblAssetCustom.Custom9

Hopefully worded a bit better.