/build/static/layout/Breadcrumb_cap_w.png
07/27/2017 794 views
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

Comments

  • How are you defining Location for your tickets and where in your tickets are the associated location values being stored?
  • Thank you for the quick response. The location is being defined by the asset name.
  • 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
  • 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.
    • 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>

There are no answers at this time