
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-04-2018 12:11 PM
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
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
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
Labels:
- Labels:
-
Report Center
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-04-2018 03:18 PM
That was fast, Thank You.
Not as easy as id hoped
Thanks for the code, works great
Not as easy as id hoped

Thanks for the code, works great

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-04-2018 01:55 PM
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.
- Add the table tblDiskdrives to your report.
- 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).
- 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.
- The order by can be done as you did in your 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,
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
