Custom Reports
Hi team,
There is a way a to obtain this reports in ondemand environment?
Ticket link to problem in status closed.
select c.WORKORDERID "Ticket" ,f.STATUSNAME "Status Ticket" , LONGTODATE (d.CREATEDTIME) "FechaAlta Ticket", h.FIRST_NAME "Tecnico Asignado" , a.PROBLEMID "ID Problema Ligado" ,a.TITLE "Titulo Problema",b.STATUSNAME "Status Problema", LONGTODATE (a.CLOSEDTIME) "Fecha de cierre Problema" ,wof.UDF_CHAR22 "Tecnico Reponsable" from Problem a inner join StatusDefinition b on b.STATUSID= a.STATUSID inner Join ProblemToIncidentMapping c on c.PROBLEMID= a.PROBLEMID inner Join WorkOrder d on d.WORKORDERID=c.WORKORDERID inner join WorkOrderStates e on e.WORKORDERID=d.WORKORDERID inner Join StatusDefinition f on f.STATUSID=e.STATUSID inner join SDUser g on g.USERID= e.OWNERID inner join AaaUser h on h.USER_ID=g.USERID LEFT JOIN WorkOrder_Fields wof ON d.WORKORDERID=wof.WORKORDERID where (((((f.STATUSNAME = N'Abierto' COLLATE Latin1_General_CI_AI) OR (f.STATUSNAME = N'En escalamiento' COLLATE Latin1_General_CI_AI)) OR (f.STATUSNAME = N'En espera' COLLATE Latin1_General_CI_AI)) OR (f.STATUSNAME = N'En proceso' COLLATE Latin1_General_CI_AI)) OR (f.STATUSNAME IS NULL)) and ((b.STATUSNAME = N'Cerrado' COLLATE Latin1_General_CI_AI) OR (b.STATUSNAME = N'Completado' COLLATE Latin1_General_CI_AI))
Tickte without approve.
SELECT distinct wo.WORKORDERID "ID de la solicitud" ,TITLE "Titulo Ticket" ,LONGTODATE (wo.CREATEDTIME) "Fecha Creacion Ticket"
,cd.categoryname "Categoria"
,scd.name "SubCategoria"
,id.name "Elemento"
,wo.DESCRIPTION "Texto Ticket"
,aau.FIRST_NAME "Solicitante"
,r.DEPTNAME "Depto Solicitante"
, std.STATUSNAME "Estado de solicitud"
,appStDef.STATUSNAME "Estado de la aprobación"
,LONGTODATE (c.SENT_DATE) "Fecha Solicitud Aprobacion"
,m.FIRST_NAME "Aprobador Asignado"
,EMAIL "Email Aprobador" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
Left Join UserDepartment t on t.USERID=aau.USER_ID
left Join DepartmentDefinition r on r.DEPTID =t.DEPTID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd on cd.categoryid=wos.categoryid
LEFT JOIN SubCategoryDefinition scd on scd.subcategoryid=wos.subcategoryid
LEFT JOIN ItemDefinition id on id.itemid=wos.itemid
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN ApprovalStageMapping a on a.workorderid=wo.workorderid and a.workorderid=wo.workorderid
LEFT JOIN ApprovalDetails b on b.APPROVAL_STAGEID= a.APPROVAL_STAGEID
LEFT JOIN ApprovalStatusDefinition appStDef on appStDef.STATUSID=b.STATUSID
LEFT JOIN ApprovalStage c on c.APPROVAL_STAGEID=b.APPROVAL_STAGEID and c.APPROVAL_STAGEID=a.APPROVAL_STAGEID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT Join AaaContactInfo i on i.EMAILID=b.EMAIL
LEFT join AaaUserContactInfo l on l.CONTACTINFO_ID=i.CONTACTINFO_ID
LEFT join SDUser j on j.USERID=l.USER_ID
LEFT join AaaUser m on m.USER_ID=j.USERID
LEFT Join UserDepartment e on e.USERID= m.USER_ID
LEFT join DepartmentDefinition f on f.DEPTID=e.DEPTID
WHERE b.ISDELETED=0
and b.STATUSID <> 2
and j.status='ACTIVE'
and qd.QUEUENAME = N'Soporte de Aplicaciones'
and std.STATUSNAME = N'En espera'