A while ago I published an article that explained how you can create a custom workflow to count incident support group changes. This is an easy way to go for those incidents that were pushed back and forth between support groups. What’s still missing however is a report that allows you to easily display those incidents. In this post I will demonstrate how this works.

I will use the SQL Report Builder 3.0 for this task, but any other tool that allows you to design report would also work. I will not go into any details how the basic setup needs to be donas this is already explained here and here.

For my report, I added a shared data source. I used “DWDataMart” one from the SCSM reporting server.

Then I also added a nice picture that will be used in the report header later.

After that I created a dataset with a SQL query that gets the data I want to display in the report from the DWDataMart.

Here we go with the full query. I marked the property for the suport group changes so that you know what needs to be changed for your environment. Also I stated that I want to display all incidents with more than 2 support group changes. At the end you see that I added some joins to make sure the enumerations are not displayed with their IDs but with a translated display string. You can develop the query in SQL Management Studio or directly in the Report Builder, whatever you prefer.

SELECT
  IncidentDimvw.Id
  ,IncidentDimvw.Title
  ,Strings.DisplayName AS Classification
  ,IncidentDimvw.CreatedDate
  ,StringsTQ.DisplayName AS TierQueue
  ,IncidentDimvw.supportgroupchanges
FROM
  IncidentDimvw

Join  IncidentClassificationvw Classification ON IncidentDimvw.Classification_IncidentClassificationId = Classification.IncidentClassificationId
Join  IncidentTierQueuesvw TierQueue ON IncidentDimvw.TierQueue_IncidentTierQueuesId = TierQueue.IncidentTierQueuesId

Join  DisplayStringDimvw Strings ON Classification.EnumTypeId = Strings.BaseManagedEntityId
Join  DisplayStringDimvw StringsTQ ON TierQueue.EnumTypeId = StringsTQ.BaseManagedEntityId

WHERE
  Strings.LanguageCode = ‘ENU’
  and
  StringsTQ.LanguageCode = ‘ENU’
  and
  IncidentDimvw.supportgroupchanges > 2

The last step is now to create a table on the report and to bring in the different values.

If needed, you can of course add report parameters. For this example I did not specify anything. But now, let’s run the report.

Nice. The last step is now to publish the report on the SCSM reporting server so that it will be available from the SCSM console.

Have fun!

Cheers
Marcel