ITNinja

If you don't see the field your looking for, it would have to be a custom SQL report.

Post your SQL we'll help if you get stuck.

you will have to forgive me, I am a total n00b on sql. Do I need to go in to the edit sql and copy and paste the sql select statements or the xml format? or something totally different?
thank you

No problem noname_com, everyone is a n00b at some point.

Your going to want to enter your SQL statement and click auto-generate layout to take care of the XML.

I would take a look at Jody's presentation on SQL from this years conference to get you going.

Airwolf also has a great intro document on his K1000 korner.

For a crash course in SQL i would check out w3schools.

Hope this helped, and good luck.

I know this is an old thread and that there is a planned update to add resolution to the Wizard in 5.4, but has anyone come up with the SQL for this? I made an attempt, but failed miserably.

The best way here is to post your SQL so someone can review it.

Ok. Here are a couple SQL statements that i came up with:

SELECT
HD_TICKET.ID,
HD_TICKET.CREATED,
S.FULL_NAME AS SUBMITTER_NAME,
HD_STATUS.NAME AS STATUS,
O.FULL_NAME AS OWNER_NAME,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.TITLE, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
HD_IMPACT.NAME AS IMPACT
FROM
HD_TICKET LEFT JOIN
USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 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) LEFT JOIN
HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) JOIN
HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.CREATED > '2012-02-08 00:00:00') AND (HD_TICKET.CREATED < '2012-02-13 23:59:59'))
GROUP BY
HD_TICKET.ID ORDER BY ID

Select
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_PRIORITY.NAME,
HD_TICKET.CREATED,
HD_TICKET.TIME_CLOSED,
HD_CATEGORY.NAME As NAME2,
HD_STATUS.NAME As NAME1,
HD_TICKET.RESOLUTION,
USER.USER_NAME
From
HD_TICKET HD_TICKET Inner Join
HD_PRIORITY On HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID Inner Join
USER On USER.ID = HD_TICKET.OWNER_ID Inner Join
HD_STATUS On HD_STATUS.ID = HD_TICKET.HD_STATUS_ID Inner Join
HD_CATEGORY On HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
Where
HD_TICKET.CREATED Between '2012/02/08 00:00:00' And '2012/02/13 23:59:59'
Group By
HD_TICKET.ID
Order By
HD_TICKET.ID

The problem with the first one is that, if I try to add HD_TICKET.RESOLUTION, I get no results even though I get the correct results without it.

The problem with the second one is that somehow I lost how I added two users and now I only get the Owner User, and also, if I add Comment, I get no results again.

If anyone can help, it would be much appreciated. Thanks in advance.