/build/static/layout/Breadcrumb_cap_w.png

K1000 Reporting By Location

Hello, 

I was wondering if anyone could help me with a report based on location. We had one based on the department but in one of the last revisions they changed it to an asset number. I would like to be able to pull "Submitted Tickets Monthly by Location". Any help would be much appreciated. 

4 Comments   [ + ] Show comments
  • How are you defining Location for your tickets and where in your tickets are the associated location values being stored? - Hobbsy 7 years ago
  • Thank you for the quick response. The location is being defined by the asset name. - agahlbeck 7 years ago
  • In what way, is the Asset name prefixed in someway? In which case you just need to write a report that has an "if AssetName begins with" type query - Hobbsy 7 years ago
  • This is what I have currently:

    SELECT HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.ID, HD_TICKET.TITLE, date_format(HD_TICKET.CREATED, '%M') as MONTH, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED, HD_PRIORITY.NAME AS PRIORITY, HD_CATEGORY.NAME AS CATEGORY, HD_STATUS.NAME AS STATUS, HD_IMPACT.NAME AS IMPACT, S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) ORDER BY CUSTOM_FIELD_VALUE3

    Basically the report used to pull the "Department", since they have removed that and is considered "location" now I can't seem to pull the name just the location ID. - agahlbeck 7 years ago
    • This is my rule:
      SELECT HD_TICKET.ID AS ID FROM HD_TICKET
      JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
      WHERE HD_TICKET.SUBMITTER_ID <>0

      Update SQL:
      UPDATE HD_TICKET T
      JOIN USER S ON (S.ID = T.SUBMITTER_ID)
      SET T.CUSTOM_FIELD_VALUE3 = LOCATION_ID WHERE T.ID=<TICKET_IDS> - agahlbeck 7 years ago

Answers (0)

Be the first to answer this question

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