K1000 - How to add owner or submitter location field to a queue ticket report?
I know this can be done by using 'sub topics' in the wizard, but those reports fo not generate flat tables making working with them difficult. I had a bunch of custom reports that pulled the User.Location field into owner or submitter ticket reports, but since the v7 upgrade all of those are broken and repairing them by joining to the asset table (where ASSET_TYPE_ID = 1) is proving quite a challenge for my weak sql foo and or sleep deprivation. Can anyone identify what I'm doing wrong? My additions to the wizard generated sql highlighted in yellow. Thanks!
SELECT
HD_TICKET.ID,
HD_TICKET.TITLE,
S.FULL_NAME AS SUBMITTER_NAME,
UL.NAME AS SUBMITTER_LOCATION,
O.FULL_NAME AS OWNER_NAME,
OL.NAME AS OWNER_LOCATION,
IF(HD_TICKET.HD_USE_PROCESS_STATUS
AND HD_TICKET.IS_PARENT,
HD_SERVICE_STATUS.NAME,
HD_STATUS.NAME) AS STATUS_NAME,
HD_CATEGORY.NAME AS CATEGORY,
MACHINE.NAME AS SYSTEM_NAME,
HD_TICKET.CREATED,
HD_TICKET.MODIFIED,
HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_STALLED
FROM
HD_TICKET
LEFT JOIN
USER S ON S.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN
USER O ON O.ID = HD_TICKET.OWNER_ID
LEFT JOIN
ASSET UL ON UL.ID = HD_TICKET.SUBMITTER_ID AND UL.ASSET_TYPE_ID = 1
LEFT JOIN
ASSET OL ON OL.ID = HD_TICKET.OWNER_ID AND UL.ASSET_TYPE_ID = 1
LEFT JOIN
HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS
AND HD_TICKET.HD_SERVICE_STATUS_ID
AND HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
JOIN
HD_STATUS ON HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
JOIN
HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
LEFT JOIN
MACHINE ON MACHINE.ID = HD_TICKET.MACHINE_ID
ORDER BY ID DESC
1 Comment
[ + ] Show comment
-
Thanks Chucksteel! - kpm8 6 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
You are linking to the asset table by looking for the submitter's user ID, when you should be looking for their location ID.
Your join statements should look like this:
LEFT JOIN ASSET OL on OL.ID = O.LOCATION_ID and OL.ASSET_TYPE_ID = 1
LEFT JOIN ASSET SL on SL.ID = S.LOCATION_ID and OL.ASSET_TYPE_ID = 1
You were using UL for the submitter location, I used SL for more clarity.
Comments:
-
Thanks Chucksteel! - kpm8 6 years ago
