Query for showing activities and information about parent items.

Eivind_Osbak

Hi.

I'm creating a dashboard for displaying information about activities and its parent workitems.
I've created a very basic query to display all manual activities:

SELECT [WorkItemId]
,[Title]
,[ParentWorkItemId]
FROM [ServiceManagement].[dbo].[WorkItem]
Where [ClassId] like '7AC62BD4-8FCE-A150-3B40-16A39A61383D'

I would like to get information like the title of its parent items, and also the parents parentitem. In this case because the MAs parent is a CR and the CRs parent is a RR.

Im not that experienced in SQL queries and hope someone can help me with a query for this so i can later manipulate it to get the data i want.

/Eivind

Roland_Kind

Hi, just as an idea to bypass SQL commands - you can create a view showing activites and properties of their parents in the scsm console and then publish the view to the Portal. This works at least for the parent workitem info - I have not tested this to get parent parentsitem (getting the RR from a CR).

maybe this is an easier approach ?

Geoff_Ross

Eivind,

Check out this post for an example of what you are trying.

https://community.cireson.com/discussion/1331/show-service-request-properties-in-manual-activity-grid

Geoff

Roland_Kind

Hi, just as an idea to bypass SQL commands - you can create a view showing activites and properties of their parents in the scsm console and then publish the view to the Portal. This works at least for the parent workitem info - I have not tested this to get parent parentsitem (getting the RR from a CR).

maybe this is an easier approach ?

Geoff_Ross

Eivind,

Check out this post for an example of what you are trying.

https://community.cireson.com/discussion/1331/show-service-request-properties-in-manual-activity-grid

Geoff

Adrian_Paech

Question.
how do you publish views in the console, into the portal?
Cheers,
Adrian

Brad_McKenna

@Adrian_Paech Checkout the quick and easy walkthrough in the Cireson Knowledge Base. Super easy to promote.

How To Promote a View from the SCSM Console to the Portal

https://support.cireson.com/KnowledgeBase/View/31#/

Adrian_Paech

Cheers Brad