Report on Multi-Select Additional Fields - ME Version 14.9 Build 14930
How can I report on a multi select additional fields for "Tags" but have the report group the tags per ID, and not multiple lines per tag with the same request ID? There are no errors or guidance in ME when I run the update SQL with the addition of below:
#################
STUFF(( SELECT CHAR(10) + rupl2.VALUE FROM Request_Multi_Fields_1 rmf2 LEFT JOIN Request_UDF_PickListValues rupl2 ON rmf2.UDF_PICK11=rupl2.PickListID WHERE rmf2.WORKORDERID = wo.WORKORDERID FOR XML PATH('') ), 1, 1, '') AS "Tags"
#################
This is my working SQL:
SELECT cd.CATEGORYNAME AS "Category",
wo.WORKORDERID AS "Request ID",
aau.FIRST_NAME AS "Requester",
ad.ORG_NAME AS "Account",
cd.CATEGORYNAME AS "Category",
scd.NAME AS "Subcategory",
rtdef.NAME AS "Request Type",
std.STATUSNAME AS "Request Status",
ti.FIRST_NAME AS "Technician",
wos.REOPENED AS "ReOpened",
Request_UDF_PickListValues_UDF_PICK11.VALUE AS "Tags"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
LEFT JOIN Request_Multi_Fields_1 rmf1 ON wo.WORKORDERID=rmf1.WORKORDERID LEFT JOIN Request_UDF_PickListValues Request_UDF_PickListValues_UDF_PICK11 ON rmf1.UDF_PICK11=Request_UDF_PickListValues_UDF_PICK11.PickListID
WHERE (( cd.CATEGORYNAME = 'Pump Checker' ) AND ( ( ( wo.RESOLVEDTIME >= 1756422000000 ) AND ( ( wo.RESOLVEDTIME != 0 ) AND ( wo.RESOLVEDTIME IS NOT NULL ) ) ) AND ( ( wo.RESOLVEDTIME <= 1756508399000 ) AND ( ( ( wo.RESOLVEDTIME != 0 ) AND ( wo.RESOLVEDTIME IS NOT NULL ) ) AND ( wo.RESOLVEDTIME != -1 ) ) ) )) AND wo.ISPARENT='1' ORDER BY 1
When I run this it fails to run:
SELECT cd.CATEGORYNAME AS "Category",
wo.WORKORDERID AS "Request ID",
aau.FIRST_NAME AS "Requester",
ad.ORG_NAME AS "Account",
cd.CATEGORYNAME AS "Category",
scd.NAME AS "Subcategory",
rtdef.NAME AS "Request Type",
std.STATUSNAME AS "Request Status",
ti.FIRST_NAME AS "Technician",
wos.REOPENED AS "ReOpened",
STUFF(( SELECT CHAR(10) + rupl2.VALUE FROM Request_Multi_Fields_1 rmf2 LEFT JOIN Request_UDF_PickListValues rupl2 ON rmf2.UDF_PICK11=rupl2.PickListID WHERE rmf2.WORKORDERID = wo.WORKORDERID FOR XML PATH('') ), 1, 1, '') AS "Tags"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
LEFT JOIN Request_Multi_Fields_1 rmf1 ON wo.WORKORDERID=rmf1.WORKORDERID LEFT JOIN Request_UDF_PickListValues Request_UDF_PickListValues_UDF_PICK11 ON rmf1.UDF_PICK11=Request_UDF_PickListValues_UDF_PICK11.PickListID
WHERE (( cd.CATEGORYNAME = 'Pump Checker' ) AND ( ( ( wo.RESOLVEDTIME >= 1756422000000 ) AND ( ( wo.RESOLVEDTIME != 0 ) AND ( wo.RESOLVEDTIME IS NOT NULL ) ) ) AND ( ( wo.RESOLVEDTIME <= 1756508399000 ) AND ( ( ( wo.RESOLVEDTIME != 0 ) AND ( wo.RESOLVEDTIME IS NOT NULL ) ) AND ( wo.RESOLVEDTIME != -1 ) ) ) )) AND wo.ISPARENT='1' ORDER BY 1
Any help would be most appreciated! :)