Adding color and hyperlinks to reports

[Susan_A]

From Lansweeper 5.2 onwards, you can specify a foreground color (text color) and background color for your report results, as well as add hyperlinks to your reports. Below are some report examples that illustrate how you can use these new features. You can reference colors using color names or hexadecimal color codes.

Change the foreground color of all report results to blue:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  'blue' As foregroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

Change the background color of all report results to yellow:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  'yellow' As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

Use a case to change the foreground color and background color of Windows computer entries with less than 2GB of memory:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  Case When tblAssets.Memory < 2048 Then 'red' End As foregroundcolor,
  Case When tblAssets.Memory < 2048 Then '#fbfb86' End As backgroundcolor,
  tblAssets.Memory
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

Google your assets' model names from within your report results:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  'http://www.google.com/search?q=' + tblAssetCustom.Model As hyperlink_hyp,
  'Google: ' + tblAssetCustom.Model As hyperlink_name_hyp
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

[emarroyo]

Hey Susan,

Is it possible to do a date range? I have a few switches out of warranty and want that date field to stand out in a different color if possible.

[Susan_A]

emarroyo wrote:
Hey Susan,

Is it possible to do a date range? I have a few switches out of warranty and want that date field to stand out in a different color if possible.

I've included a sample report below that highlights assets whose warranty has expired. Keep in mind though that you can only mark entire lines, not specific columns.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Warrantydate,
  Case When tblAssetCustom.Warrantydate < GetDate() Then 'red'
  End As foregroundcolor,
  Case When tblAssetCustom.Warrantydate < GetDate() Then '#fbfb86'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1