Event list with non workflow Actions
"SELECT de.EventNumber AS [ID]
,dou.OrgUnitName AS [Business Unit]
,dent.entityname AS [Project]
,de.EventName AS [Event Title]
,det.EventTemplateName AS [Form]
,CONCAT (
dcu.FirstName
,' '
,dcu.LastName
) AS [Reporter]
,count(DISTINCT dact.ActionNumber) AS [NonWorkflow-Actions]
,substring((
SELECT ', ' + dact2.ActionNumber
FROM DimAction dact2
LEFT JOIN FactEventSectionSegmentField fact2 ON fact2.DimEventSegmentId = dact2.DimEventSegmentId
WHERE fact2.DimEventID = de.DimEventId
GROUP BY dact2.ActionNumber
FOR XML path('')
,TYPE
).value('.', 'NVARCHAR(MAX)'), 3, 4000) AS [Associated Action Ids]
,de.EventDateTime AS [Event Date]
,devs.EventStatusName
FROM DimEvent de
INNER JOIN DimEntity dent ON de.DimEntityId = dent.DimEntityId
INNER JOIN DimOrgUnit dou ON de.DimOrgUnitId = dou.DimOrgUnitId
INNER JOIN DimEventTemplate det ON de.DimEventTemplateId = det.DimEventTemplateId
INNER JOIN DimCompanyUser dcu ON de.DimAuditorUserId = dcu.DimCompanyUserId
INNER JOIN DimEventStatus devs ON de.DimEventStatusId = devs.DimEventStatusId
INNER JOIN FactEventSectionSegmentField fact ON fact.DimEventId = de.DimEventId
INNER JOIN DimAction dact ON dact.DimEventSegmentId = fact.DimEventSegmentId
--where de.EventNumber = '201907124.0'
GROUP BY de.EventNumber
,dou.OrgUnitName
,dent.entityname
,de.EventName
,det.EventTemplateName
,CONCAT (
dcu.FirstName
,' '
,dcu.LastName
)
,de.EventDateTime
,devs.EventStatusName
,de.DimEventId"