Link to doc added to report

[MustQ]

Hello,

Situation: we keep PDF's of our invoices when we purchase hardware, and intend to upload them to lansweeper's 'doc' page for each asset.

We'd like to add a column to the below report that will provide a link the uploaded document associated with that asset. This way when we export to excel, you can click on the field and be brought to the Invoice/Purchase order in question. If there are multiple documents, either comma separate in the same column or create a column for each document?

Select Top 1000000 tblAssets.Assettype,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Memory,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.State,
tblAssetCustom.OrderNumber,
tblAssetCustom.Comments,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.Custom1 As [Invoice Date],
tblAssetCustom.Custom2 As Vendor,
tblAssetCustom.Custom3 As [P/O #],
tblAssetCustom.Custom4 As [Invoice #],
tblAssetCustom.Custom5 As [Payment Type],
tblAssetCustom.Custom6 As [Invoice Total],
tblAssetCustom.Custom7 As [Price Per Unit],
tblAssetCustom.Custom8 As [Original Assigned User]
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
Where tblAssetCustom.State = 1 And tblDiskdrives.Caption = 'c:'
Order By tblAssets.AssetID

[Susan_A]

FYI for everyone: you can now add clickable links to Lansweeper reports. See this forum topic.

[Hemoco · Accepted solution]

MustQ wrote:
If there are multiple documents, either comma separate in the same column or create a column for each document?

SQL doesn't allow for this. Table records are displayed as lines, not columns. You can try the report below to list the doc links as lines. Replace YourLansweeperServerName and YourPort. (Note that these links will not be clickable within the report results.)

Select Top 1000000 tblAssets.Assettype,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Memory,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.State,
tblAssetCustom.OrderNumber,
tblAssetCustom.Comments,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.Custom1 As [Invoice Date],
tblAssetCustom.Custom2 As Vendor,
tblAssetCustom.Custom3 As [P/O #],
tblAssetCustom.Custom4 As [Invoice #],
tblAssetCustom.Custom5 As [Payment Type],
tblAssetCustom.Custom6 As [Invoice Total],
tblAssetCustom.Custom7 As [Price Per Unit],
tblAssetCustom.Custom8 As [Original Assigned User],
tblAssetDocs.Docname,
tblAssetDocs.Docguid,
'http://YourLansweeperServerName:YourPort/AssetActions.aspx?action=getDoc&assetId=' +
Cast(tblAssets.AssetID As nvarchar) + '&docId=' + tblAssetDocs.Docguid As
DocLink,
tblAssetDocs.AddedBy,
tblAssetDocs.Added
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
Inner Join tblAssetDocs On tblAssets.AssetID = tblAssetDocs.AssetID
Where tblAssetCustom.State = 1 And tblDiskdrives.Caption = 'c:'
Order By tblAssets.AssetID,
tblAssetDocs.Docname