Tested in: 14504, 14610
QUERY:
SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS "Status", LONGTODATE(chdt.CREATEDTIME) AS "Created Time", orgaaa.FIRST_NAME AS "Change Requester", tempDef.NAME AS "Change Template",appr.id AS "Approval_level#id",appr.name AS "Approval_level#name",au.FIRST_NAME AS "Approver" FROM ChangeDetails chdt LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.User_ID LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN ApprovalStatusDefinition approvaldef ON chdt.APPR_STATUSID=approvaldef.STATUSID LEFT JOIN QueueDefinition qd ON chdt.GROUPID=qd.QUEUEID LEFT JOIN Change_StatusDefinition statusDef ON chdt.WFSTATUSID=statusDef.WFSTATUSID LEFT JOIN ChangeTemplate tempDef ON chdt.TEMPLATEID=tempDef.TEMPLATEID LEFT JOIN AaaUser cmDef ON chdt.CHANGEMANAGERID=cmDef.USER_ID left join change_approvallevel chl on chdt.changeid = chl.entityid left join approvallevel appr on appr.id = chl.levelid left join ApprovalDetails ad on ad.approval_level_id = appr.id left join sduser sd on ad.approverid = sd.userid left join aaauser au on sd.userid = au.user_id WHERE (( chdt.DELETEDTIME IS NULL ) )
The above query returns the following columns,
In which the Approval Level ID, Approval Level Name, and the Approver Name cannot be retrieved using a custom report.