→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
wayneRex
Engaged Sweeper II
Hello,
Im new to creating reports, i have made a report which is working fine. But found code on the forum which i would like to add to the report. How do i merge the code into my existing report?

Would like to add this

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen As [last scan],
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen
Order By tblAssets.AssetID


Into my report

Select Top 1000000 tblAssets.AssetName,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssetCustom.Model,
tblAssets.Username,
tblADComputers.Description,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.AssetID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.IPAddress Like '10.86.%.%'
Order By tblAssets.AssetName
2 REPLIES 2
wayneRex
Engaged Sweeper II
That was fast, Thank You.

Not as easy as id hoped

Thanks for the code, works great
Esben_D
Lansweeper Employee
Lansweeper Employee
Merging two reports requires understanding of how both reports work. Basically you will have to add the tables and fields from the first report which are missing in the second report to the second report. However the report you want to merge has some aggregations in it which makes it a bit more complex.
  1. Add the table tblDiskdrives to your report.
  2. Copy and paste the two "Sum" lines into your report in your Select statement (above "From", be weary of commas which might cause an error message).
  3. Because you use an aggregation, you will have to group all fields except for the Sum fields, so tick the group box for every other field.
  4. The order by can be done as you did in your report
The result should be the following:
Select Top 1000000 tblAssets.AssetName,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssetCustom.Model,
tblAssets.Username,
tblADComputers.Description,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.AssetID,
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssets.IPAddress Like '10.86.%.%'
Group By tblAssets.AssetName,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssets.Username,
tblADComputers.Description,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.AssetID
Order By tblAssets.AssetName,
tblAssets.AssetID