cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Detail asset report

ChrisK
Engaged Sweeper II
New to Lansweeper. I need a full detail report similar to the report tab when looking at an asset for everything in inventory.

1 ACCEPTED SOLUTION

Andy_Sismey
Champion Sweeper III
You would probably need to do something like this report, with an indent, you will need to add additional columns, but this should get you started, then export to Excel :

Select Top 1000000 Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassets.AssetName
Else Null
End As 'Name',
Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassetcustom.Manufacturer
Else Null
End As 'Manufacturer',
Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassetcustom.Model
Else Null
End As 'Model',
Query1.softwareName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID) Query1 On Query1.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1

View solution in original post

4 REPLIES 4

ChrisK
Engaged Sweeper II
Thaks I can work with that

Andy_Sismey
Champion Sweeper III
You would probably need to do something like this report, with an indent, you will need to add additional columns, but this should get you started, then export to Excel :

Select Top 1000000 Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassets.AssetName
Else Null
End As 'Name',
Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassetcustom.Manufacturer
Else Null
End As 'Manufacturer',
Case
When Row_Number() Over (Partition By tblassets.AssetName Order By
Query1.softwareName) = 1 Then tblassetcustom.Model
Else Null
End As 'Model',
Query1.softwareName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftware.AssetID
From lansweeperdb.dbo.tblSoftware
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID) Query1 On Query1.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1

Andy_Sismey
Champion Sweeper III
Have a look at this one : https://www.lansweeper.com/forum/yaf_postst14691_List-all-assets-report.aspx#post50226

ChrisK
Engaged Sweeper II
Andy.S wrote:
Have a look at this one : https://www.lansweeper.com/forum/yaf_postst14691_List-all-assets-report.aspx#post50226


Thanks for the quick reply. Not exactly what I'm looking for.

When looking at an individual asset and you click on the Report tab you can see hardware detail and software installed. I need this detail for all of my assets and ability to export to pdf would be a bonus.