Two AssetName colums clickable

[kschmeling]

Hello,

I'm new to Lansweeper and I have basic knowledge of SQL.

We are using Lansweeper to track corporate smartphones and SIM cards. We created the Smartphone and SIM card categories and registered a smartphone and a sim card to test, then created relations between the two.

The idea now is to create a report that shows information about the smartphone and, if there is a sim card linked to this smartphone, show the SIM card number. If there is not a SIM card number linked, it shows a blank result.

After a lot of trying and errors, I got a working report:



If I hover the mouse over the AssetName, I can see the information about the asset.



But I can't do the same thing with a SIM card columm. I know the problem is the SQL alias.

There is any way to allow us to see details and click on both columms (AssetName and SIM Card - Both are assets)

Here is the SQL (If you have a better way to do the query, I accept suggestions.):

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassetcustom.Manufacturer As Manufacturer,
  tblassetcustom.Model As Model,
  tblassetcustom.Custom2 As [IMEI 1],
  tblassetcustom.Custom3 As [IMEI 2],
  Query2.Displayname As [User],
  Query2.Department As Department,
  Query2.Title As Title,
  Query4.AssetName As [SIM Card]
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join (Select tblAssetUserRelations.Username,
      tblAssetUserRelations.AssetID
    From tblAssetUserRelations) Query1 On Query1.AssetID = tblassets.AssetID
  Inner Join (Select tblADusers.Displayname,
      tblADusers.Username,
      tblADusers.Department,
      tblADusers.Title
    From tblADusers) Query2 On Query2.Username = Query1.Username
  Inner Join (Select tblAssetRelations.ParentAssetID,
      tblAssetRelations.ChildAssetID
    From tblAssetRelations) Query3 On Query3.ParentAssetID = tblassets.AssetID
  Inner Join (Select tblassets.AssetID,
      tblassets.AssetName
    From tblassets) Query4 On Query4.AssetID = Query3.ChildAssetID
Where tsysassettypes.AssetTypename = 'Smartphone'

[RCorbeil]

It won't provide the bubble view on hover, but you can create your own hyperlink for the SIM card. Replace

  Query4.AssetName As [SIM Card]

with

  'http://your_lansweeper_server:81/asset.aspx?AssetID=' + Convert(VarChar(8),Query4.AssetID) As [hyperlink_SIM Card],
  Query4.AssetName As [hyperlink_name_SIM Card]

Substitute your LANSweeper server name or IP address for "your_lansweeper_server", of course.

Source: Adding color and hyperlinks to reports

[kschmeling]

RC62N wrote:
It won't provide the bubble view on hover, but you can create your own hyperlink for the SIM card. Replace

  Query4.AssetName As [SIM Card]

with

  'http://your_lansweeper_server:81/asset.aspx?AssetID=' + Convert(VarChar(8),Query4.AssetID) As [hyperlink_SIM Card],
  Query4.AssetName As [hyperlink_name_SIM Card]

Substitute your LANSweeper server name or IP address for "your_lansweeper_server", of course.

Source: Adding color and hyperlinks to reports

PERFECT!!! Works great!