facing an issue while creating an asset report with the user's department

[israr0333]

issue while creating an asset report with the user's department ,i can see only Laptop not desktop and i want to see both, code is attached.

[Mercedes_O]

Thank you for taking time to provide the answer to others

[israr0333 · Accepted solution]

i manage to fix myself by creating left join on Username and Userdomain as seen in the report ,i believe very good report now can enable lots of things in one report,  Sharing the code if someone also need a help.

Select Top (1000000) Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblADComputers.OU
From tblAssets
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Left Outer Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tsysAssetTypes.AssetTypename In ('Windows', 'Windows CE') And
tblState.Statename = 'Active'
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10),
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblOperatingsystem.Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End,
tsysAssetTypes.AssetTypename,
tblADComputers.OU,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADusers.Department
Order By [Desktop/Laptop],
tblAssets.Username,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Department,
tblAssets.Domain