Asset location report with count ?

[stahly8]

I am currently adding in floor maps to our lansweeper db and I was wondering if a computer count can be added to a report like this?


Select Top 1000000 tblAssets1.AssetID,
tblAssets1.AssetName,
tblAssets1.Description
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Group By tblAssets1.AssetID,
tblAssets1.AssetName,
tblAssets1.Description



Right now the output of the report looks like this:

AssetName, Description
Administration
ServerRoom
Other


But I would like to include a number next to it for how many computers have been assigned to that asset location. example:

AssetName, Description, Number
Administration 11
ServerRoom 43
Other 22



thank you!

[Argon0]

How does one include a count of asset types per location (e.g. I need to see numbers of Desktops, Printers, Network Switches, laotops, etc...).

Many thanks

Argon0

[Daniel_B · Accepted solution]

The only thing missing is a Count of records in tblAssets. In the report editor, select tblAssets.AssetID to be displayed in your report and choose Count as Aggregate function. You already enabled grouping of the other columns.

Select Top 1000000 tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Description,
  Count(tblAssets.AssetID) As Number
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ParentAssetID
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Group By tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Description