Detail report of all tickets

jprinz

I have to say, I have encountered the most frustrating issue this week. We have a need to generate a scheduled report to send all open tickets with very simple detail on a bi-daily basis.

Webhelpdesk does not have this ability.  In case you missed it, webhelpdesk cannot generate a detailed report without manual creation of said report and manual distribution of said report.

Does anyone have a sql script that might help me out?

Im nearly done with this product.

Anyone migrate to servicenow? Difficult?

kwashington

JOB_TICKET.JOB_TICKET_ID,

.SUBJECT,

.QUESTION_TEXT,

rtrim(CLIENT.FIRST_NAME) + ' ' + rtrim(CLIENT.LAST_NAME) AS CLIENT,

.PHONE,

rtrim(TECH.FIRST_NAME) + ' ' + rtrim(TECH.LAST_NAME) AS ASSIGNED_TO,

.PRIORITY_TYPE_NAME,

.PROBLEM_TYPE_NAME,

.STATUS_TYPE_NAME,

CONVERT(VARCHAR(10),JOB_TICKET.REPORT_DATE,101) AS DATE,

.STRING_VALUE

WEBHELPDESK.dbo.JOB_TICKET LEFT OUTER JOIN

ON TECH.CLIENT_ID = JOB_TICKET.ASSIGNED_TECH_ID LEFT OUTER JOIN

ON CLIENT.CLIENT_ID = JOB_TICKET.CLIENT_ID LEFT OUTER JOIN

ON PROBLEM_TYPE.PROBLEM_TYPE_ID = JOB_TICKET.PROBLEM_TYPE_ID LEFT OUTER JOIN

ON PRIORITY_TYPE.PRIORITY_TYPE_ID = JOB_TICKET.PRIORITY_TYPE_ID LEFT OUTER JOIN

ON STATUS_TYPE.STATUS_TYPE_ID = JOB_TICKET.STATUS_TYPE_ID LEFT OUTER JOIN

ON JOB_TICKET.JOB_TICKET_ID = TICKET_CUSTOM_FIELD.ENTITY_ID

JOB_TICKET.DELETED <> '1'

AND STATUS_TYPE.STATUS_TYPE_NAME IN ('Open', 'Pending')