Report listing Ticket field values

[harringg]

If I have a basic ticket that has a Username field, a date field and two custom fields (Department, Test Score), is there a report that will list those fields?

Report:
Ticket ID, Ticket Subject, Username, Date, Department, Test Score

[harringg]

Did some more testing, and confirmed, this is the solution to customize my Helpdesk reports.

Again, thank you!

[harringg]

Thank you. That's got me started. Now, to add more fields, would I just duplicate this block for each value I want included (ie. Testing - Date, Testing - Graded, etc..)?

TestDate.data As TestDate

Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'Testing - Date') As TestDate
    On htblticket.ticketid = TestDate.ticketid

[Nick_VDB · Accepted solution]

We added a report below that will give back tickets that have both the Department and testscore custom fields

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Distinct Top 1000000 htblticket.date,
  htblticket.slaresolved,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblticket.subject,
  htblusers1.name As [User Name],
  htblusers.name As [Help Desk Agent Name],
  Department.data As Dapartment,
  TestScore.data As TestScore
From htblticket
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers On htblusers.userid = htblagents.userid
  Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
  Left Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htbltickettypes.tickettypeid =
    htblticket.tickettypeid
  Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'Department') As Department
    On htblticket.ticketid = Department.ticketid
  Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'Test Score') As TestScore
    On htblticket.ticketid = TestScore.ticketid