cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
stahly8
Engaged Sweeper III
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!
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
Argon0
Champion Sweeper
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
Lansweeper Alumni
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