cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Darth_Jed
Engaged Sweeper
I probably have overlooked this somewhere, but what SQL code do you need to create a custom report that lists printer names that are clickable links to their detail pages? For example, I'm trying to create a report that will list the printer names in the first column, followed by all the other fields. If I look at Device: Printer Status, that report has the effect I'm looking for. When I build my report in a similar fashion, the displayname is not converted into a clickable link. Here's my code.

Select Top 1000000 tblCustDevices.Displayname, tblCustDevices.Ipaddress, tblCustDevices.Model, tblstate.Statename, tblCustDevices.LastSeen, tblCustDevices.DeviceKey, tblCustDevices.DeviceKey As DeviceKey1 From tblCustDevices Inner Join tblstate On tblCustDevices.State = tblstate.State Inner Join tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype Inner Join web40AllIPLocationsDev On tblCustDevices.DeviceKey = web40AllIPLocationsDev.DeviceKey Where tblstate.Statename = 'active' And tblCustDevices.DeviceKey Like '%.%' And tblCustDevices.Devicetype = 16 Order By tblCustDevices.Displayname
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please try the report below. To link the name to the device page the fields "Displayname" and "DeviceKey" must be included in your report and "Displayname" must be given the alias "Device Name".

Select Top 1000000 tblCustDevices.Displayname As [Device Name], tblCustDevices.Ipaddress, tblCustDevices.Model, tblstate.Statename, tblCustDevices.LastSeen, tblCustDevices.DeviceKey From tblCustDevices Inner Join tblstate On tblCustDevices.State = tblstate.State Inner Join tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype Inner Join web40AllIPLocationsDev On tblCustDevices.DeviceKey = web40AllIPLocationsDev.DeviceKey Where tblstate.Statename = 'active' And tblCustDevices.DeviceKey Like '%.%' And tblCustDevices.Devicetype = 16 Order By tblCustDevices.Displayname

View solution in original post

5 REPLIES 5
Waterscorpion
Engaged Sweeper
Awesome, that did it! Thanks
Hemoco
Lansweeper Alumni
Please try the report below instead. You must include the tblComputers.Computername field in your report.

Select Top 1000000 tblComputers.Computername, tblComputers.Computer, tblComputerSystemProduct.IdentifyingNumber, tblCompCustom.Custom1 As AssignedTo, tblComputerSystemProduct1.Name, tblComputerSystemProduct1.Vendor, tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate, WarrantyStatus = Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End, web40AllIPLocations.IPLocation From tblComputers Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Inner Join tblComputerSystemProduct tblComputerSystemProduct1 On tblComputers.Computername = tblComputerSystemProduct1.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Where Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End Is Not Null
Waterscorpion
Engaged Sweeper
I know this is an old post at this point, but what would be the appropriate input to make the computer name linkable in the following report?

Select Top 1000000 tblComputers.Computer, tblComputerSystemProduct.IdentifyingNumber, tblCompCustom.Custom1 As AssignedTo, tblComputerSystemProduct1.Name, tblComputerSystemProduct1.Vendor, tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate, WarrantyStatus = Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End, web40AllIPLocations.IPLocation From tblComputers Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Inner Join tblComputerSystemProduct tblComputerSystemProduct1 On tblComputers.Computername = tblComputerSystemProduct1.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Where Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End Is Not Null
Darth_Jed
Engaged Sweeper
That's exactly what I was missing! Thank you!
Hemoco
Lansweeper Alumni
Please try the report below. To link the name to the device page the fields "Displayname" and "DeviceKey" must be included in your report and "Displayname" must be given the alias "Device Name".

Select Top 1000000 tblCustDevices.Displayname As [Device Name], tblCustDevices.Ipaddress, tblCustDevices.Model, tblstate.Statename, tblCustDevices.LastSeen, tblCustDevices.DeviceKey From tblCustDevices Inner Join tblstate On tblCustDevices.State = tblstate.State Inner Join tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype Inner Join web40AllIPLocationsDev On tblCustDevices.DeviceKey = web40AllIPLocationsDev.DeviceKey Where tblstate.Statename = 'active' And tblCustDevices.DeviceKey Like '%.%' And tblCustDevices.Devicetype = 16 Order By tblCustDevices.Displayname