Customizing SCSM Reports using extended fields on an Management Pack

Customizing SCSM Reports using extended fields on an Management Pack

A week ago I was hired to customize SCSM 2012 to extend the SCSM Incident Field and to add some information on it. Like adding custom fields on the incident form on this link: http://blog.scsmfaq.ch/2011/01/04/adding-a-custom-field-to-incident-forms/ . My problem was that I need to create a list and then retrieve information of this list using the reports on SCSM. Unfortunatelly there is no much doccumentation about this.

So I had a hard time wondering how does it all works. And you have to do some steps to get it right:
  1. Create your MP using authoring.
  2. Seal the MP using authoring console or powershell (to do that remember to genereate the snk, you may use the "sn -k genkey.snk" command line).
  3. Remember to seal the MP otherwise it won´t send your data to the DW.
  4. Sync the DW MP_SyncJob.


In a few moment, or hours :), your DWDATAMART database will get new columns in the IncidentDim Table. But instead of regular fields you will see some GUID.
Keep in mind that your table with the values are recorded on the ServiceManager database on a table begining with dbo.MT_ClassExtension_<guid>.




Ok to customize your report you have to follow these steps:


To do all this you should read some documents like:
http://blogs.technet.com/b/servicemanager/archive/2010/04/23/how-to-create-a-custom-report-and-display-it-in-the-console.aspx
http://scug.be/scsm/2012/04/10/working-with-scsm-reporting/
http://blogs.technet.com/b/servicemanager/archive/2009/10/23/the-system-center-platform-in-service-manager-part-6-the-data-warehouse-try-it.aspx
http://blogs.technet.com/b/servicemanager/archive/2011/03/16/create-a-report-model-with-localized-outriggers-aka-lists.aspx


1.Customize an SP. In my case I copy the   ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents, and renamed it.
 

2.Then you have to customize your list query on the ReportBuilder.


3. Customize your Parameters using Report Builder.


4.Customize your Report adding a Column.


5. You can test your reports they should look like this:


If you did this all and olny get GUIDs or Report Server Erros try enabling the SQLRS error log, and check your querys.

The querys I used were:

List_Periodicidade:

SELECT DISTINCT 
D.LTValue AS Label, 
E.Periodicidade_953F1075_07A0_EC08_A501_C93F473C1E64 AS Value
FROM   sm2012.dbo.MT_ClassExtension_8f33e7a5_7f0a_4b0c_b9a9_d656293c33af E
LEFT OUTER JOIN sm2012.dbo.LocalizedText D
ON CAST(D.LTStringId AS nvarchar(256)) = CAST(E.Periodicidade_953F1075_07A0_EC08_A501_C93F473C1E64 AS nvarchar (256))
AND D.LanguageCode = 'ENU'
ORDER BY Label


Stored Procedure:

USE [DWDataMart]
GO
/****** Object:  StoredProcedure [dbo].[test_Report_IncidentManagement_SP_GetListOfIncidents]    Script Date: 08/29/2012 13:08:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[test_Report_IncidentManagement_SP_GetListOfIncidents]
@DateFilter nvarchar(256) = null,
@StartDate datetime,
@EndDate datetime,
@Source nvarchar(max) = '-1',
@Status nvarchar(max) = '-1', 
@Priority nvarchar(max) = '-1',
@Periodicidade nvarchar(256) = '-1',
@Urgency nvarchar(max) = '-1',
@Impact nvarchar(max) = '-1',
@Classification nvarchar(max) = '-1',
@SupportGroup nvarchar(max) = '-1',
@ResolutionCategory nvarchar(max) = '-1',
@AssignedTo int = null,
@ContactMethod nvarchar(max) = null,
@Description nvarchar(max) = null,
@ResolutionDescription nvarchar(max) = null,
@RelatedCIs  nvarchar(max) = null,
@ID  nvarchar(max) = null,
@LanguageCode nvarchar(max)= 'ENU'
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @Error int
  DECLARE @ExecError int
 
  SET @EndDate = DateAdd(Day, 1, @EndDate)
 
  DECLARE @tableID TABLE(value nvarchar(256))
  INSERT @tableID (value)
  Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ''))
 
  DECLARE @tableSource TABLE (value nvarchar(256))  
  INSERT @tableSource (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Source) 
 
  DECLARE @tableStatus TABLE (value nvarchar(256))  
  INSERT @tableStatus (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Status)
  
  DECLARE @tablePriority TABLE (value nvarchar(256))  
  INSERT @tablePriority (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)

  DECLARE @tableUrgency TABLE (value nvarchar(256))  
  INSERT @tableUrgency (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)
  
  DECLARE @tableImpact TABLE (value nvarchar(256))  
  INSERT @tableImpact (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)

  DECLARE @tableClassification TABLE (value nvarchar(256))  
  INSERT @tableClassification (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)

  DECLARE @tableSupportGroup TABLE (value nvarchar(256))  
  INSERT @tableSupportGroup (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)
  
  DECLARE @tableResCategory TABLE (value nvarchar(256))  
  INSERT @tableResCategory (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@ResolutionCategory)
  
  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  
  INSERT @tableRelatedCIs (value)
  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)
  
SELECT DISTINCT
I.IncidentDimKey,
I.CreatedDate,
Description=NULL, 
I.ResolvedDate, 
I.Priority, 
--I.Periodicidade,
D.LTValue AS Periodicidade, 
--E.Periodicidade_953F1075_07A0_EC08_A501_C93F473C1E64 AS Periodicidade, 
I.Id, 
I.Title,

Source = ISNULL(SourceDS.DisplayName, SourceEnum.IncidentSourceValue) ,
SourceEnum.IncidentSourceId AS SourceId,

Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) , 
StatusEnum.IncidentStatusId AS StatusId, 

Impact = ISNULL(ImpactDS.DisplayName, ImpactEnum.IncidentImpactValue),
ImpactEnum.IncidentImpactId AS ImpactId,

AssignedTo.UserDimKey AssignedToUserId,
AssignedTo.DisplayName AssignedToUserName


FROM         
dbo.IncidentDim I 

INNER JOIN dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey

LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 
AND dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL

LEFT OUTER JOIN
dbo.UserDimvw AS AssignedTo 
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 

LEFT OUTER JOIN 
dbo.WorkItemAboutConfigItemFactvw ON 
dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey 
AND dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL

LEFT OUTER JOIN 
dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON 
CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey 
AND CIFctForFilter.DeletedDate IS NULL

LEFT OUTER JOIN
dbo.IncidentSourcevw AS SourceEnum 
ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId  

LEFT OUTER JOIN 
dbo.DisplayStringDimvw SourceDS
ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId
AND SourceDS.LanguageCode = @LanguageCode

LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum 
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId  

LEFT OUTER JOIN 
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = @LanguageCode

LEFT OUTER JOIN
dbo.IncidentImpactvw AS ImpactEnum 
ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId

LEFT OUTER JOIN 
dbo.DisplayStringDimvw ImpactDS
ON ImpactEnum.EnumTypeId=ImpactDS.BaseManagedEntityId
AND ImpactDS.LanguageCode = @LanguageCode


LEFT OUTER JOIN
sm2012.dbo.MT_ClassExtension_8f33e7a5_7f0a_4b0c_b9a9_d656293c33af E
ON E.Periodicidade_953F1075_07A0_EC08_A501_C93F473C1E64 = I.Periodicidade

LEFT OUTER JOIN sm2012.dbo.LocalizedText D
ON CAST(D.LTStringId AS nvarchar(256)) = CAST(E.Periodicidade_953F1075_07A0_EC08_A501_C93F473C1E64 AS nvarchar (256))
AND D.LanguageCode = 'ENU'

WHERE
(
(@DateFilter = 'ResolvedOn' AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate))) OR
(@DateFilter = 'ClosedOn' AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate)))  OR
(@DateFilter = 'CreatedOn' AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate))) OR
(@DateFilter = 'All')
) AND 
(@StartDate <= @EndDate) AND
((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource))) AND
((-1 IN (Select value from @tableStatus)) OR (I.Status_IncidentStatusId IN (Select value from @tableStatus))) AND
((-1 IN (Select value from @tableImpact)) OR (I.Impact_IncidentImpactId IN (Select value from @tableImpact))) AND
((-1 IN (Select value from @tableUrgency)) OR (I.Urgency_IncidentUrgencyId IN (Select value from @tableUrgency))) AND
((-1 IN (Select value from @tableClassification)) OR (I.Classification_IncidentClassificationId IN (Select value from @tableClassification))) AND
((-1 IN (Select value from @tableSupportGroup)) OR (I.TierQueue_IncidentTierQueuesId IN (Select value from @tableSupportGroup))) AND
((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority))) AND 
--((-1 IN (Select value from @tablePeriodicidade)) OR (I.Periodicidade IN ( Select value from @tablePeriodicidade))) AND 
((I.Periodicidade IN (  @Periodicidade   ))) AND 
(
(-1 in (Select value from @tableResCategory)) 
OR 
(
I.ResolutionCategory_IncidentResolutionCategoryId IN (Select value from @tableResCategory) 
OR 
(
(I.ResolutionCategory_IncidentResolutionCategoryId IS NULL) 
AND 
('' IN (Select value from @tableResCategory))
)
)
) AND
((@AssignedTo = 0) OR AssignedTo.UserDimKey = @AssignedTo) AND
((@ID IS NULL) OR (I.Id IN (Select value from @tableID)))
AND ((0 IN (select value from @tableRelatedCIs)) OR (CIFctForFilter.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)))

  SET @Error = @@ERROR

QuitError:
  
  RETURN @Error
END

   
Classificar por: Data da Publicação | Mais Recente | Mais Úteis
Comentários
  • I have extended Incident class with a list called PatientType. My SCSM database name is ServiceManager in another instance on the same server. When I left Join as you have done but It says "Invalid Object Name ServiceManager.dbo.MT_ClassExtension_e743e4f7_bbf9_402f_8954_ec97eb786aae"

    Can u please help to resolve the issue?

    LEFT OUTER JOIN

    ServiceManager.dbo.MT_ClassExtension_e743e4f7_bbf9_402f_8954_ec97eb786aae E

    ON E.PatientType_E410835E_B9A0_964F_B439_8AD94A5AD237 = I.PatientType

    LEFT OUTER JOIN ServiceManager.dbo.LocalizedText D

    ON CAST(D.LTStringId AS nvarchar(256)) = CAST(E.PatientType_E410835E_B9A0_964F_B439_8AD94A5AD237 AS nvarchar (256))

    AND D.LanguageCode = 'ENU'

Página 1 de 1 (1 itens)