Custom Report - How to make columns clickable?

[Filo]

Hi!
I have built some custom reports in order to get Lansweeper to accomplish the task of our central CMDB.
For example, this report is showing all desktop-devices:

Select Top 1000000 tblAssets.AssetName As [Asset Name | Hostname],
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model As Model,
Case When tblBattery.Availability Is Not Null Then 'Notebook' Else 'Desktop'
End As Type,
tblAssets.Username As Owner,
tblAssetCustom.Serialnumber As [Serial-No.],
tsysOS.Image As icon,
tsysOS.OSname As [Operating System],
tblAssets.Memory As RAM,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As Harddisk,
tblAssets.IPAddress As [IP Address],
tblAssets.Mac As [Mac Address],
tblAssetCustom.Custom1 As Installdate,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Enddate],
tblState.Statename,
tblAssets.AssetID As [Asset-ID]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Model <> 'VMWare Virtual Platform' And
tblAssetCustom.Model <> 'Virtual Machine' And tsysOS.OSname <> 'Win 2003' And
tsysOS.OSname <> 'Win 2003 R2' And tsysOS.OSname <> 'Win 2008' And
tsysOS.OSname <> 'Win 2008 R2' And tsysOS.OSname <> 'Win 2012' And
tsysOS.OSname <> 'Win 2012 R2' And tblBattery.Availability Is Null And
tblAssetCustom.State = 1 And tblDiskdrives.Caption = 'c:'
Order By [Asset Name | Hostname]

What it shows is this

As you can see, the columns are not clickable wheras the model-name is. Espacially the one with the asset-name or owner would interest us, to be able to click to the asset itself or the owner from within the report. This feature is often found in Lansweeper's standard-reports but I've not been able to reproduce it yet.

Is there a chance to modify the SQL (I'm no expert to this) in order to get this task accomplished?

Many thanks in advance for any answer to this!

[Filo]

Great answer - thanks alot!
It was a matter of the Alias-Names I had choosen.

Reverting to the names you posted was the solution!

Thanks!

[Nick_VDB · Accepted solution]

Not all fields can be made clickable. The ones that are clickable can be made so through certain keywords or field combinations. For example these are a couple that we can come up with:

• Assetname: Requires the tblAssets.assetID field and the field must have no Alies. It also requires the tblAssets.Assetname field to not have an alias
  
• tsysOS.Image must have its alias set to 'icon' for the OS icon to appear in the report
  
• tblAssetCustom.Manufacturer must have its alias set to 'Manufacturer'
  
• tblAssetCustom.Model must have its alias set to 'Model'
  
• tblAssets.Domain must have its alias set to 'Domain'

You can always check the built-in reports and look up what aliases they have as these are the most likely requirement for the field to be linkable.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Manufacturer As Manufacturer,
  tblAssetCustom.Model As Model,
  Case When tblBattery.Availability Is Not Null Then 'Notebook' Else 'Desktop'
  End As Type,
  tblAssets.Username As Owner,
  tblAssetCustom.Serialnumber As [Serial-No.],
  tsysOS.Image As icon,
  tsysOS.OSname As [Operating System],
  tblAssets.Memory As RAM,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As Harddisk,
  tblAssets.IPAddress As [IP Address],
  tblAssets.Mac As [Mac Address],
  tblAssetCustom.Custom1 As Installdate,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Enddate],
  tblState.Statename
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
  Left Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Model <> 'VMWare Virtual Platform' And
  tblAssetCustom.Model <> 'Virtual Machine' And tsysOS.OSname <> 'Win 2003' And
  tsysOS.OSname <> 'Win 2003 R2' And tsysOS.OSname <> 'Win 2008' And
  tsysOS.OSname <> 'Win 2008 R2' And tsysOS.OSname <> 'Win 2012' And
  tsysOS.OSname <> 'Win 2012 R2' And tblBattery.Availability Is Null And
  tblAssetCustom.State = 1 And tblDiskdrives.Caption = 'c:'
Order By tblAssets.AssetName

[tmui]

Nick.VDB wrote:
Not all fields can be made clickable. The ones that are clickable can be made so through certain keywords or field combinations. For example these are a couple that we can come up with:

• Assetname: Requires the tblAssets.assetID field and the field must have no Alies. It also requires the tblAssets.Assetname field to not have an alias
  
• tsysOS.Image must have its alias set to 'icon' for the OS icon to appear in the report
  
• tblAssetCustom.Manufacturer must have its alias set to 'Manufacturer'
  
• tblAssetCustom.Model must have its alias set to 'Model'
  
• tblAssets.Domain must have its alias set to 'Domain'

You can always check the built-in reports and look up what aliases they have as these are the most likely requirement for the field to be linkable.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Manufacturer As Manufacturer,
  tblAssetCustom.Model As Model,
  Case When tblBattery.Availability Is Not Null Then 'Notebook' Else 'Desktop'
  End As Type,
  tblAssets.Username As Owner,
  tblAssetCustom.Serialnumber As [Serial-No.],
  tsysOS.Image As icon,
  tsysOS.OSname As [Operating System],
  tblAssets.Memory As RAM,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As Harddisk,
  tblAssets.IPAddress As [IP Address],
  tblAssets.Mac As [Mac Address],
  tblAssetCustom.Custom1 As Installdate,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Enddate],
  tblState.Statename
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
  Left Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Model <> 'VMWare Virtual Platform' And
  tblAssetCustom.Model <> 'Virtual Machine' And tsysOS.OSname <> 'Win 2003' And
  tsysOS.OSname <> 'Win 2003 R2' And tsysOS.OSname <> 'Win 2008' And
  tsysOS.OSname <> 'Win 2008 R2' And tsysOS.OSname <> 'Win 2012' And
  tsysOS.OSname <> 'Win 2012 R2' And tblBattery.Availability Is Null And
  tblAssetCustom.State = 1 And tblDiskdrives.Caption = 'c:'
Order By tblAssets.AssetName

Is there any way to get a link for the following field: tblHyperVGuest.Name