AD users and computers report

[kdunnett]

Hi there,

I have this report that I am trying to add model information to, but when I add model in tableassetcustom I get the following error.

Column 'tblAssetCustom.Model' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


Select Distinct Top 1000000 SubQuery2.Username,
SubQuery2.Userdomain,
tblADusers.Name,
tblAssets.Lastseen,
SubQuery2.ComputersLoggedInto,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model
From (Select Top 1000000 SubQuery1.Username,
SubQuery1.Domain As Userdomain,
Count(SubQuery1.AssetID) As ComputersLoggedInto
From (Select Distinct Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblCPlogoninfo.AssetID
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Where tblAssetCustom.State = 1) SubQuery1
Group By SubQuery1.Username,
SubQuery1.Domain) SubQuery2
Left Join tblADusers On tblADusers.Username = SubQuery2.Username And
tblADusers.Userdomain = SubQuery2.Userdomain
Inner Join tblCPlogoninfo On tblCPlogoninfo.Username = SubQuery2.Username And
tblCPlogoninfo.Domain = SubQuery2.Userdomain
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Group By SubQuery2.Username,
SubQuery2.Userdomain,
tblADusers.Name,
tblAssets.Lastseen,
SubQuery2.ComputersLoggedInto,
tblAssets.AssetID,
tblAssets.AssetName
Order By SubQuery2.Userdomain,
SubQuery2.Username

[Esben_D · Accepted solution]

If you use an aggregate anywhere in your SQL query. Non-aggregated fields must be grouped. You forgot to group tblAssetsCustom.Model.

If you check the Grouping checkbox in the report builder, the report should run.