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]

Hello E,

Let's take a multi-step approach here. I see why you are having the errors. But let's forget about that for now and do this instead:

First, we find the custom field id for your custom field "location". Then we add it to your query and then sort/count or do any calculation with it.

Run this query:

Select Top 1000000 htblticketcustomfield.ticketid,
htblcustomfields.name,
htblticketcustomfield.data,
htblticketcustomfield.fieldid
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
where name like 'location%'

You should see the location values in the data column. Let me know the value of the fieldid. I'll follow up with the second query.

[ebruntjen]

Not sure why but it is still failing here. We are using SQL Compact.

My goal is really just a count of the tickets that have a specific Location selected in the Location ComboBox over the last 30 days. I wonder if there is another, more simple Query we could run that just does that... just totals the tickets for Each Location for the past 30 days.

Really, even a simple output that lists the Location for all tickets would be fine, I could export to Excel and get my calculations done there.

Thank you again, I appreciate the help!

E

[endyk]

I tried your query and it works on my end. Try replacing "Location" in the order by with 7 and let me know if it works.
Reason being your version of SQL Server may not support using the calculated Location field name in the order by section.

i.e.
Order By Year Desc,
Month Desc,
7,
Type

[ebruntjen]

Below is the code as I edited it. I am getting an error that I don't understand: Type There was an error parsing the query. [ Token line number = 1,Token line offset = 371,Token in error = Over ]

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%],
Case Row_Number() Over (Order By htbltickettypes.typename)
When 1 Then 'Wapato'
When 2 Then 'Wenatchee'
When 3 Then 'Prosser - All'
Else 'Pendleton'
End 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)
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,
Location,
Type

[ebruntjen]

Thank you, I will try that today.

[endyk]

Hello,

To sort by the custom location, you'll have to add the custom location field to the sql code then update the order by clause to with the field's position number or field name (depending on your version of SQL Server).

In the example below, I made up a custom location field called "Location" which is the 7th field in the select statement. I updated the order by clause to order by this field position before ordering by type. You can run the below query and let me know if you have any question.
Remember, you'll have to change my custom location field with your own custom location logic.

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%],
case ROW_NUMBER() over (order by typename) when 1 then 'Braavos' when 2 then 'Mereen' when 3 then 'Qarth'
when 4 then 'Mereen' else 'King''s Landing' end 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)
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,
7, Type