Adding user's manager

[blighter]

Hi,

I've been playing around all day but unable to get the user's manager showing in an asset list report. Does anybody have any ideas? 🙂

thanks

Select Top 1000000 tblState.Statename,
  tblAssetCustom.Custom5 As [IT Loan?],
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Custom6,
  tblAssets.Username,
  tblAssets.Lastseen As Updated,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Processor,
  tblAssets.Memory,
  tblADComputers.OU,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblADusers.Picture,
  tblADusers.ManagerADObjectId
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblAssetUserRelations On tblAssets.AssetID =
    tblAssetUserRelations.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
    And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tblState.Statename != 'Sold' And tblAssets.AssetName Like 'LON%'
  And tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName,
  tblState.Statename

[Hemoco · Accepted solution]

Use the report below. Note that we removed tblAssetUserRelations from your query, as the inclusion of this table didn't make sense to us. The tblAssets.Username field (last logged on user) has nothing to do with the tblAssetUserRelations.Username field (users linked to computer).

Select Top 1000000 tblState.Statename,
tblAssetCustom.Custom5 As [IT Loan?],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Custom6,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers1.Username As ManagerName,
tblADusers1.Userdomain As ManagerDomain,
tblAssets.Lastseen As Updated,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Processor,
tblAssets.Memory,
tblADComputers.OU,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblADusers.Picture,
tblADusers.ManagerADObjectId
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tblADusers tblADusers1 On tblADusers1.ADObjectID =
tblADusers.ManagerADObjectId
Where tblState.Statename != 'Sold' And tblAssets.AssetName Like 'LON%'
And tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName,
tblState.Statename