Home General Discussion

SCSM SSRS SQL

CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

Dear All,

Can anyone help, i need to get fields for affected user and also assigned to user on my reports. I can do one or the other using the following but doesnt work with both (using same code):

WorkItemAffectedUserFactvw AS AffectedUser ON workitem.WorkItemDimKey = affecteduser.WorkItemDimKey LEFT OUTER JOIN

             UserDimvw AS userdim ON AffectedUser.WorkItemAffectedUser_UserDimKey = userdim.UserDimKey

Any help with this is appreciated.

Daniel

Best Answers

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    Answer ✓

    Dear Adam,

    I changed your code as below and getting better results now. I moved the two where statements into the joins which seems to have fixed it.

    Daniel


    LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

    AND wiau.DeletedDate is null

    LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey


    LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

    AND wiat.DeletedDate IS NULL

    LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

Answers

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    edited May 2020

    Dear Adam,

    Thank you for the help.

    Kind Regards

    Daniel

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    That seems to have worked! Thank you for the code and also your guidance here.

    Kind Regards

    Daniel

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    Hi Adam,

    If an incident has been accident to many different technicians it shows different lines in the report for each technician. Can it only show the current assigned?

    Thanks for the help!

    Daniel

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited May 2020

    Yes - per my original query notice the WHERE clause that's being used. the "DeletedDate" property of those relationship tables keep track of when the relationship was broken (e.g. unassigned). Therefore the relationship that has a NULL date would be the current/final user.

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    Ok it clears out 2 of the four entries? Really appreciate the help with this.

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    You are right, i think it was the data warehouse taking time to catch up.

    Thank you for all the help.

    Kind Regards

    Daniel

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    Hi Adam,

    I hope you are ok, im still having problems with this actually.

    When I put in:

    and wiau.DeletedDate is null
    	and wiat.DeletedDate is null
    

    It doesn't show up all the records where as when i put it in it shows duplicates with other assigned people to the ticket and not the actual assigned person as it is at the moment?

    Are you able to offer any help?

    Daniel

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    edited May 2020

    This is my SQL code:

    FROM      IncidentDim 

    LEFT OUTER JOIN entitydim as e on IncidentDim.EntityDimKey = e.EntityDimKey

    LEFT OUTER JOIN workitemdim as wi on e.EntityDimKey = wi.EntityDimKey

    LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

    LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

    LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

    LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey


    WHERE    


    AND (wiau.DeletedDate is null)

    AND (wiat.DeletedDate is null)

    ORDER BY IncidentDim.CreatedDate DESC

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭

    Dear Adam,

    I went through a process of rebuilding the datawarehouse before would this affect data that is shown on the reports? I am trying to identify if new tickets are ok and old are not now.

    When I rebuilt the datawarehouse there was still test tickets in the scsm databases.

    Daniel

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    Answer ✓

    Dear Adam,

    I changed your code as below and getting better results now. I moved the two where statements into the joins which seems to have fixed it.

    Daniel


    LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

    AND wiau.DeletedDate is null

    LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey


    LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

    AND wiat.DeletedDate IS NULL

    LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    If you're still seeing test tickets from early on, it sounds like your core SCSM Grooming settings are a bit large. You can adjust these settings from the SCSM Console

    Administration -> Settings -> Data Retention Settings

    There you'll find the retention for each Work Item class along with the History retention. In general, you'll want to keep all of these numbers as low as you possibly can. The faster you groom, the more lean your SCSM deployment will be. However this comes at the potential cost that people looking for "old" Work Items may not find them because they get groomed out so quickly. So this is a bit of a balancing act.


    But glad to hear you got this sorted.

  • CaterhamITSupportCaterhamITSupport Member Advanced IT Monkey ✭✭✭
    edited May 2020

    Hi Adam,

    Thanks for this but this is definitely sorted now with the change in SQL coding moving them from where part to joins. Thank you for all the help with this, its really appreciated.

    Kind Regards

    Daniel

Sign In or Register to comment.