/build/static/layout/Breadcrumb_cap_w.png

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 7 years ago

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
1
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 7 years ago

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