Request of a report

[jeost]

Hi, Im having trouble makeing a report to gather som information. Im trying to make a reprot that shows AssetName AssetTypename, Memory, NrProcessors, Processor ,Mac ,Location, Manufacturer,username, and userdepartment. It is the user depart men i am haven problems getting to work.

This is what i got so far:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Mac,
tblAssetCustom.Location,
tblAssetCustom.Manufacturer,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

Regradt Jeost

[Daniel_B · Accepted solution]

The user department is scanned from Active Directory and stored in tblADusers. You need to add this table to your report, join it through the columns Username and Userdomain to tblAssets and select the user department to be listed in your report.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblAssets.Memory,
  tblAssets.NrProcessors,
  tblAssets.Processor,
  tblAssets.Mac,
  tblAssetCustom.Location,
  tblAssetCustom.Manufacturer,
  tblAssets.Username,
  tblADusers.Department
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1