/build/static/layout/Breadcrumb_cap_w.png

Weekly Helpdesk Report (custom fields, multiple conditions, adjust column widths in classic reports)

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

0 Comments   [ + ] Show comments

Answers (0)

Be the first to answer this question

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ