Add to existing Report

[wayneRex]

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

[wayneRex]

That was fast, Thank You.

Not as easy as id hoped

Thanks for the code, works great

[Esben_D]

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