ITNinja

Are you looking for Custom 6 from the Database or the GUI?

Custom 6 in the GUI is really custom 5 in the DB.

Try this:
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF(Time_to_sec(HD_TICKET.TIME_CLOSED) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(HD_TICKET.TIME_CLOSED) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DEPARTMENT
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
ORDER BY SUBMITTER_NAME

select HD_TICKET.CUSTOM_FIELD_VALUE6 as DEPARTMENT,

Without any testing of any kind, have you tried changing the fist line to something like this:
select COUNT(HD_TICKET.CUSTOM_FIELD_VALUE6) as DEPARTMENT_COUNT,

Tried that, but then I only got one ticket, no department listed and that was it.

Okay, the code below gives me what I am looking for by Submitter, I also have it doing the Break on Column on the SUBMITTER_NAME. This give me the count of how many tickets a particular person has put in. Now if I can get the same thing by department, it would make my headache go away.

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by SUBMITTER_NAME

How do I get the CUSTOM_FIELD_VALUE6 = DEPARTMENT to work in here? I thought I had it, but it doesn't put the department names into the report. ANY help would be gratefully accepted.

D

Where exactly are you pulling department from?

I created a list in Custom Field 6 with all the departments that are covered by our help desk. We are a county government help desk, so the departments are anything from administration to sheriff. I did the same thing with different types of categories for problems, like Software::JDE or Hardware:: PC Setup. I haven't had any problem getting that to show in custom reports, but the departments just show up blank. I get the heading, but no departments listed.

Hope that helps.

D

What happens when you run this?

SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF(Time_to_sec(HD_TICKET.TIME_CLOSED) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(HD_TICKET.TIME_CLOSED) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE6 AS DEPARTMENT
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
ORDER BY SUBMITTER_NAME

Still getting nothing under the Department column.[:(]

EUREKA!!![:D]

Now how do I get it to give me a sort and count by department?

I told it to Break on Column CUSTOM_FIELD_VALUE5, but it isn't doing it.

Change the order by submitter to order by the custom field.

The count is a little more tricky because you have to choose some fields to get rid of when you aggregate the totals.

Give the SQL a try, and post your results.

We'll help if you get stuck.

Here is the final result:

SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF(Time_to_sec(HD_TICKET.TIME_CLOSED) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(HD_TICKET.TIME_CLOSED) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DEPARTMENT
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
ORDER BY DEPARTMENT, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Break on Column: DEPARTMENT

Gives me a count on each department! When I had CUSTOM_FIELD_VALUE5 in the Break on Column, it didn't break, but when I changed it to DEPARTMENT, it worked!

Thanks to all of you for all your help. I'm learning more SQL everyday. Hopefully, I'll be able to help some rusty or new users soon.

D