→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ArjanvA
Engaged Sweeper
Hello all,

We are trying to get a new report to work to show our helpdesk tickets.
Information we're trying to get out is as follows.
- Ticket ID
- Subject
- Date of creation
- Date of closure
- Calculation of the time passed between creation and closure.
- Category of ticket (this is a custom field)
- Subcategory of ticket (this is a custom field)

For some reason however I cannot seem to get the custom fields to work in my report.
Does anyone have any examples where custom helpdesk fields are used in reports?

Kind regards,

Arjan
1 REPLY 1
harringg
Champion Sweeper
See this link for an example I just posted.

https://www.lansweeper.com/forum/yaf_postst16374_Helpdesk-report-no-results.aspx#post55084

The key takeaways are (in reverse order):
The code in the From portion of the query looks like this for each "element" of the ticket.
  Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
On htblticket.ticketid = ToteIDName.ticketid


This value needs to match the Ticket Field
Where htblcustomfields.name Like 'PCD-HDD ToteID Name'


This has to be unique value
As ToteIDName


This has to match the value listed in the As field
ToteIDName.ticketid


The data from the Select portion of the SQL query should look like this:
ToteIDName.data As ToteIDName