Notification

Icon
Error

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

Posted: Sunday, May 12, 2019 10:29:22 PM(UTC)
ebruntjen

ebruntjen

Member Original PosterPosts: 9
0
Like
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
#1endyk Member Posts: 24  
posted: 5/14/2019 12:40:53 PM(UTC)
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
ebruntjen
#2ebruntjen Member Original PosterPosts: 9  
posted: 5/14/2019 3:44:04 PM(UTC)
Thank you, I will try that today.
ebruntjen
#3ebruntjen Member Original PosterPosts: 9  
posted: 5/14/2019 4:16:02 PM(UTC)
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
endyk
#4endyk Member Posts: 24  
posted: 5/14/2019 4:36:53 PM(UTC)
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
#5ebruntjen Member Original PosterPosts: 9  
posted: 5/16/2019 5:31:32 PM(UTC)
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
#6endyk Member Posts: 24  
posted: 5/16/2019 10:13:47 PM(UTC)
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
#7ebruntjen Member Original PosterPosts: 9  
posted: 5/16/2019 10:44:53 PM(UTC)
Sounds like a good plan, thanks.

The FieldID is 28
endyk
#8endyk Member Posts: 24  
posted: 5/16/2019 11:02:37 PM(UTC)
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
#9ebruntjen Member Original PosterPosts: 9  
posted: 5/16/2019 11:11:23 PM(UTC)
still no luck. The error is ... Order By Year Desc, Month Desc, 7, Type ORDER BY <column number> not supported.
endyk
#10endyk Member Posts: 24  
posted: 5/16/2019 11:19:21 PM(UTC)
Originally Posted by: ebruntjen Go to Quoted Post
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]
ebruntjen
#11ebruntjen Member Original PosterPosts: 9  
posted: 5/16/2019 11:25:35 PM(UTC)
Still no luck, error is now: 28, Type ORDER BY <column number> not supported.

Thank you still!

Eric
ebruntjen
#12ebruntjen Member Original PosterPosts: 9  
posted: 5/16/2019 11:29:28 PM(UTC)
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
#13endyk Member Posts: 24  
posted: 5/16/2019 11:32:37 PM(UTC)
You are welcome!
ebruntjen
#14ebruntjen Member Original PosterPosts: 9  
posted: 5/17/2019 12:09:40 AM(UTC)
Yes! It is working perfectly now. Thank you.
endyk
#15endyk Member Posts: 24  
posted: 5/17/2019 1:22:19 AM(UTC)
Glad it worked. Feel free to reach out if you have further issues/questions.

Active Discussions

Lansweeper TPM scanning
by  gene@sca   Go to last post Go to first unread
Last post: Yesterday at 10:40:24 PM(UTC)
Lansweeper Bulk AssetLock set
by  tcooper  
Go to last post Go to first unread
Last post: Yesterday at 6:24:15 PM(UTC)
Lansweeper Set AssetType based on Model?
by  tcooper   Go to last post Go to first unread
Last post: Yesterday at 6:16:57 PM(UTC)
Lansweeper Public IP Address
by  David Goodwin  
Go to last post Go to first unread
Last post: Yesterday at 4:57:14 PM(UTC)
Lansweeper Exchange Scan in v7 and DAG
by  Peter.Riederer   Go to last post Go to first unread
Last post: Yesterday at 8:42:48 AM(UTC)
Lansweeper Asset Scanning - Change Types
by  DNuhija  
Go to last post Go to first unread
Last post: 12/12/2019 5:40:47 PM(UTC)
Lansweeper Plundervolt
by  NWSF   Go to last post Go to first unread
Last post: 12/12/2019 5:10:14 PM(UTC)
Lansweeper MSI Package
by  Stefano Magri  
Go to last post Go to first unread
Last post: 12/12/2019 3:34:16 PM(UTC)