I have a helpdesk setup for cutomer service tickets for all of our locations across the country.  each location has a manager that we assign as the owner of the tickets for their location.  All the locations use a 3 character airport code (example - SAN for San Diego)  We have 4 regional managers that are over certain groups of locations.  I have reports that are scheduled to run weekly and monthly off this helpdesk.  Each Regional Manager gets a report of all tickets broken down by location for his or her area.  My issue is that the reports are starting to grab locations from other areas into the wrong report.   The problem is that for each managers report i used the 3 character airport code in the report creator to specify which locations go into each report.  Example of my issue:  I have one report for the west coast.  One of the locations it should pull is SAN - San Diego.  That same report is pulling in our Miami location because the manager in miami has SAN in his last name (Santiago).  Is there a way in the report creation tool to add spaces before and after the SAN so it wont include everything that has the letters SAN in them?  I just want it to pull the one with the matching 3 digit code.  

3 Comments   [ + ] Show Comments

Comments

  • Here is my report in SQL form. I'm not very literate in SQL so i mostly use the gui to create reports.

    SELECT HD_TICKET.CUSTOM_FIELD_VALUE4,O.FULL_NAME AS OWNER_NAME,HD_TICKET.CUSTOM_FIELD_VALUE7,HD_TICKET.ID,HD_TICKET.CREATED,HD_TICKET.TIME_OPENED,HD_TICKET.TIME_CLOSED FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 4) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) AND ((O.FULL_NAME like '%SEA%') OR (O.FULL_NAME like '%PDX%') OR (O.FULL_NAME like '%RNO%') OR (O.FULL_NAME like '%LAS%') OR (O.FULL_NAME like '%OGG%') OR (O.FULL_NAME like '%HNL%') OR (O.FULL_NAME like '%SJC%') OR (O.FULL_NAME like '%SFO%') OR (O.FULL_NAME like '%SAN%') OR (O.FULL_NAME like '%SNA%') OR (O.FULL_NAME like '%ONT%') OR (O.FULL_NAME like '%OAK%') OR (O.FULL_NAME like '%LAX%') OR (O.FULL_NAME like '%BUR%')) ORDER BY CUSTOM_FIELD_VALUE4,OWNER_NAME,CUSTOM_FIELD_VALUE7

    you can see i use the Owner Full Name to pull in which locations should be in the report.
    The Display Name (OWNER FULL NAME) for the managers is like this: MIA - Eddie Santiago. He is the manager for Miami, but since his name last name has SAN in it then his tickets get pulled into the report for SAN - San Diego. all of our managers names are done that way so we know which person is where and who to assign tickets to. if i could get kace to see the space before and after the 3 digit airport code then it shouldn't include ones that have those letters in people's actual name.
    • If the 3 digit codes start the field, you can drop the initial %. If there are leading spaces, you will need to include those. So, if there is one space prior to the 3 digit code:
      (O.FULL_NAME like '%SAN%')
      becomes
      (O.FULL_NAME like ' SAN%')
    • If you have this built with the GUI, change the operator from "contains" to "begins with" and include any leading spaces in the value.
  • Could you post the SQL from one of the problem queries?
  • I was trying what you said about dropping the % in front of the 3 digit code. That would work except some of our users are attached to more than one site so their name might start with 2 location codes ex: "SAN and SMF - Bob Smith" So i kept playing around with those % signs and found if i do like this - (O.FULL_NAME like '%SAN %') and put that space at the end of the 3 digit code then it works like i need it since all the codes have a space after them. Thanks for your time and the push in the right direction.
    • That will work until you hire Carmen San Diego. ;)
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity