KACE Product Support

This is the report side of my post on pulling from custom user fields listed here:

http://itninja.com/question/faulttree-105894

Report lists all tickets closed in the past 7 days and all non-closed tickets for users in the Sales department.

SELECT HD_TICKET.ID AS 'ID', HD_CATEGORY.NAME AS 'Category', HD_TICKET.TITLE AS 'Issue', S.FULL_NAME AS 'Submitter', O.FULL_NAME AS 'Owner', HD_STATUS.NAME AS 'Status', HD_TICKET.CREATED AS 'Created' FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT
JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1 AND HD_TICKET.CUSTOM_FIELD_VALUE3 = 'sales' AND HD_STATUS.NAME != 'closed') OR (HD_TICKET.HD_QUEUE_ID = 1 AND HD_TICKET.CUSTOM_FIELD_VALUE3 = 'sales' AND HD_STATUS.NAME = 'closed' AND DATEDIFF(NOW(), HD_TICKET.TIME_CLOSED) < 7)
ORDER BY HD_TICKET.ID asc

Break on Columns:
<blank>
____________________________________________________

The WHERE statement combines these two statements:

1) List all non-closed calls for users in the sales department
WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CUSTOM_FIELD_VALUE3 = 'sales' AND HD_STATUS.NAME != 'closed')

2) List all closed calls for users in the sales department from the previous 7 days (past week)
WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CUSTOM_FIELD_VALUE3 = 'sales' AND HD_STATUS.NAME = 'closed' AND DATEDIFF(NOW(), HD_TICKET.TIME_CLOSED) < 7)
____________________________________________________

I created this in the classic reports section so I could schedule a PDF to be sent out to recipients every Monday morning.

Also found a couple things which others probably already know about (but I figured out one day when I had time):

1) To adjust the date format listed in columns like Created, Closed, etc - search for SimpleDateFormat in the XML code and adjust as necessary. For this report I used "MM/dd/yyyy HH:mm" since seconds aren't necessary and just waste column space. On my daily non-closed tickets report, I just use "HH:mm".

2) To adjust the column widths, search the XML for "ColumnHeaderFooter" and "Detail" and adjust these numbers to get a better looking report.

X values Column Widths

0 20
20 40
60 120
180 262
442 100
542 80
622 80
702 80

If anyone needs clarification on any of this, just let me know. Just trying to help someone out there save some time.

John