cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ebruntjen
Engaged Sweeper
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
15 REPLIES 15
endyk
Engaged Sweeper III
Glad it worked. Feel free to reach out if you have further issues/questions.
ebruntjen
Engaged Sweeper
Yes! It is working perfectly now. Thank you.
ebruntjen
Engaged Sweeper
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
Engaged Sweeper III
You are welcome!
ebruntjen
Engaged Sweeper
Still no luck, error is now: 28, Type ORDER BY <column number> not supported.

Thank you still!

Eric
ebruntjen
Engaged Sweeper
still no luck. The error is ... Order By Year Desc, Month Desc, 7, Type ORDER BY <column number> not supported.
endyk
Engaged Sweeper III
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
Engaged Sweeper III
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
Engaged Sweeper
Sounds like a good plan, thanks.

The FieldID is 28