A few days ago, I had the privilege of presenting at the System Center Service Manager User Group (SCSMUG). The topic was on how to build your own self-service reports with Service Manager and SSRS. I've included a link to that video for anyone who is interested in checking it out.
Build Your Own Self-Service Reports with Service Manager and SSRS (Video)
Throughout the presentation, I mention some additional resources and code snippets to make your life a little easier. Below you will find all of those resources.
1. SM Job Aids. This download contains the SCSM class diagram.
2. SQL Server 2012 Report Builder. This download contains the Report Builder app you can use to build your own reports in SSRS.
3. The Incident Search Report Query. Below is the query (in copy/paste mode) that we used to build our report. As a note, as Chris mentions in the video, it is recommended to query the Data Warehouse for your reporting needs. There are some instances where I've found querying the CMDB to be effective, but use this method with caution. All of the principles described in the presentation can be applied to the Data Warehouse.
SELECT
I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'Id',
I.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'Title',
I.Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B AS 'Description',
Sta.DisplayName AS 'Status',
Afu.DisplayName AS 'Affected User',
Asu.DisplayName AS 'Assigned To User',
Sgr.DisplayName AS 'Support Group',
Cls.DisplayName AS 'Classification',
Imp.DisplayName AS 'Impact',
Urg.DisplayName AS 'Urgency',
I.Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794 AS 'Priority',
Src.DisplayName AS 'Source',
I.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS 'Created Date',
CASE WHEN I.Escalated_525F1F92_CEB3_079D_C0A5_E7A06AC4D6A5 = 1 THEN 'True' ELSE 'False' END AS 'Is Escalated'
FROM MTV_System$WorkItem$Incident I
-- Classification
LEFT OUTER JOIN DisplayStringView Cls ON I.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = Cls.LTStringId AND Cls.LanguageCode = 'ENU'
-- Source
LEFT OUTER JOIN DisplayStringView Src ON I.Source_96FD9295_16FA_3D7A_5995_F805B7B01F21 = Src.LTStringId AND Src.LanguageCode = 'ENU'
-- Impact
LEFT OUTER JOIN DisplayStringView Imp ON I.Impact_276C8DBF_2BC3_2374_665E_77FC76513017 = Imp.LTStringId AND Imp.LanguageCode = 'ENU'
-- Urgency
LEFT OUTER JOIN DisplayStringView Urg ON I.Urgency_D4A8FF0E_3074_B44F_54AD_CC201ABD9A6A = Urg.LTStringId AND Urg.LanguageCode = 'ENU'
-- Status
LEFT OUTER JOIN DisplayStringView Sta ON I.Status_785407A9_729D_3A74_A383_575DB0CD50ED = Sta.LTStringId AND Sta.LanguageCode = 'ENU'
-- Support Group
LEFT OUTER JOIN DisplayStringView Sgr ON I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = Sgr.LTStringId AND Sgr.LanguageCode = 'ENU'
-- Affected User
LEFT OUTER JOIN Relationship AfuRel ON I.BaseManagedEntityId = AfuRel.SourceEntityId AND AfuRel.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' AND AfuRel.IsDeleted = 0
LEFT OUTER JOIN MTV_System$Domain$User Afu ON AfuRel.TargetEntityId = Afu.BaseManagedEntityId
-- Assigned To User
LEFT OUTER JOIN Relationship AsuRel ON I.BaseManagedEntityId = AsuRel.SourceEntityId AND AsuRel.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' AND AsuRel.IsDeleted = 0
LEFT OUTER JOIN MTV_System$Domain$User Asu ON AsuRel.TargetEntityId = Asu.BaseManagedEntityId
4. The Status Enum Data Set. Below is the query (in copy/paste mode) that we used to populate the Status report filter with a dropdown. You can modify this query to create additional data sets for the other enums in your report.
SELECT '_Blank' AS Label, '_Blank' AS Value
UNION
SELECT DISTINCT D.DisplayName AS Label, D.DisplayName AS Value
FROM DisplayStringView D
INNER JOIN EnumType E on D.MPElementId = E.EnumTypeId
WHERE E.EnumTypeName LIKE 'IncidentStatusEnum%' AND D.LanguageCode = 'ENU' AND E.ParentEnumTypeId IS NOT NULL
5. Text Search Filter. Below is the filter logic we used to create filters around string columns (like "Title" in the provided example in the video).
=IIF(IsNothing(Fields!Title.Value),"
",Fields!Title.Value)
LIKE
="*" + Parameters!Title.Value + "*"
6. Enum Search Filter. Below is the filter logic we used to create filters around enum/list columns (like "Status" in the provided example in the video).
=IIF(IsNothing(Fields!Status.Value),"_Blank",Fields!Status.Value)
IN
@Status
7. SCSM Data Warehouse and Reporting. This link contains a wealth of knowledge around how reporting works in SCSM.
If I am missing anything or you would like me to post the actual report/query files, just add a comment below I'll be sure to get that out to the community.
Once again I want to send a note of thanks to both Lee Berg and Chris Ross for the invitation and privilege of being able to present at the User Group. I'm impressed by the work you guys are doing. Take care everyone and talk to you soon!
Until the Whole World Hears,
Christopher