none
Create Change Request report containing PA and SA from DataWarehouse RRS feed

  • Question

  • Hi everyone!

    I need to create a report from DW containing PA and SA like this:

    , I have searched the tables but I can not get it. The Activities are configured as the image below:(yellow color indicates the activities to be shown in the report)


    I have a SQL query which shows the activities but only shows me the parent activities. On SCSM DataWarehouse exist any tables that show the child activities?


    Saludos.- Mauricio Urrutia.



    Thursday, January 10, 2019 10:24 PM

Answers

  • No problem at all so what you want to do is the following. I formatted it so that it will show it similar to your table above

    This is an example of doing it showing the Sequential Activities

    (Note that your Change Request columns will have different guids on it so make sure it resolves to when you use it. May have to delete the column name after the table name . then type it again to resolve)

    select MTV_System$WorkItem$ChangeRequest.Id,
    MTV_System$WorkItem$ChangeRequest.Title,
    MTV_System$WorkItem$ChangeRequest.ActualStartDate,
    MTV_System$WorkItem$ChangeRequest.ActualEndDate,
    RelationshipGenericView.TargetObejctDisplayName
    from MT_System$WorkItem$Activity$SequentialActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$SequentialActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView
    ON Relationshipview.RelationshipId = RelationshipGenericView.IdJOIN MTV_System$Workitem$ChangeRequest
    ON RelationshipGenericView.SourceObjectName = MTV_System$WorkItem$ChangeRequest.ID

    If you want to show the entire thing being Sequential and Parallel Activieis then you need this

    select MTV_System$WorkItem$ChangeRequest.Id,
    MTV_System$WorkItem$ChangeRequest.Title,
    MTV_System$WorkItem$ChangeRequest.ActualStartDate,
    MTV_System$WorkItem$ChangeRequest.ActualEndDate,
    SequentialActivity.TargetObejctDisplayName,
    ParallelActivity.TargetObjectDisplayName
    from MT_System$WorkItem$Activity$SequentialActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$SequentialActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView As SequentialActivity
    ON Relationshipview.RelationshipId = SequentialActivity.IdJOIN RelationshipGenericView As ParallelActivity
    ON Relationshipview.RelationshipId = ParallelActivity.IdJOIN MTV_System$Workitem$ChangeRequest
    ON RelationshipGenericView.SourceObjectName = MTV_System$WorkItem$ChangeRequest.ID


    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro

    Monday, February 11, 2019 9:47 AM

All replies

  • I'm working on doing this report tomorrow will give you an update on this
    Monday, January 21, 2019 8:02 PM
  • Have you tried looking at the DWDataMart DependentActivityDependsOnWorkItemvw table? Can you share your query as well?
    Tuesday, January 22, 2019 10:29 AM
  • Hi Dwalsham!!

    the table that you mention is empty:


    Saludos.- Mauricio Urrutia.

    Thursday, January 24, 2019 10:39 PM
  • When you run a query to get a list of the activites on the ActivityDim table on the DWDataMart Database, are you able to see all of the activity IDs mentioned in your Change Request?
    Monday, January 28, 2019 11:40 AM
  • Negative. I only see those parents activities (Yellow), not child activities(red).

    


    Saludos.- Mauricio Urrutia.

    Monday, January 28, 2019 10:04 PM
  • If you create a test change request and the activites are live do you see anything in the MTV_System$WorkItem$Activity$DependentActivity table in the ServiceManager database?

    Also you can try the MTV_System$WorkItem$Activity$PublishActivity and MTV_System$WorkItem$Activity$SequentialActivity this should list of all them. If they do then we'll need to create a SQL Query to join them all which can be done from the Service Maager and the Data Warehouse DBs

    • Edited by Dwalsham Tuesday, January 29, 2019 8:40 AM More words
    Tuesday, January 29, 2019 8:38 AM
  • Great!!!

    last question: how can I create the inner join between those tables to show a report that contains the Change Request ID and ID activity?

    I'm trying to create relationship these tables:  [MTV_System$WorkItem$Activity$SequentialActivity],[MTV_System$WorkItem$Activity$ParallelActivity].

    But I don't know how to join them to : [MTV_System$WorkItem$ChangeRequest].

    Any idea?


    Saludos.- Mauricio Urrutia.


    Wednesday, January 30, 2019 8:49 PM
  • Ok so this is where it gets a bit of work now

    So as its in your change request (may add this query to technet too) you'll need to do a few joins

    The problem is because its in the ServiceManager database the tables are more scattered, and you cannot simply join change requests to other related items, each of them have their own table but they can only be joined through the RelationShipView and Relationship Generic Tables, i'll see if i can do a quick query to give an idea on how to put it together


    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro

    Thursday, January 31, 2019 12:51 PM
  • Ok so try something like this for sequential activities

    select * from MT_System$WorkItem$Activity$SequentialActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$SequentialActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView
    ON Relationshipview.RelationshipId = RelationshipGenericView.Id

    then this for parallel activities

    select * from MT_System$WorkItem$Activity$ParallelActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$ParallelActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView
    ON Relationshipview.RelationshipId = RelationshipGenericView.Id

    This should allow you to see the activities for each change request but just seperately by the activity category. If you want them to be merged together then we can create a view for you, but let me know if this gets you what you need. I can filter it by specific columns too as it currently shows everything

    If this looks ok or somewhere in the right lines then i'll create a SQL query for you


    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro


    • Edited by Dwalsham Thursday, January 31, 2019 1:10 PM More Words
    Thursday, January 31, 2019 1:10 PM
  • Perfect Dwalsham!!!

    I'll run the query on the Service Manager DB and let you know the result.


    Saludos.- Mauricio Urrutia.

    Thursday, January 31, 2019 2:52 PM
  • How did you get on with this?

    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro

    Thursday, February 7, 2019 11:32 AM
  • Hi Dwalsham!!

    yes the query run perfect.

    Question: how can I join table [MTV_System$WorkItem$ChangeRequest] with this query?



    Regars.-

    Mauricio Urrutia.



    Thursday, February 7, 2019 3:03 PM
  • No problem at all so what you want to do is the following. I formatted it so that it will show it similar to your table above

    This is an example of doing it showing the Sequential Activities

    (Note that your Change Request columns will have different guids on it so make sure it resolves to when you use it. May have to delete the column name after the table name . then type it again to resolve)

    select MTV_System$WorkItem$ChangeRequest.Id,
    MTV_System$WorkItem$ChangeRequest.Title,
    MTV_System$WorkItem$ChangeRequest.ActualStartDate,
    MTV_System$WorkItem$ChangeRequest.ActualEndDate,
    RelationshipGenericView.TargetObejctDisplayName
    from MT_System$WorkItem$Activity$SequentialActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$SequentialActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView
    ON Relationshipview.RelationshipId = RelationshipGenericView.IdJOIN MTV_System$Workitem$ChangeRequest
    ON RelationshipGenericView.SourceObjectName = MTV_System$WorkItem$ChangeRequest.ID

    If you want to show the entire thing being Sequential and Parallel Activieis then you need this

    select MTV_System$WorkItem$ChangeRequest.Id,
    MTV_System$WorkItem$ChangeRequest.Title,
    MTV_System$WorkItem$ChangeRequest.ActualStartDate,
    MTV_System$WorkItem$ChangeRequest.ActualEndDate,
    SequentialActivity.TargetObejctDisplayName,
    ParallelActivity.TargetObjectDisplayName
    from MT_System$WorkItem$Activity$SequentialActivity
    JOIN RelationshipView
    ON MT_System$WorkItem$Activity$SequentialActivity.BaseManagedEntityId = Relationshipview.TargetEntityId
    JOIN RelationshipGenericView As SequentialActivity
    ON Relationshipview.RelationshipId = SequentialActivity.IdJOIN RelationshipGenericView As ParallelActivity
    ON Relationshipview.RelationshipId = ParallelActivity.IdJOIN MTV_System$Workitem$ChangeRequest
    ON RelationshipGenericView.SourceObjectName = MTV_System$WorkItem$ChangeRequest.ID


    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro

    Monday, February 11, 2019 9:47 AM
  • Is this working ok for you now?

    Thanks


    Website: www.walshamsolutions.com Technical Blog: https://www.walshamsolutions.com/technical-blog Personal Blog: https://www.walshamsolutions.com/personal-blog Twitter: Dwalshampro

    Monday, February 18, 2019 10:27 AM