I want to know if its posible to count workitems and group them by type (incidents, service requests, change requests) and also group them by status (Active/Inprogress, Closed) from the SystemCenterWorkItemCube out of the box cube. Here is the query I made to DWDATAMART. But I think that in the Cubes the data is partitioned, so you cant make that query only from this cube.

SELECT SUM(CASE WHEN i.BaseManagedEntityId IS NOT NULL AND i.Status_IncidentStatusId = 2 THEN 1 ELSE 0 END) AS WorkedIncident

,SUM(CASE WHEN s.BaseManagedEntityId IS NOT NULL AND s.Status_ServiceRequestStatusId = 2 THEN 1 ELSE 0 END) AS WorkedSR

,SUM(CASE WHEN c.BaseManagedEntityId IS NOT NULL AND c.Status_ChangeStatusId = 2 THEN 1 ELSE 0 END) AS WorkedCR

,SUM(CASE WHEN i.BaseManagedEntityId IS NOT NULL AND i.Status_IncidentStatusId = 4 THEN 1 ELSE 0 END) AS ClosedIncident

,SUM(CASE WHEN s.BaseManagedEntityId IS NOT NULL AND s.Status_ServiceRequestStatusId = 3 THEN 1 ELSE 0 END) AS ClosedSR

,SUM(CASE WHEN c.BaseManagedEntityId IS NOT NULL AND c.Status_ChangeStatusId = 4 THEN 1 ELSE 0 END) AS ClosedCR

FROM [DWDataMart].[dbo].[WorkItemDim] w

LEFT JOIN [DWDataMart].[dbo].[IncidentDim] i ON w.BaseManagedEntityId = i.BaseManagedEntityId

LEFT JOIN [DWDataMart].[dbo].[ServiceRequestDim] s ON w.BaseManagedEntityId = s.BaseManagedEntityId

LEFT JOIN [DWDataMart].[dbo].[ChangeRequestDim] c ON w.BaseManagedEntityId = c.BaseManagedEntityId

Hi,

Please refer to the query below, you could count the IR**/SR**/CR**.

FROM [DWDataMart].[dbo].[WorkItemDim] w

LEFT JOIN [DWDataMart].[dbo].[IncidentDim] i ON w.BaseManagedEntityId = i.BaseManagedEntityId

LEFT JOIN [DWDataMart].[dbo].[ServiceRequestDim] s ON w.BaseManagedEntityId = s.BaseManagedEntityId

LEFT JOIN [DWDataMart].[dbo].[ChangeRequestDim] c ON w.BaseManagedEntityId = c.BaseManagedEntityId