Report: Count of tickets per *Custom Field* and month

[ebruntjen]

Hi All,

We have a custom field (drop down) in our Tickets for "Location" used to select geographic locations that a ticket relates to (only 4 possible values in the drop down).

I would like to modify the standard Lansweeper Report "Helpdesk: Count of tickets per type and month" so that it sorts the counts by the Location Custom Field instead of "type". Any assistance would be much appreciated. Below is the SQl for the standard report. Thanks in advance.

Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htbltickettypes.typename As Type,
Count(htblticket.ticketid) As TicketCount,
Cast((Count(htblticket.ticketid) / Cast(TicketCount.Amount As decimal) *
100) As decimal(10,2)) As [Type%]
From htblticket
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Count(htblticket.ticketid) As Amount
From htblticket
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)) As TicketCount On TicketCount.Year =
DatePart(yyyy, htblticket.date) And TicketCount.Month = DatePart(mm,
htblticket.date)
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date),
htbltickettypes.typename,
htbltickettypes.icon,
TicketCount.Amount
Order By Year Desc,
Month Desc,
Type

[endyk]

Glad it worked. Feel free to reach out if you have further issues/questions.

[ebruntjen]

Yes! It is working perfectly now. Thank you.

[ebruntjen]

OK I see now. I think it is working. I used customfield.[data] instead of 28. I got results! Let me check them. I will update soon. Thanks!

[endyk]

You are welcome!

[ebruntjen]

Still no luck, error is now: 28, Type ORDER BY <column number> not supported.

Thank you still!

Eric

[ebruntjen]

still no luck. The error is ... Order By Year Desc, Month Desc, 7, Type ORDER BY <column number> not supported.

[endyk]

ebruntjen wrote:
still no luck. The error is ... Order By Year Desc, Month Desc, 7, Type ORDER BY <column number> not supported.

Replace the 7 with customfield.[data]

[endyk]

Great. Run this and let me know if how it looks:

Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htbltickettypes.typename As Type,
Count(htblticket.ticketid) As TicketCount,
Cast((Count(htblticket.ticketid) / Cast(TicketCount.Amount As decimal) *
100) As decimal(10,2)) As [Type%],
customfield.[data] As Location
From htblticket
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Count(htblticket.ticketid) As Amount
From htblticket
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)) As TicketCount On TicketCount.Year =
DatePart(yyyy, htblticket.date) And TicketCount.Month = DatePart(mm,
htblticket.date)
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblcustomfields.name,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblticketcustomfield.fieldid = 28) As customfield
On htblticket.ticketid = customfield.ticketid
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date),
htbltickettypes.typename,
htbltickettypes.icon,
TicketCount.Amount,
customfield.[data]
Order By Year Desc,
Month Desc,
7,
Type

[ebruntjen]

Sounds like a good plan, thanks.

The FieldID is 28