Custom Report Problem
I have a custom report that was given to me by a ME employee. It pulls Service Requests records and includes approval information. It seems the query is only pulling records from items under one of our service catalog categories. How can I add in other Service Catalog categories? here is the query:
SELECT wo.WORKORDERID "Request ID",wo.title "Subject",requesttemplate_list.templatename "Template name",wo.description"description",LONGTODATE(wo.CREATEDTIME) "Created Time",LONGTODATE(wo.RESOLVEDTIME) "Resolved Date", accountdefinition.org_name "Account",sdu.firstname "First Name", sdu.lastname "Last Name",servicereq_601.Udf_char3 "Requester First Name",servicereq_601.Udf_char2 "Requester Last Name",
asd.stagename "Stage Name",ad.email "Approver Email",LONGTODATE(astage.SENT_DATE) "Approval Sent date",LONGTODATE(ad.ACTION_DATE) "Approved Date",ApprovalStatusDefinition.STATUSNAME "Approval Status",ad.COMMENTS "Comments" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN approvalstagemapping asmm ON asmm.workorderid=wo.workorderid
LEFT JOIN approvalstage astage ON astage.approval_stageid=asmm.approval_stageid
LEFT JOIN approvaldetails ad ON ad.approval_stageid=astage.approval_stageid
LEFT JOIN approvalstagedefinition asd ON asd.stageid=ad.stageid
LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid
LEFT JOIN sdorganization sdd ON wo.siteid=sdd.org_id
LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid
LEFT JOIN approvalstatusdefinition ON ad.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN requesttemplate_list ON requesttemplate_list.templateid=wo.templateid
left join sduser sdu ON wo.requesterid=sdu.userid
LEFT JOIN ServiceReq_601 ON wo.workorderid = ServiceReq_601.workorderid
WHERE (wo.ISPARENT='1' AND wo.IS_CATALOG_TEMPLATE='1') and astage.sent_date is not NULL and requesttemplate_list.templatename like '%%' and accountdefinition.org_id in ($Account) and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
SELECT wo.WORKORDERID "Request ID",wo.title "Subject",requesttemplate_list.templatename "Template name",wo.description"description",LONGTODATE(wo.CREATEDTIME) "Created Time",LONGTODATE(wo.RESOLVEDTIME) "Resolved Date", accountdefinition.org_name "Account",sdu.firstname "First Name", sdu.lastname "Last Name",servicereq_601.Udf_char3 "Requester First Name",servicereq_601.Udf_char2 "Requester Last Name",
asd.stagename "Stage Name",ad.email "Approver Email",LONGTODATE(astage.SENT_DATE) "Approval Sent date",LONGTODATE(ad.ACTION_DATE) "Approved Date",ApprovalStatusDefinition.STATUSNAME "Approval Status",ad.COMMENTS "Comments" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN approvalstagemapping asmm ON asmm.workorderid=wo.workorderid
LEFT JOIN approvalstage astage ON astage.approval_stageid=asmm.approval_stageid
LEFT JOIN approvaldetails ad ON ad.approval_stageid=astage.approval_stageid
LEFT JOIN approvalstagedefinition asd ON asd.stageid=ad.stageid
LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid
LEFT JOIN sdorganization sdd ON wo.siteid=sdd.org_id
LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid
LEFT JOIN approvalstatusdefinition ON ad.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN requesttemplate_list ON requesttemplate_list.templateid=wo.templateid
left join sduser sdu ON wo.requesterid=sdu.userid
LEFT JOIN ServiceReq_601 ON wo.workorderid = ServiceReq_601.workorderid
WHERE (wo.ISPARENT='1' AND wo.IS_CATALOG_TEMPLATE='1') and astage.sent_date is not NULL and requesttemplate_list.templatename like '%%' and accountdefinition.org_id in ($Account) and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>